Business Automation
n8nAutomationData SyncSystem DesignCRMPostgreSQL

Single Source of Truth with n8n: Master Data, Bidirectional Sync, and Conflict Resolution

AO
Adrijan Omićević
·16 min read

# What You’ll Build and Why It Matters#

Most companies end up with customer data split across a CRM, a billing system, and a product database. Without clear ownership, each system edits the same fields and you get drift: duplicated accounts, stale addresses, mismatched plan names, and support tickets that start with “Which one is correct?”.

This guide shows how to implement a single source of truth using n8n by designing a system of record with explicit ownership rules, a canonical data model, mapping tables, bidirectional sync, deterministic conflict resolution, deduplication, and replay or backfill workflows.

You’ll also learn how to structure workflows so they stay reliable at scale. In production, the biggest failures are not “bad code”, but missing idempotency, missing checkpoints, and no way to replay safely after an outage.

For deeper mechanics like CDC, pagination, and dedup at the node level, also read n8n Data Sync: CDC, Pagination, and Deduplication and for Postgres-first automation patterns see n8n + Supabase/Postgres Automation Patterns. If you’re still stabilizing API consumption basics, start with our API Integration Guide.

# Architecture: “System of Record” Instead of “Everything Syncs With Everything”#

A true single source of truth is not one database to rule them all. It’s one authoritative owner per entity and often per field, plus a canonical representation that other systems conform to.

Example stack: CRM, Billing, Product DB#

A common setup looks like this:

SystemExamplesPrimary purposeTypical strengthsTypical risks
CRMHubSpot, Salesforce, PipedriveSales pipeline, contacts, companiesHuman-friendly editing, lifecycle stagesUsers overwrite authoritative billing fields
BillingStripe, Paddle, ChargebeeSubscriptions, invoices, payment methodsStrong event model, immutable invoice factsCustomer identity mismatches with CRM
Product DBPostgres, SupabaseApp accounts, entitlements, usage, rolesReal-time app truth, joins, constraintsDevelopers create new sources of truth accidentally
Canonical layerPostgres tables + n8n workflowsSynchronization + governanceDeterministic rules, audit trailBecomes messy without mapping + logging

The goal is to avoid “mesh sync”. Mesh sync is when every system pushes to every other system, creating loops, conflicts, and brittle assumptions. Instead, build a hub-and-spoke pattern:

  1. 1
    Define a canonical model for each entity.
  2. 2
    Define ownership rules.
  3. 3
    Maintain mapping tables for IDs across systems.
  4. 4
    Run directional sync workflows per entity and per event type.
  5. 5
    Enforce idempotency, conflict rules, and replay.

🎯 Key Takeaway: “Bidirectional” does not mean “everyone can edit everything”. It means changes can propagate both ways through a controlled authority model.

# Step 1: Define Canonical Entities and Ownership Rules#

Start by listing entities that must be consistent across systems. For most SaaS businesses:

  • Account or Company
  • Contact or User
  • Subscription
  • Plan or Product
  • Entitlements or Features
  • Invoice facts and payment status

Then decide who owns what. Ownership rules must be explicit, written, and enforced by the workflows.

Ownership matrix example#

EntityFieldOwnerSync directionConflict strategy
Accountlegal_nameCRMCRM to Canonical to Product DBPrefer CRM, log if Product differs
Accountbilling_emailBillingBilling to Canonical to CRM and Product DBPrefer Billing, block CRM overwrites
AccountcountryBillingBilling to Canonical to CRMPrefer Billing unless missing
UseremailProduct DBProduct DB to Canonical to CRMPrefer Product DB, immutable after verification
SubscriptionstatusBillingBilling to Canonical to Product DBAlways Billing, treat as event-sourced
Subscriptionplan_idBillingBilling to Canonical to Product DBBilling wins, validate plan mapping
Accountlifecycle_stageCRMCRM to Canonical onlyCRM-only field, do not push to Billing

This matrix prevents the classic failure mode: a sales rep “fixes” a billing email in CRM, and n8n dutifully overwrites the billing system. If Billing owns billing_email, your sync should reject CRM-origin updates for that field.

⚠️ Warning: Avoid global “last write wins”. It is simple, but it guarantees you will eventually overwrite correct data with wrong data because editability is not equal to authority.

# Step 2: Build Mapping Tables That Make Sync Deterministic#

You cannot build stable bidirectional sync without mapping tables. Relying on email matching alone is a guaranteed dedup nightmare once people change emails, share domains, or merge accounts.

Core mapping tables#

At minimum, create:

TablePurposeKeys
entity_mapMap canonical entity to external IDsentity_type, canonical_id, system, external_id
sync_checkpointStore cursor or last processed markerworkflow_name, scope, cursor, updated_at
sync_logAudit and debugrun_id, workflow, system, entity_type, external_id, action, status, error
conflict_logExplain conflictsentity_type, canonical_id, field, winner_system, loser_system, reason

A practical implementation in Postgres:

SQL
CREATE TABLE entity_map (
  entity_type TEXT NOT NULL,
  canonical_id UUID NOT NULL,
  system TEXT NOT NULL,
  external_id TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(entity_type, system, external_id),
  UNIQUE(entity_type, canonical_id, system)
);

This enables deterministic operations:

  • Find canonical ID from any system’s ID
  • Prevent accidental duplicates
  • Support merges by remapping external IDs to a single canonical ID

The canonical table is not optional#

Define canonical tables too. Example for accounts:

Canonical fieldTypeNotes
idUUIDCanonical ID
display_nametextHuman-friendly
legal_nametextOwned by CRM in this example
billing_emailtextOwned by Billing
billing_statustextOwned by Billing
updated_attimestamptzTrack canonical changes
source_updated_at_crmtimestamptzOptional per system
source_updated_at_billingtimestamptzOptional per system

You can store per-system timestamps and hashes to avoid unnecessary writes, which matters when APIs have strict rate limits.

# Step 3: Design n8n Workflows as “Pipelines” With Idempotent Upserts#

Break workflows by entity and direction, not by “sync everything”. You want smaller workflows that are easier to reason about, test, and replay.

A clean baseline set:

WorkflowTriggerDirectionPurpose
crm_to_canonical_accountsWebhook or pollingCRM to canonicalIngest CRM updates, apply ownership rules
billing_to_canonical_subscriptionsWebhook eventsBilling to canonicalEvent-driven subscription truth
canonical_to_productdb_entitlementsCron or queueCanonical to Product DBApply entitlements and roles
canonical_to_crm_enrichmentCronCanonical to CRMPush billing status or computed metrics
replay_backfill_accountsManualAnyControlled historical rebuild

Idempotency rules you should enforce#

In n8n, assume you will reprocess the same event at least once. Network timeouts, retries, and partial failures make this normal.

Use these patterns:

  • Upsert instead of create
  • Use mapping table lookup before creating any entity
  • Store event_id for webhook events and ignore duplicates
  • Use a deterministic idempotency key like system + entity_type + external_id + source_updated_at

Example idempotency key creation:

JavaScript
// n8n Code node
const system = $json.system;
const entityType = $json.entity_type;
const externalId = $json.external_id;
const updatedAt = $json.source_updated_at || '';
return [{ idempotency_key: `${system}:${entityType}:${externalId}:${updatedAt}` }];

Keep the final write stage idempotent. If the same idempotency key arrives twice, it should result in the same canonical state without duplicating rows.

💡 Tip: When an API supports it, pass an idempotency key on writes. Stripe, for example, supports idempotency keys for many endpoints, reducing duplicate side effects during retries.

# Step 4: Field-Level Conflict Resolution That Matches Business Reality#

Conflict resolution is where most “single source of truth” projects fail. Not because the logic is complex, but because teams avoid making explicit decisions.

Use an explicit resolution strategy per field#

Common strategies that work in practice:

StrategyBest forRule
Owner winsMost fieldsThe owner system overwrites others
Prefer non-emptyOptional enrichmentIf owner value is empty, accept other
Freshness within ownerSame owner multiple eventsCompare source_updated_at
Immutable after setIdentity fieldsOnce verified, disallow changes
Manual review queueHigh-risk fieldsLog conflict, do not auto-resolve

Implement owner wins in n8n#

Your workflow should:

  1. 1
    Identify the entity and canonical record.
  2. 2
    For each incoming field, check ownership.
  3. 3
    If not owned by incoming system, ignore or log.
  4. 4
    If owned, update canonical and propagate.

Pseudo-logic:

JavaScript
// n8n Code node: decide allowed updates based on ownership
const incomingSystem = $json.system;
const ownership = {
  billing_email: 'billing',
  legal_name: 'crm',
  subscription_status: 'billing'
};
 
const incoming = $json.data;
const allowed = {};
const blocked = {};
 
for (const key of Object.keys(incoming)) {
  const owner = ownership[key];
  if (!owner || owner === incomingSystem) allowed[key] = incoming[key];
  else blocked[key] = { value: incoming[key], owner };
}
 
return [{ allowed, blocked }];

If blocked has entries, write them to conflict_log with a reason like “field not owned by source system”.

Avoid blind timestamp comparisons across systems#

A CRM “updated_at” might change when someone opens a record or edits a note. Billing timestamps might represent event creation time, not semantic updates. If you do last write wins across systems, you’ll be comparing unrelated clocks.

Use timestamps only within the same system, plus ownership rules across systems.

# Step 5: Deduplication Patterns That Don’t Destroy Data#

Deduplication is not a single step. It’s a set of controls:

  • Prevent duplicates at ingestion
  • Detect duplicates that slip through
  • Merge safely with a consistent policy

Prevention: use mapping tables and unique constraints#

Your entity_map unique constraints are your first line of defense. Before creating a new canonical entity, always attempt to resolve:

  1. 1
    By external_id mapping
  2. 2
    By stable natural keys like billing customer ID
  3. 3
    Only then consider fuzzy matching like email and company name

Detection: periodic dedup scan#

Run a daily or weekly workflow that finds suspicious duplicates:

SignalQuery ideaAction
Same billing customer linked to multiple canonical IDsgroup by billing external IDAuto-merge candidate
Same verified email across multiple usersgroup by emailManual review
Same VAT ID across accountsgroup by tax idAuto-merge if other fields match

Safe merging: re-map, don’t delete first#

A safe merge procedure:

  1. 1
    Choose winner canonical ID.
  2. 2
    Update all entity_map rows to point to winner.
  3. 3
    Update foreign keys in canonical tables.
  4. 4
    Mark loser as merged, keep record for audit.

This makes replay and historical logs explainable.

ℹ️ Note: In regulated environments, hard-deleting customer records can create compliance and audit issues. Prefer soft delete plus merge metadata.

# Step 6: Bidirectional Sync Without Loops#

Bidirectional sync creates loops if you don’t track provenance. If a change originated in Billing, then propagated to canonical, then to CRM, you must prevent the CRM update from being treated as a new source change that goes back to Billing.

Provenance fields#

Add fields to sync payloads and logs:

FieldExamplePurpose
source_systembillingWhere the change originated
source_event_idevt_123Unique event identity
sync_run_idUUIDTrace a full propagation chain
synced_attimestampPrevent immediate echo

Loop prevention rule#

If a workflow is pushing into CRM, mark records with a “last synced by n8n” marker if the CRM supports it, or store the timestamp in your canonical DB keyed by the external ID. Then, when ingesting CRM changes:

  • If the change occurred within a short window after an n8n push and the only changed fields match what you just sent, ignore.
  • If other fields changed, process only those fields and apply ownership.

This is where a sync_log with per-field diff becomes very useful, even if it costs more storage.

# Step 7: Replay and Backfill Jobs That Are Actually Safe#

A sync system without replay is not production-grade. APIs fail, webhooks get disabled, credential rotations break runs, and you will need to rebuild truth.

Replay types#

Replay typeWhen to useInputMust be idempotent
Event replayMissed webhooksStored event payloadsYes
Range backfillNew canonical logicDate range cursorYes
Full rebuildMajor model changeEntire datasetYes, and staged

Store raw events when possible#

For webhook-driven systems like billing, store raw events:

  • event_id
  • received_at
  • payload_json
  • processing status and attempts

Then your replay job is simple: load unprocessed events, apply the same transform, upsert canonical, mark processed.

Cursor-based backfill pattern#

When backfilling via API list endpoints, store checkpoints:

  • cursor token, page number, or last seen updated_at
  • scope such as accounts or subscriptions
  • optional partition keys like region or workspace ID

Example checkpoint row:

workflow_namescopecursorupdated_at
backfill_crm_accountsworkspace_12026-10-01T00:00:00Znow

Use this to continue after failures without starting over.

If you need pagination patterns and guardrails, follow n8n Data Sync: CDC, Pagination, and Deduplication.

⚠️ Warning: Do not run backfill and real-time sync against the same entities without isolation. Either pause real-time workflows, or use a strict rule that real-time wins and backfill only fills missing mappings.

# Step 8: Practical Example Design Across CRM, Billing, and Product DB#

Here’s a concrete ownership setup that works for many SaaS teams.

Canonical entity: Account#

  • CRM owns: legal_name, industry, lifecycle_stage
  • Billing owns: billing_email, tax_ids, payment_status
  • Product DB owns: workspace_slug, feature_flags_override

Sync flows:

  1. 1
    Billing event updates canonical payment status in seconds.
  2. 2
    Canonical pushes payment status to CRM nightly or hourly.
  3. 3
    CRM updates legal name, canonical pushes legal name to product DB daily.
  4. 4
    Product DB changes to slug do not sync back to CRM or billing.

Canonical entity: Subscription#

Treat subscription as billing-owned and event-sourced:

  • Billing is always the owner.
  • Canonical stores a current snapshot plus a history table if needed.
  • Product DB is a projection for entitlements.

This reduces conflicts because no other system should be allowed to set subscription status.

Mapping plan IDs reliably#

Never hardcode plan IDs in workflows. Maintain a plan mapping table:

canonical_plan_codebilling_price_idproduct_entitlement_tier
starter_monthlyprice_abcstarter
pro_monthlyprice_defpro
enterpriseprice_xyzenterprise

Then in n8n, look up by billing_price_id and write canonical_plan_code to canonical. Your product DB consumes the canonical code, not the billing vendor ID.

If you’re using Postgres as your automation backbone, the patterns in n8n + Supabase/Postgres Automation Patterns map directly to this approach.

# Step 9: Implementation Checklist in n8n#

A reliable single source of truth setup usually includes these nodes and controls.

Required workflow building blocks#

Concernn8n implementationOutcome
IngestionWebhook, Cron + HTTP RequestGet changes in
TransformationSet, Code, Date & TimeCanonical shape
Mapping resolutionPostgres node queryResolve canonical ID
IdempotencyPostgres upsert + unique keysSafe retries
Conflict detectionCode + Postgres insertExplainable decisions
Rate limitingWait node, batchingAvoid API bans
Observabilitysync_log and alertsFaster debugging

Minimal Postgres upsert pattern#

SQL
INSERT INTO canonical_account (id, billing_email, payment_status, updated_at)
VALUES ($1, $2, $3, NOW())
ON CONFLICT (id) DO UPDATE SET
  billing_email = EXCLUDED.billing_email,
  payment_status = EXCLUDED.payment_status,
  updated_at = NOW();

Alerting on repeated failures#

If a workflow fails more than a threshold, alert a channel. Even simple alerting prevents multi-day silent drift.

A practical threshold is “more than 5 consecutive failures” or “more than 1 percent of items failed in a batch”, depending on volume.

If you need API robustness patterns like retries, backoff, and error classification, see our API Integration Guide.

# Key Takeaways#

  • Define a system of record by ownership per entity and per field, not by hoping timestamps will sort it out.
  • Use mapping tables with unique constraints to make sync deterministic and to enable safe merges and deduplication.
  • Design bidirectional sync without loops by tracking provenance and suppressing echo updates.
  • Implement conflict resolution explicitly with owner wins, prefer non-empty, immutable fields, and manual review queues where needed.
  • Make every workflow replayable using idempotent upserts, event storage where possible, and cursor-based checkpoints for backfills.

# Conclusion#

A single source of truth with n8n is achievable when you treat sync like a product: explicit ownership rules, canonical models, deterministic mappings, and operational tooling for replay, backfill, and conflict audits. The payoff is measurable: fewer duplicates, fewer manual fixes, and predictable data for reporting and customer support.

If you want us to design or implement your n8n data sync single source of truth across CRM, billing, and Postgres, contact Samioda and we’ll propose a workflow architecture, canonical schema, and rollout plan that matches your systems and team constraints.

FAQ

Share
A
Adrijan OmićevićFounder & Senior Developer

Founder & Senior Developer at Samioda. 8+ years building React, Next.js, Flutter and n8n automation solutions for clients across Europe.

Need help with your project?

We build custom solutions using the technologies discussed in this article. Senior team, fixed prices.