Canonical Entities and Sources of Truth Across Supabase, GoHighLevel, and Airtable

Good to see the product blueprint becoming more concrete. You have already defined the tenant lifecycle, made the ICP configurable, and turned the core workflow into acceptance criteria. Those criteria repeatedly refer to “prospects,” “contacts,” “opportunities,” “campaigns,” “workflow runs,” and tenant-scoped actions—but they do not yet say where each record lives or which system is allowed to change it.

This lesson establishes that contract. You will define a canonical entity model for the command center and assign clear authority among Supabase, GoHighLevel, and Airtable. The goal is not to eliminate copies of data; integrations inevitably create copies. The goal is to ensure that every important field has one accountable owner, every external record can be traced to an internal record, and no integration becomes an accidental second database.

---

Canonical does not mean “everything lives in one tool”

A canonical entity is the product’s stable representation of a business concept. It gives your application a consistent vocabulary even when external systems use different names, IDs, schemas, and lifecycle rules.

For example:

• Your product calls a potential customer organization a prospect.
• Airtable may contain a row in a lead-research table.
• GoHighLevel may contain a CRM contact linked to an opportunity.
• Supabase may contain your enriched prospect record, qualification result, and external-record mappings.

These can all describe related things without being interchangeable records.

A source of truth is the system authorized to make a particular fact authoritative. It answers questions such as:

• Where is a new tenant workspace created?
• Which system decides whether an opportunity is won or lost?
• Which system owns the approved outreach draft and its reviewer decision?
• If Airtable and GoHighLevel disagree about a company name, which value should your command center display?
• When an external system changes a record, which internal fields may be updated automatically?

The crucial design principle is:

Assign authority at the smallest useful level: usually an entity, and sometimes a field group within that entity.

Trying to make all systems co-own a generic “contact” record is a common cause of sync loops and silent data loss. A contact’s normalized email and enrichment attributes can belong to your product, while GoHighLevel remains authoritative for its native DND state, CRM assignment, and delivery history.

The roles of the three systems

For the first production slice, give each platform a distinct responsibility:

System	Primary role in this product	What it should not become
Supabase	Product system of record: tenant control plane, canonical growth workflow, evidence, approvals, operational history, and integration mappings	A blind duplicate of every CRM field
GoHighLevel	CRM and engagement system: contacts in the client CRM, pipeline operations, native communication activity, appointments, and opportunity updates	The authority for your tenant model, AI workflow, or approval system
Airtable	Approved lead-intake and operator-managed source for imports	A second live CRM or a writable mirror of your application database

This division is particularly useful for a multi-tenant SaaS. Supabase is where you enforce your own product rules consistently, regardless of whether a tenant uses one GoHighLevel pipeline, several Airtable bases, or later adds another connector.

---

Examine GoHighLevel’s object boundary before modeling it

Before deciding what GoHighLevel owns, inspect the shape of the operational CRM surface it exposes. The important takeaway is not that you must synchronize every object; it is that the connector should deliberately select the few objects required by your acceptance criteria.

GoHighLevel’s webhook catalog is the other half of that boundary. It shows that changes can occur in GoHighLevel independently of your application: a salesperson can move a deal stage, a contact can be marked DND, or an inbound message can arrive.

A webhook is evidence that a remote system changed, not permission to overwrite your entire internal record. That distinction will guide the source-of-truth policy below.

---

Start with product entities, not vendor objects

Your canonical model should describe the language of your SaaS, even if the first implementation stores most records in Supabase. It should not inherit GoHighLevel’s schema simply because GoHighLevel is the first CRM connector, nor should it inherit Airtable’s columns simply because a client has a convenient lead list there.

For the MVP, use the following entity vocabulary.

Entity	Meaning in the product	Important identity rule
Organization	A paying client workspace, such as Northstar	One stable internal UUID; never infer identity from a name
User	An authenticated human identity	Auth identity is distinct from tenant membership
Membership	A user’s role in an organization	A user may belong to more than one organization
Integration connection	A tenant-authorized connection to GoHighLevel or Airtable	Bound to one organization and one external account scope
ICP configuration version	A versioned definition of good-fit prospects	Qualification always records which version was used
Prospect	A company or organization being evaluated for growth activity	Tenant-scoped; normalized domain is a primary deduplication signal
Contact	A person associated with a prospect	Tenant-scoped; normalized email and phone are identifiers, not display strings
Research evidence	A source URL, snippet, capture time, and extracted support for an attribute	Evidence is append-oriented and attributable
Enrichment result	A structured set of prospect attributes derived from evidence	Must preserve confidence, freshness, and evidence references
Qualification assessment	A deterministic and AI-assisted evaluation against one ICP version	Never overwrite the assessment that justified a prior decision
Suppression	A reason a contact must not receive outreach	The safe effective state is the union of all applicable restrictions
Outreach draft and approval	A reviewable message or sequence plus its approval decision	Approval belongs to your product, not to a CRM campaign status
CRM contact link	The mapping between a canonical contact and a GoHighLevel contact	External IDs must be namespaced by provider and location
Opportunity projection	Your tenant-scoped view of a CRM deal	The active CRM deal remains owned by GoHighLevel
Campaign	The product-level business intent, audience, assets, and tracking identity	Separate campaign intent from delivery execution
Content asset	A draft, approved, or published content item	Versioned and reviewable inside the product
Business event	An immutable record of acquisition, engagement, conversion, or revenue activity	Preserve event source and observed time
Automation run, step, and attempt	Operational records for imports, enrichment, handoffs, and retries	One run may have many steps and connector attempts
Audit event	A record of privileged or consequential action	Append-only and tenant-scoped
Export or offboarding request	A controlled tenant-lifecycle action	Must capture scope, approval, and final status

Two choices deserve emphasis.

A prospect is not merely an Airtable row

An Airtable row is input provenance. A prospect is a durable product entity that can accumulate normalized identity, research evidence, qualification history, suppression checks, drafts, CRM links, and automation history.

The original Airtable values remain important. Preserve them as an import snapshot and link them to the resulting prospect. But do not make the live Airtable row the mutable database record for the rest of the workflow.

An opportunity is not merely a qualification result

Qualification decides whether a prospect deserves sales attention. An opportunity represents an active deal in a CRM pipeline, with business-owned stage, status, monetary value, and assignment.

A qualified prospect may have no opportunity yet. Conversely, a CRM opportunity can be created manually by a sales team. Your product should support both cases without assuming that every prospect automatically becomes a deal.

---

The source-of-truth decision matrix

The following is a practical MVP policy. It gives every entity one primary authority while allowing necessary projections in the other systems.

Tenant and product-control entities

Entity or fact group	Source of truth	Other systems’ role	Policy
Authentication identity	Supabase Auth	None	Supabase Auth is authoritative for authentication identity and session issuance
Organization, memberships, and roles	Supabase	GoHighLevel and Airtable are tenant integrations, not tenant authorities	Never derive a user’s product role from a CRM user record
Workspace onboarding state	Supabase	None	The command center decides whether required onboarding is complete
ICP configuration and versions	Supabase	Optional export as CRM metadata later	Qualification runs reference an immutable ICP version
Consent policy and contact-frequency policy	Supabase	GoHighLevel may contribute native DND facts	Product policy is evaluated before any outreach action
Brand profile, value proposition, campaign brief, and content approval	Supabase	GoHighLevel receives only approved delivery configuration	Approval stays in the command center
Research evidence, enrichment results, and qualification assessments	Supabase	CRM may receive selected resulting fields	Evidence and reasoning must remain inspectable in the product
Automation runs, retries, errors, and idempotency records	Supabase	External systems expose only their own operation results	The command center needs one operational history
Audit events, export requests, and offboarding state	Supabase	None	These are platform governance records

Supabase is therefore the product system of record. It contains the information required to explain why a workflow did or did not happen.

Lead intake and research entities

Entity or fact group	Source of truth	Supabase responsibility	Airtable responsibility
Raw imported lead row	Airtable at import time	Store an immutable raw snapshot, import run ID, source location, and mapping result	Provide the approved source table and row
Import mapping configuration	Supabase	Define which Airtable fields map to product fields	Provide the source columns
Canonical prospect	Supabase	Own normalized domain, display name, lifecycle state, deduplication outcome, evidence, and qualification history	May be used as a read-only intake source only
Canonical contact	Supabase	Own normalized person identity, prospect relationship, enrichment, and product-level restrictions	May supply initial identity values
Airtable record reference	Supabase	Store base, table, and record identifiers as an external link	Remain the external provenance location

For v1, Airtable should be import-only. A tenant may continue maintaining lists in Airtable, but your product does not continuously write enrichment, scores, or pipeline stages back to Airtable.

That restriction prevents a dangerous ambiguity: an operator edits a company’s domain in Airtable while the system has already enriched, qualified, and linked the prior identity to a GoHighLevel contact. A new import can detect and surface the difference, but it should not silently rewrite a canonical prospect.

CRM and engagement entities

Entity or fact group	Source of truth	Supabase representation	GoHighLevel role
GoHighLevel contact ID	GoHighLevel	Store a linked external identifier	Creates and owns its native contact record
Canonical contact identity and enrichment	Supabase	Canonical contact profile	May receive selected approved profile fields
Native DND status and native conversation activity	GoHighLevel	Ingest as source events and evaluate in suppression checks	Owns CRM-native delivery and conversation state
Effective product suppression state	Supabase	Stores the safe aggregate restriction and its sources	Can contribute a DND or unsubscribe signal
Pipeline definitions and stage IDs	GoHighLevel	Store mapped pipeline and stage references	Owns active pipeline configuration
Opportunity stage, status, value, and assignee	GoHighLevel	Maintain a tenant-scoped projection with observed_at time	Owns the live CRM deal
Opportunity-to-prospect linkage	Supabase	Link the external opportunity to canonical prospect and contact records	Holds its own remote IDs
Outreach draft, sequence intent, and approval	Supabase	Owns content, evidence references, reviewer, and approval state	Receives only a permitted activation or delivery request
Message delivery and inbound reply facts	GoHighLevel	Store normalized events and a delivery projection	Owns its native send and conversation records
Campaign business identity and UTM policy	Supabase	Owns campaign intent, content version, and tracking identifier	May host the configured CRM execution
Campaign execution status	GoHighLevel	Mirror status with source and observed time	Owns native campaign execution state

This distinction resolves a common contradiction:

• Your command center needs to show pipeline stage and opportunity value.
• The client’s sales team may update both directly in GoHighLevel.
• Therefore GoHighLevel is authoritative for the live CRM opportunity.
• Supabase stores an application projection so that the dashboard, automation rules, audit trail, and analytics have a consistent tenant-scoped representation.

Do not let an internal qualification score overwrite a salesperson’s opportunity stage. Qualification can recommend routing; once the opportunity is active in GoHighLevel, stage movement belongs to the configured CRM workflow.

Events and revenue facts

A business event is different from a current-state record. A current state answers, “What is the opportunity stage now?” An event answers, “What happened, when did it happen, and which system reported it?”

For the early product:

Fact	Source of truth	Canonical storage policy
Airtable import outcome	Supabase	The import run and accepted or rejected records are product facts
Research retrieval and enrichment outcome	Supabase	Store evidence references and run state
GoHighLevel contact, opportunity, appointment, and messaging activity	GoHighLevel	Persist a normalized immutable event after verified ingestion
Product approvals and operator actions	Supabase	Persist as audit events and workflow events
Revenue or payment activity from GoHighLevel	GoHighLevel when that tenant uses it as billing evidence	Store a sourced event; do not claim universal revenue authority in v1

The last row is intentionally conservative. A B2B SMB may invoice in GoHighLevel, Stripe, QuickBooks, or another system. Until you support and validate the tenant’s billing source, revenue metrics should be labeled as CRM-reported or omitted—not treated as universally authoritative.

---

Use field-level ownership for contacts and opportunities

The phrase “contact source of truth” is too broad to be safe. Make ownership explicit in the schema and in connector code.

Contact ownership policy

Contact field group	Authority	Reason
Internal ID, tenant ID, prospect relationship	Supabase	These are product-domain facts
Normalized email, normalized phone, canonical display name	Supabase after identity resolution	The product needs stable identity for deduplication and traceability
Enriched role, seniority, company attributes, evidence references	Supabase	These are outputs of your research workflow
GoHighLevel remote ID, location ID, remote timestamps	GoHighLevel	They identify the native CRM object
DND, unsubscribe, inbound reply, delivery status	GoHighLevel as originating source; Supabase as effective policy record	CRM activity can create a suppression signal that must block future outreach
Tags and custom fields	Explicitly configured per field	Never assume all CRM custom fields are safe to overwrite

For v1, use a safe union for suppression. If either system reports a restriction, outreach is blocked unless an authorized operator clears it through a documented process. A missing GoHighLevel DND value is not evidence that a contact has consent.

Opportunity ownership policy

Opportunity field group	Authority	Reason
Internal projection ID, tenant ID, linked prospect and contact	Supabase	Needed for product relationships and tenant isolation
GoHighLevel opportunity ID and pipeline identifiers	GoHighLevel	Native CRM identifiers and configuration
Stage, status, monetary value, assigned CRM user	GoHighLevel	Sales operations happen in the CRM
Qualification result that triggered routing	Supabase	Preserve the reason for entering the CRM workflow
CRM handoff status, attempts, and error details	Supabase	This is connector-operational state, not sales state
Last observed CRM snapshot	Supabase, sourced from GoHighLevel	Makes data freshness visible in the command center

A useful rule is: Supabase may request or initiate a CRM change, but GoHighLevel confirms the resulting CRM state.

---

Keep every external record link explicit

Never identify a GoHighLevel record by email alone or an Airtable row by a company name. External systems can contain duplicates, an email can change, and different tenants can legitimately use the same domain.

Create a general mapping entity, such as external_record_links, with fields conceptually equivalent to:

Field	Purpose
id	Internal immutable link ID
organization_id	Tenant boundary
entity_type	Such as prospect, contact, opportunity, or campaign_execution
entity_id	Internal canonical record ID
provider	Such as gohighlevel or airtable
connection_id	The tenant’s specific integration connection
external_scope_id	GoHighLevel location ID or Airtable base and table scope
external_id	The provider’s record identifier
last_observed_at	When your system last confirmed the remote record
remote_version	Provider revision, timestamp, or hash when available
sync_status	Such as linked, pending_create, synced, conflict, or archived

At minimum, enforce uniqueness for the combination of:

• organization;
• provider;
• integration connection;
• external scope;
• entity type;
• external record ID.

This prevents an external contact from one tenant’s GoHighLevel location being accidentally linked to another tenant’s internal record.

Keep provider credentials out of this table. The link records identity and operational metadata; secrets belong in encrypted server-side credential storage, which you will implement in the integrations module.

---

Define allowed synchronization directions before building sync code

A source-of-truth matrix is only useful when it translates into permitted write behavior. For the MVP, record the following rules in your product specification.

Change scenario	System allowed to initiate the business change	Required resulting record
Operator imports Airtable lead rows	Airtable supplies source data; Supabase accepts an import command	Supabase creates or links prospects and records raw-row provenance
Research changes prospect attributes	Supabase	Supabase stores evidence-backed attributes; optional CRM field update only if explicitly mapped
Qualification is completed	Supabase	Supabase records assessment, score, decision, and ICP version
Approved prospect enters CRM	Supabase initiates the handoff	GoHighLevel creates or updates native contact and opportunity; Supabase stores returned IDs
Salesperson changes opportunity stage	GoHighLevel	A webhook or reconciliation updates the Supabase opportunity projection
Contact replies or is marked DND	GoHighLevel	Supabase records the sourced event and updates effective suppression evaluation
Outreach draft is approved or rejected	Supabase	Only Supabase changes approval state; GoHighLevel may receive activation after approval
Airtable source row changes after import	Airtable	No automatic overwrite; a later import detects, deduplicates, or flags the change

This design intentionally avoids general bidirectional synchronization. “Bi-directional sync” often sounds complete, but without field ownership it usually means two systems overwrite each other on a timer.

Instead, each connector action should be described as one of these:

• Create: establish a new remote object and save the returned external link.
• Update owned fields: write only fields your source-of-truth policy authorizes.
• Ingest external event: accept a provider-originated change only for the field groups GoHighLevel owns.
• Reconcile: compare remote and projected state, then record a conflict or update an allowed projection.
• Archive: preserve history and prevent accidental recreation after a deletion or offboarding event.

---

Model states separately from the records they govern

The previous acceptance criteria introduced states such as completed, failed, blocked, and needs_review. Keep those operational states in the product model rather than trying to encode them solely as CRM tags or Airtable views.

For example:

• A prospect can have enrichment state needs_review.
• A qualification assessment can conclude disqualified.
• An outreach draft can be pending_approval.
• A CRM handoff can be failed even while the prospect remains qualified.
• An opportunity projection can show the last CRM stage observed from GoHighLevel.
• An automation attempt can be retried without creating a second opportunity.

These are separate facts, with different owners and retention needs. Combining them into one generic status field on a contact will eventually create contradictions that are impossible to explain in the dashboard.

---

Produce the canonical-model artifact

Create this file in the repository:

docs/product/canonical-data-model.md

Use the following structure.

# Canonical Data Model and Source-of-Truth Policy

## Scope and principles
- Canonical entity definition
- Source-of-truth definition
- Supabase, GoHighLevel, and Airtable responsibilities
- Rule against unconfigured bidirectional synchronization

## Entity catalog
For each entity:
- Purpose
- Tenant ownership
- Internal identifier
- Key relationships
- Lifecycle states
- Retention or archival notes

## Source-of-truth matrix
For each entity or field group:
- Authoritative system
- Permitted writers
- Read-model or mirror locations
- Inbound event sources
- Conflict policy

## External record links
- Required fields
- Provider scope requirements
- Uniqueness constraints
- Archival behavior

## Synchronization rules
- Airtable import policy
- CRM handoff policy
- GoHighLevel webhook ingestion policy
- Reconciliation policy
- Retry and idempotency policy

## Open decisions
- CRM custom-field allowlist
- Initial pipeline and stage mappings
- Contact identity conflict process
- Supported Airtable import schema
- Revenue-data authority per tenant

Populate the entity catalog first. Then add the source-of-truth matrix from this lesson, adapting entity names to the vocabulary you want to preserve in code.

Before considering the artifact complete, verify these implementation-facing decisions are visible:

• Every tenant-owned Supabase entity includes an organization_id.
• Every imported Airtable row has immutable provenance after import.
• Every GoHighLevel record link includes the tenant’s specific location or connection scope.
• Contact identity, CRM delivery state, suppression policy, and opportunity stage are not treated as one undifferentiated record.
• A GoHighLevel webhook can update only the field groups GoHighLevel owns.
• A future connector can be added without renaming your core product entities.
• No entity has two systems silently acting as equal writers.

---

Key takeaways

A canonical model is the product’s shared language; it is not a claim that only one database contains data. For this command center:

• Supabase owns tenant control, product workflow, evidence, approvals, automation operations, audit history, and canonical prospect and contact identity.
• Airtable is an approved intake source whose rows become preserved provenance, not a continuously synchronized CRM.
• GoHighLevel owns native CRM and engagement facts, especially pipeline configuration, active opportunity state, DND and conversation activity, and message-delivery outcomes.
• Some concepts need field-level ownership, particularly contacts, suppressions, campaign execution, and opportunities.
• Explicit, tenant-scoped external record links are what make safe synchronization and later webhook handling possible.

Next, you will build on this model by specifying tenant ownership and access boundaries for users, contacts, opportunities, campaigns, assets, events, and automation runs.