Status · revised, supersedes prior version

ConnectIQ — Architecture

Architecture information is spread across this documentation and the application repository, rather than kept in one architecture.md. This page owns the structural rules; the application files put them where the work happens.

CLAUDE.md — the core rules and a guide to what to read next.
src/domains/CLAUDE.md — domain boundaries, events and data naming.
supabase/migrations/CLAUDE.md — database changes, permissions and row-level security.
.claude/rules/design-tokens.md — visual rules for UI work.
ARCHITECTURE_RULES.md — security, quality and other rules that do not belong to one path.

Why split it? In Claude Code, relevant rules load as work reaches the files they govern. This keeps each session focused, makes rules easier to maintain beside the code, and avoids another all-in-one copy drifting out of date. Lint, checks and hooks enforce the rules that can be automated. See the full file and enforcement map.

ⓘ This page defines rules, not a complete schema. Sections 2–8 are binding, as are the prefixes in the domain map. Proposed table ownership is a planning aid. The delivery plan owns scope and sequence; this page wins on structural decisions.

Core principles

One Postgres database, with tables grouped by domain prefix. Each domain owns its writes and exposes data through defined interfaces, making domains easier to build independently and separate 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

4Path-triggered domain rules + intended-files check

5Code review against §2 matrix

Each prefix declares a table’s owner. Use the domain, prefix and table map; never invent a variant for an existing domain.

Cross-domain interaction rules

Use service clients for operational reads, reporting views for shared reports, and events for cross-domain changes.

#	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.

2.3 · Keep each business transaction inside one domain

Create a job and its tasks together. For work across domains, each domain commits its own changes and events coordinate the next step. The originating change and its plat_domain_events row commit together — see how events work.

How domains trigger changes in other domains

A domain records what happened in plat_domain_events, in the same transaction as its change. A dispatcher delivers the event; the receiving domain updates its own data. This is the outbox pattern: rolled-back changes produce no event, and committed changes keep their event for delivery. Consumers must be idempotent: processing the same event twice has the same result as processing it once.

3.1 · Delivery

Supabase Edge Function on a schedule, or pg_notify for latency-sensitive cases. Transport is replaceable — the outbox table is not.

3.2 · Event names

Past-tense facts, not commands: QuoteApproved, JobCreated, UnitReplaced. The consumer decides what to do.

3.3 · Internal events and external webhooks

int_webhook_events is inbound-external. plat_domain_events is internal. Different tables, never merged.

How to read another domain’s data

A read projection exposes another domain’s data without giving the reader ownership. Choose by purpose:

Reports and dashboards

`rpt_*` view

security_invoker = true. For reporting and dashboard reads.

Operational screens

Domain service client

The default. For operational reads.

Migration only

`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. For operational data, use the owner’s service client by default.

When to copy a value or store its ID

If the source changes tomorrow, should this record show the new value?

No — save a snapshot

Freeze values needed for history: quote pricing, contract customer name/address, delivery-note recipient or invoice description. Name the column explicitly, e.g. customer_name_snapshot.

Yes — store a reference ID

Keep an ID such as cl_customer_id and read current data through the owner’s service client. Use this for a job’s customer or a support ticket’s site; dashboards use reporting views.

Record IDs, business codes and references

Every table uses id uuid primary key default gen_random_uuid(). UUIDs keep independently created records from sharing a sequence across preview, import and production data.

Purpose	Convention
Record ID	UUID primary key; use it to address records in APIs.
Human-facing code	Separate column such as `customer_code` or `job_no`, generated by a database function. For display and lookup, never the primary key.
Reference in the same domain	`<entity>_id`, e.g. `job_id`.
Reference to another domain	`<prefix><entity>_id`, e.g. `cl_location_id`.
Status values	Use shared constants or enums, never raw status strings.
External system ID	Airtable, HubSpot, Freshdesk and Xero IDs live only in `int_external_system_mappings` (`system, entity_type, internal_id, external_id`), never in domain tables.

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

Reporting layer

Reporting reads domain tables directly — but only through rpt_* 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.

Migration: backfill and cutover

Not a continuous two-way sync — a transform-and-load into real domain tables, followed by a permanent cutover.

Cutover entities — customers, locations, contacts, brands

Load

Transform & load into cl_ / com_

Validate

Build & test against real data

Freeze

Source read-only, final delta

Reconcile

Row counts + integrity checks

Flip

CRM becomes source of truth

Disable

Old sync switched off at source

Mirror entities

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.

9.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

Domains, prefixes and table ownership

The eleven prefixes are locked. Never invent a variant for an existing domain. The responsibilities and table groups below are indicative, not a current schema inventory; use the delivery decisions for settled ownership and the ownership rule for open questions.

Prefix / domain	Indicative table groups	Boundary or open question
`cl_` Customer & Location	Customers, brands, locations, geography and arrangements.	Contacts: Customer or Commerce.
`com_` CRM / Commerce	Opportunities, quotes and lines, products, price books, billing entities and payment terms.	Commercial contracts may differ from subscription contracts.
`plan_` Sales Handover	Signed quote to execution scope: BOM, resources and readiness.	Job creation batches and templates: Planning or Ops.
`ops_` Field Operations	Jobs, tasks, schedules, maintenance, partners and workers.	Read customer, location, inventory and subscription data through their owners.
`inv_` Inventory / Asset	Resources, units, stock locations, movements, sets, scans, delivery notes, attributes and specs.	Serial-bound licences belong here; movable entitlements belong to Subscriptions.
`sub_` Subscription	Contracts, subscriptions, types, renewals and unit assignments.	Keep the commercial lifecycle separate from the device; see the subscription decision.
`sup_` Support / Incident	Tickets, logs, SLA, out-of-service items, menu items and compensation.	Agents and Weeks: ownership open.
`mon_` Device Health	Heartbeats, TargetR/Lisa status, uptime and alerts.	Inventory identifies the device; Monitoring tracks its health.
`int_` Integration / Sync	Import staging, external ID mappings, webhooks, sync logs and migration mirrors.	Moves data; owns no business decisions.
`rpt_` Reporting	Cross-domain views, read models and dashboards.	Read-only; views use security_invoker = true.
`plat_` Platform	Internal events, audit, configuration and identity infrastructure.	Broader Identity & Access, Finance & Billing, Procurement, Notifications and Audit ownership remains to be assigned when needed.

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.

The device swap, drawn

✓ Chosen

Separate domain

Commercial, time-based lifecycle: start, renew, suspend, cancel
Device swap is a non-event
1 subscription : many units, many locations
Supports software-only products
Ops actions cannot affect billing
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

11.1 · Linking subscriptions to units

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.

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.

Appendix B — Lovable knowledge-file rules

Archive. Claude Code replaced Lovable as the builder on 10 September 2026 (ADR-13). The old knowledge block is historical; the current rules above take precedence.

The former paste-in rules are preserved in the original Lovable knowledge block. For current work, use the application files listed at the top.