Database architecture

Use the controls to enlarge the diagram. Pan horizontally and vertically when zoomed.

Principal data domains

Domain	Purpose
Tenancy and access	Tenant membership, roles, hierarchy, visibility, and menu permissions
Customer and lead	Customer identity, vehicles, source leads, relationships, and activity
Workflow	Tasks, queue transitions, dispositions, attempts, and operational audit evidence
Documents and quotes	Document metadata, ownership links, integrity evidence, and quote calculations
Policy operations	Policy identity, verification, accounting periods, and lifecycle events
Finance	Commission versions, reconciliation, statements, receipts, allocations, adjustments, and reversals
Communications	Message and call requests, provider status, and workflow attribution
Configuration	Product, insurer, vehicle, geography, workflow, and question catalogues

Access paths and enforcement

• The browser uses a publishable client credential plus the authenticated user's session.
• Database grants and row-level security determine which records an ordinary request can access; route and menu controls are not the authoritative security boundary.
• Sensitive finance and administrative actions use approved database operations rather than unrestricted table access.
• Privilege-defining routines and trusted server operations are elevated boundaries. Selected operations validate actor, tenant, role, hierarchy, input, and object ownership; complete operation coverage is not available.
• The storage service evaluates object policies while application metadata records purpose, owner, size, type, and integrity information where required.

Change and recovery model

• Database changes are versioned and reviewed before an authorised deployment.
• A reviewed change artifact is not effective until separately deployed.
• Destructive rollback is avoided for audit and financial records; correction normally uses a forward change and versioned evidence.
• Provider recovery capability and application-level restore testing are documented separately under the backup and disaster-recovery checklist items.

Transaction and consistency model

Operation class	Consistency requirement	Enforcement location
Single-record maintenance	Required fields, enumerated state, referential ownership, and modification attribution commit together	Database constraints, policies, and application validation
Workflow transition	Previous state, requested transition, actor authority, resulting state, and audit event form one logical operation	Approved database operation or trusted server transaction
Policy verification	Required evidence, policy fields, vehicle context, actor scope, and financial prerequisites are evaluated before the verified state is stored	Trusted operation with database invariants
Financial posting	Source statement, matched policy, version, allocation, amount, and adjustment reason remain reconcilable	Transactional database operation with append-oriented evidence
Provider callback	Provider authenticity, replay protection, correlation reference, current business state, and idempotency are checked before mutation	Trusted serverless operation and transaction
Bulk import	File-level provenance and row-level validation are separated; accepted and rejected rows remain distinguishable	Staged parsing plus bounded database writes

Authorization evaluation order

1. The managed gateway validates the request shape and routes it to the intended service.
2. The signed session establishes the authenticated subject; a request-supplied user identifier is not accepted as proof of identity.
3. Database grants determine whether the caller role can access the object or execute the operation.
4. Row-level policies restrict the visible or mutable rows using tenant, hierarchy, ownership, or mapped scope.
5. Privileged operations perform explicit actor, target, and workflow-state checks before using elevated execution rights.
6. Constraints and transactions reject invalid relationships or partial multi-record state.
7. Audit attribution records the actor or trusted workload, business object, transition, and time.

Data lifecycle characteristics

Record class	Mutation model	Integrity expectation
Reference configuration	Controlled update with effective-state validation	Consumers resolve a consistent insurer, product, geography, vehicle, or workflow classification
Customer and vehicle	Correctable operational record with relationship history	Normalization and duplicate controls prevent accidental cross-customer association
Lead and task	Stateful workflow plus append-oriented event history	Current state is explainable from source, assignments, transitions, and dispositions
Quote and document	Point-in-time artefact with provenance	A generated or uploaded object remains associated with the exact task, policy, purpose, and actor
Policy	Versioned business record with controlled verification	Verification cannot be inferred solely from UI state; required evidence and authoritative operation decide it
Finance	Append, version, allocation, adjustment, and reversal	Historical settled evidence is not silently overwritten; corrections remain reconcilable

Security characteristics

Characteristic	Implication
Row-level security on reviewed application tables	Ordinary API access is policy-mediated or denied; individual policy correctness still requires review
Privilege-defining database operations	Central operations can enforce stable business rules but require restricted execution grants and explicit actor checks
Referential and transactional constraints	Relationships and multi-record invariants are enforced close to the system of record
Append-oriented audit and finance evidence	The design requires historical events to be added, adjusted, or versioned rather than silently overwritten
Shared managed database services	Authentication, storage, realtime, and application data integrate through controlled service boundaries

Authoritative platform references

• Supabase architecture
• Supabase database overview
• Supabase Auth architecture