A single physical Postgres database, logically separated by domain — built to behave like microservices today, and to split into real ones later without a rewrite.
ⓘThis page defines rules, not schema. Sections 2–9 are binding and are mirrored in the Lovable knowledge file. Domain names and table lists elsewhere in this document are planning aids only — finalised by whichever team builds that phase.
→This page is the authority on architecture — domains, ownership, table naming and cross-domain access. For what gets built and in what order, see the delivery plan; where the two disagree on anything structural, this page wins.
1
Core principles
One database. Domains behave like separate services anyway — no domain writes into another domain's tables, ever. That single constraint is what keeps a shared database safe to build fast on now, and cheap to physically split later.
The shared database, drawn as if it were already split
No arrows cross directly into another domain's boxes. The only three legal paths across a boundary: a service call, an event through the outbox, or a read through a rpt_* view.
1.1 · Why prefixes, not schemas
Supabase supports Postgres schemas fully. Lovable's generated code assumes default-schema access — steering it into .schema('x') calls on every prompt is fragile. See Appendix A.
Cost accepted: schemas would give a one-line permission boundary. Prefixes give none by default — the five substitutes below are mandatory, not optional.
1Per-domain Postgres roles + table grants
2RLS on every table
3CI lint: no cross-domain import
4Knowledge-file guardrails per prompt
5Code review against §2 matrix
1.2 · Prefix registry — locked
The prefix is the ownership declaration. Never invent a variant for an existing domain.
cl_Customer & Location
com_CRM / Commerce
plan_Sales Handover
ops_Field Operations
inv_Inventory / Asset
sub_Subscription
sup_Support / Incident
mon_Device Health
int_Integration / Sync
rpt_Reporting
plat_Platform
2
Cross-domain interaction rules
The operative section — every possible interaction between two domains falls into exactly one of these thirteen rows.
#
Interaction
Allowed
Notes
1
Store another domain's ID as a reference
Yes
Anywhere
2
Call another domain's service client / API
Yes
The only operational path for cross-domain data
3
Snapshot a value at event time
Yes
Subject to §5
4
Cross-domain read join
Restricted
Only inside a rpt_* view, owned by Reporting
5
Application reads a rpt_* view
Yes
The only cross-domain read surface for app code
6
Insert into shared audit / log tables
Yes
Insert-only
7
Emit a domain event
Yes
The only way to trigger a cross-domain write
8
Same-domain direct read
Yes
Unrestricted
9
App reads another domain's raw table
No
Including reporting and dashboard code
10
Nested/embedded PostgREST select across prefixes
No
See §2.2
11
Direct write to another domain's table
No
No exception
12
Cross-domain database transaction
No
No exception
13
Admin repair script
Exception
Approved, logged, never part of a normal workflow
2.1 · The operational read rule
Stricter than typical shared-DB design, on purpose — exactly one operational path survives a future physical split.
2.2 · Cross-prefix foreign keys — kept
ON DELETE RESTRICT. Reversal asymmetry decides it: dropping a constraint is instant; adding one later means repairing every orphan first.
Keep FK
No FK
Integrity
Orphans impossible
Found late
Reverse cost
One statement
Unknown cleanup
Backfill
Bad rows rejected
Land quietly
The one real cost: an FK makes PostgREST's nested select available — exactly the join rule 10 forbids. FK stays for integrity; the join stays forbidden in code review.
3
Domain events — the outbox pattern
An event is written in the same transaction as the state change that caused it. A dispatcher delivers it later. This guarantees an event is never lost on commit, and never fired on rollback.
3.2 · Transport
Supabase Edge Function on a schedule, or pg_notify for latency-sensitive cases. Transport is replaceable — the outbox table is not.
3.3 · Naming
Past-tense facts, not commands: QuoteApproved, JobCreated, UnitReplaced. The consumer decides what to do.
3.4 · Not webhooks
int_webhook_events is inbound-external. plat_domain_events is internal. Different tables, never merged.
4
Read projections
A read-only representation of data owned by another domain, exposed without transferring ownership. Three permitted forms:
View projection
rpt_* view
security_invoker = true. For reporting and dashboard reads.
Service projection
Domain service client
The default. For operational reads.
Mirror projection
int_*_mirror
Physical synced table. Migration only — source of truth still lives outside Supabase.
All three: read-only to the consumer, owning domain stays sole source of truth, never written back to. Where the planning maps say "read projection," assume service projection unless a table is explicitly a mirror.
5
Snapshot vs reference
Snapshot when the value must be frozen in time. Reference when the current value is wanted.
Snapshot — frozen at the moment
Quote pricing, contract snapshots, delivery-note recipient, invoice line description.
Reference — always current
Job-screen customer, dashboard KPIs, active location list, support ticket site.
Test: if the source record changes tomorrow, should this document read differently? No → snapshot. Yes → reference.
6
Identifier & key conventions
Every table: id uuid primary key default gen_random_uuid(). Decided by Supabase branching — preview branches, Airtable-loaded data, and production are three ID spaces that bigint sequences would collide across; UUIDs never do.
Airtable, HubSpot, Freshdesk and Xero IDs never live in domain tables — only in the mapping table. That's what keeps migration reversible.
6.2 · API routes
Address records by UUID. Codes are for humans and lookup, not addressing.
6.4 · Same-domain FK
<entity>_id — e.g. job_id
6.4 · Cross-domain FK
<prefix><entity>_id — e.g. cl_location_id
Rejected: bigint identity (branch/seed collision is fatal here); ULID/text (not native to Postgres, Lovable won't generate it).
7
Ownership tiebreak rule
The domain that performs the write owns the table. If two domains write, the concept is two tables.
1Naming forces the decision — prefix is the declaration
2Sets dependency direction — which domain calls which
3Unowned means built twice, or not at all
8
Reporting layer
Reporting reads domain tables directly — but only throughrpt_* views. The view is the seam that makes deferring a real read-model layer safe.
Non-negotiable: a Postgres view runs with the creator's rights by default, bypassing RLS. Without security_invoker = true, a portal user could see every customer's rows through a view that looks correct.
Same-domain dashboards may read their own tables directly — no view required. Only crossing a prefix boundary forces the view.
9
Transactions
Allowed
Within one domain — e.g. creating a Job and its Job Tasks together.
Forbidden
Across domains — sequenced instead by events and statuses: QuoteApproved → JobCreated → ResourceRequired → SubscriptionActivated, each in its own domain transaction.
10
Migration: backfill and cutover
Not a continuous two-way sync — a transform-and-load into real domain tables, followed by a permanent cutover.
Source of truth stays in Airtable/Freshdesk a while longer: loaded into int_*_mirror, read-only, carry synced_at, never joined into an operational write path, retired per-domain as each is migrated.
10.2 · Critical requirements, by consequence
1UUID stability across load runs — highest-risk detail in the migration
2Idempotent upsert keyed on external ID, never name/email
3Reverse kill switch — disable old sync at source
4Freeze window — source read-only during final delta
5Reconcile, with a stated rollback, before the flip
11
Indicative domain map
Planning only — not binding. Finalised by whichever team builds each phase.
cl_
Customer & Location
Customer, brand, location, geography, arrangement master data
Quotes, Quote line items, Products, Price books, Contracts, Billing entities, VAT/payment terms. Keep HubSpot stable while foundational domains are built.
mon_
Device Health
TargetR Status, TargetR PoP, Lisa Box Status, Monitor, Last online, Time offline. Inventory knows a serial is a device; Monitoring knows if it's healthy.
int_
Integration / Sync
Import staging, HubSpot/Freshdesk sync, scan ingestion, external system mappings, sync logs, webhook events, mirror tables. A technical service, not a business owner — it moves data and preserves mapping, owns no business decisions.
13
Decision record — Subscriptions separate from Inventory
Deciding scenario: the device swap. A player fails and is replaced. Fold subscription into Inventory, and it dies with the unit. Keep it separate, and the swap is a non-event.
Failure mode: drift — caught by a weekly reconciliation report
✕ Rejected
Folded into Inventory
One lifecycle, wrong for half the cases
Swap breaks continuity and revenue reporting
Forces a false 1:1 cardinality
Software-only needs a fake inventory record
Warehouse edits touch billing
Failure mode: structural, unrecoverable without remodelling
13.1 · Connective tissue
sub_subscription_units
subscription_id uuid
inv_unit_id uuid
valid_from date
valid_to date null
Also resolves licences: movable entitlement → Subscriptions; serial-bound attribute → Inventory; both exist → two tables.
A
Appendix A — Rejected: schema-based separation
Postgres and Supabase both support custom schemas well:
const supabase = createClient(URL, KEY,
{ db: { schema: 'quotation' } })
// or per query
await supabase.schema('quotation')
.from('quotes').select('*')
Why rejected
Lovable's generated code assumes default-schema access. Steering it into .schema() calls on every prompt is workable hand-written, fragile AI-generated.
Reversible: ALTER TABLE ... SET SCHEMA plus a rename is the natural first step of a real microservice split.
B
Appendix B — Lovable knowledge-file rules
Paste the block below directly into the Lovable knowledge file. It is self-contained — no reference back to this page is required.
knowledge-file.md
### ConnectIQ — Domain & Database Rules
These rules are binding for all generated code in this project. Apply them to every table, query, and migration, regardless of what a specific feature prompt asks for. If a request conflicts with a rule below, follow the rule and flag the conflict instead of silently resolving it.
1. Tables are prefixed by domain (cl_, com_, plan_, ops_, inv_, sub_, sup_, mon_, int_, rpt_, plat_). Never invent a prefix variant (e.g. cust_) for a domain that already has one.
2. Work only inside the domain you were asked to build. Do not modify global routing, layout, auth, the Supabase client, shared components, package files, or migrations belonging to another domain without explaining why first.
3. Never write to a table outside your domain's prefix.
4. Never read another domain's raw tables. Use that domain's service client for operational reads, or a rpt_* view for reporting/dashboard reads.
5. Never use PostgREST nested or embedded selects across prefixes (e.g. .select('*, cl_locations(*)')), even where a foreign key makes it technically available.
6. Never open a database transaction that spans more than one domain prefix.
7. Cross-domain effects are triggered by writing an event row to plat_domain_events, in the same transaction as the state change that caused it. Event names are past tense (e.g. QuoteApproved, JobCreated, UnitReplaced), describing a fact, not a command. Consumers must be idempotent — the same event delivered twice must produce the same result.
8. Every table has: id uuid primary key default gen_random_uuid(). Human-facing business codes (e.g. customer_code, job_no) are separate columns, generated by a database function, and are never the primary key.
9. Cross-domain foreign key / reference columns are named <prefix><entity>_id (e.g. cl_location_id, inv_unit_id). Same-domain foreign keys are named <entity>_id (e.g. job_id).
10. External system record IDs (Airtable, HubSpot, Freshdesk, Xero) never live in domain tables. They are stored only in int_external_system_mappings (system, entity_type, internal_id, external_id).
11. Snapshot a value only when it must be frozen at a point in time (e.g. quote pricing, contract customer name/address, delivery note recipient). Otherwise store a reference (ID) so the value stays current. Snapshotted columns are named to make this obvious, e.g. customer_name_snapshot.
12. Every rpt_* view is created WITH (security_invoker = true). This is not optional — without it, a view bypasses RLS on the underlying tables.
13. The customer portal never reads a raw domain table, under any circumstance. Portal reads go through rpt_* views or dedicated read models with RLS.
14. Cross-prefix foreign keys exist for referential integrity only. They do not authorise joins or nested selects in application code — see rule 5.
15. Use shared status constants/enums for any status field. Never write or compare a raw status string.
16. Before writing code, list the files you intend to create or edit, and confirm they stay within the current domain's boundary (rule 2).