Data model

Every table in the public schema, grouped by module, with the relationships that matter and the columns worth knowing. This is a map, not a column dump: the creating migration in supabase/migrations/ is the authority for a single table, and the latest migration touching a table wins.

Conventions

Each table has one of three data classes, which decides its RLS shape (see permissions):

Class	Meaning	Policy shape
platform	Operated by Complied, or locked from the API entirely	Operators only, or no authenticated grant
tenant-owned	Belongs to one tenant (a few are personal to one user)	tenant_id = current_tenant_id() plus a permission key
public reference	Shared by every tenant: the citywide registry, agency events, catalogs	Any signed-in user may read; writes are service-role or migration only

Patterns you will see everywhere:

• Tenant-owned rows carry a non-null tenant_id. For child tables a BEFORE INSERT trigger copies tenant_id (and often project_id) from the parent row, so a client cannot spoof it.
• id is uuid. Exceptions: permissions.key, document_types.code, license_types.slug and compliance_programs.id are text keys; invoice_number_counters is keyed (tenant_id, year).
• Vocabulary that changes with the rulebook (service types, contest grounds, abatement methods, collaborator roles) lives in TypeScript under domain/src/ and is stored as free text, so the database does not need a migration to add a value.
• History tables (project_order_decisions, project_phase_transitions, inspection_changes, xrf_edit_log, unit_exemption_monitoring_visits) are append-only: no UPDATE or DELETE policy.

Module overview

---

1. Platform

The isolation root and the tables only Complied operates.

Table	Class	Purpose	Key columns
tenants	platform	A white-label organization; the isolation root	slug (subdomain key, also the invoice-number prefix), name, is_active
platform_operators	platform	Complied staff above every tenant	id (auth user), email, is_active
impersonation_sessions	platform	A time-boxed, reasoned "act as this tenant" grant	operator_user_id, target_tenant_id, reason, expires_at, ended_at
audit_log	platform	Append-only record of impersonation and attributed sensitive actions	actor_user_id, acting_tenant_id, target_tenant_id, action, entity_type, entity_id, metadata

Functions: start_impersonation / stop_impersonation write the session and audit rows; current_tenant_id() resolves to the impersonation target while a session is active. Tenants read their own audit history with view_audit_log.

2. Identity, roles and clients

Who a user is, what they may do, and which customers a tenant serves.

Table	Class	Purpose	Key columns
profiles	tenant-owned	A staff login. One user, one tenant. A row here is what makes AuthProvider treat the user as staff	id (auth user), tenant_id, permission_bundle_id, is_active, invited_at, accepted_at
permissions	public reference	The catalog of permission keys	key (PK), category, sort_order, implies text[]
permission_preset_grants	public reference	The default keys of each preset role	(preset, permission_key)
permission_bundles	tenant-owned	A tenant's named role: a set of keys	tenant_id, name, is_preset, preset
permission_bundle_grants	tenant-owned	Role membership	(bundle_id, permission_key)
clients	tenant-owned	A property owner or managing agent the tenant serves	tenant_id, name, is_active, runs_notice_cycle_for_client, requires_notarized_affidavit
client_users	tenant-owned	A client's portal login. No profiles row	client_id, user_id, email, is_active

A project does not store client_id. The client is reached through projects.building_id → tenant_buildings (same tenant) → client_id. Role names are labels; no policy reads them. Details: permissions.

3. Citywide registry

The shared record of NYC buildings and apartments, plus the tenant's tracking layer on top.

Table	Class	Purpose	Key columns
buildings	public reference	One row per real NYC building	bin, bbl, address_line, normalized_address, borough, latitude/longitude, geom_3857, rollups open_hpd_lead, overdue_hpd_lead, open_dob, open_ecb, last_compliance_sync_at
units	public reference	Apartments in a building	building_id, apt_label, floor
tenant_buildings	tenant-owned	"This tenant services this building, for this client"	tenant_id, building_id, client_id, is_active, on-site contacts occupant_name/occupant_contact/superintendent_name/superintendent_contact
compliance_programs	public reference	Lookup for projects.program_id. Holds no rules	id (nyc-lead-paint), jurisdiction, primary_agency, participating_agencies

Two tenants that service the same BIN share one buildings row; anything tenant-authored about a building (client, contacts, notes) lives on that tenant's tenant_buildings row. units has no tenant_id, so tenant facts about an apartment live in the obligation tables (section 14). Apartments arrive from ingestion; staff can correct floor and apt_label but not create units. The deletion guard on buildings and public_events is described in section 4.

Functions: find_or_create_building_by_bin, upsert_building_identities, refresh_building_compliance_summary (the rollup columns), normalize_building_address.

4. Public events and ingestion

What agencies have said about a building, and the machinery that loads it. See ingestion for the pipeline.

Table	Class	Purpose	Key columns
public_events	public reference	One agency record (violation, complaint, hearing, lien, 311). Unique on (agency, source_id)	agency, event_type, building_id (not null), unit_id, status_norm (OPEN/CLOSED/DISMISSED), status_detail, citation_code (the HPD order number), issued_date, due_date, closed_date, raw_data, tombstoned_at
unlinked_events	public reference	Quarantine for events whose building cannot be resolved	agency, source_id, attempted_bin/attempted_bbl/attempted_address, reason, status (pending/resolved/ignored)
sync_runs	public reference	One row per feed execution	feed_id, triggered_by, rows_fetched/rows_upserted/rows_quarantined/rows_tombstoned, outcome
feed_sync_state	public reference	Per-feed cursors shared by the nightly delta and the backfill CLI	feed_id, delta_watermark, backfill_cursor, backfill_status

An event that cannot be tied to a building never lands in public_events; it goes to unlinked_events so the failure is visible. tombstoned_at marks an event that is still OPEN in the database but absent from the latest full fetch.

Views: sync_health_summary (last outcome and recent failures per feed) and unlinked_events_by_reason. RPC: resolve_unlinked_event moves a quarantined row into public_events.

The buildings and public_events tables carry a deletion guard: a statement-level trigger blocks DELETE and TRUNCATE, and an event trigger blocks DROP and DROP COLUMN. Inserts and updates are unaffected. The guard functions live in the separate ingestion_guard schema.

5. Map

The /map reads a Parquet snapshot, not these tables directly. What lives in Postgres is the taxonomy, the event view the exporter reads, and the staleness stamp. See map.

Object	Class	Purpose	Key columns
map_departments, map_categories, map_category_orders	public reference	The map's department and category taxonomy, and which order codes belong to which category	key, dept, label, order_code, category
map_event_source (view, security_invoker)	n/a	A public_events row shaped as a map record; the exporter's source
map_data_version	platform	One row; changed_at is stamped by mark_map_stale() at the end of every sync	changed_at
map_grid_cells	public reference	Pre-aggregated grid counts, rebuilt by refresh_map_grid() at the end of each sync. No application code reads it	level, cx, cy, buildings, open_lead, overdue_lead

map_tenant_overlay() is the one tenant-specific query the map service makes, using the caller's own JWT. It returns nothing without view_map.

6. Projects

The unit of billable work and everything that hangs directly off it.

Table	Class	Purpose	Key columns
projects	tenant-owned	One unit of billable work. Always has a building; a unit is optional	tenant_id, building_id, unit_id, origin (violation/obligation/occupant_request, immutable), program_id, phase, phase_substate, side_state, project_reason, request_method, client_notes
project_event_links	tenant-owned	Which public_events a project addresses	project_id, event_id
project_order_decisions	tenant-owned	Append-only resolution path for one linked order. Changing a decision inserts a new row	project_id, event_id, path (cure/contest/postpone/dismiss), ground, cert_option, instance_state, supersedes_decision_id, decided_by, decided_at
project_phase_transitions	tenant-owned	Append-only history of phase and side-state changes, written by trigger	from_phase, to_phase, from_side_state, to_side_state, changed_by
project_collaborators	tenant-owned (owner side)	The one cross-tenant grant: another tenant works this project	project_id, collaborator_tenant_id, role, invited_by, revoked_at

There is no services table. The services a project needs are derived on read from linked orders and their current decisions (buildDecidedFormBundles then servicesForTracks); the current decision for an order is the latest decided_at per (project_id, event_id). Triggers: projects_check_scope (the unit must belong to the building), origin immutability, projects_check_update_permissions (who may move the phase label versus edit other columns), projects_log_initial_phase and projects_log_phase_transition.

Functions: collaboration_tenant_directory (invitable tenants by name) and project_collaborator_tenants (tenants already on a project).

7. Field visits

A visit is one trip to one building for one service. A project can have many.

Table	Class	Purpose	Key columns
inspections	tenant-owned	One field visit	project_id, service_type (xrf/dust_wipe/paint_chip/abatement), is_clearance, status (scheduled/in_progress/completed/cancelled), outreach_status, scheduled_date, assigned_inspector_id, instrument_id, access_status, access_notes
inspection_events	tenant-owned	Many-to-many: which public_events a visit addresses. Insert and delete only	inspection_id, event_id (unique together), denormalized tenant_id, project_id
inspection_changes	tenant-owned	Append-only history of dispatch fields (date, inspector, status, outreach, access), written only by trigger	field, old_value, new_value, changed_by, changed_at
inspection_contact_attempts	tenant-owned	Outreach log before a visit is booked	contacted_party, method, attempted_at, logged_by
inspection_rooms	tenant-owned	Rooms covered in the visit	room_name, room_type, display_order
inspection_checklist_responses	tenant-owned	Checklist ticks, per room or throughout the apartment	checklist_item_id, inspection_room_id, applies_throughout_apartment, is_checked
inspection_notes	tenant-owned	Free-text or reason-coded notes	note_type, reason_code, room_label
inspection_apartment_exclusions, inspection_room_exclusions	tenant-owned	Why something was not tested, chosen from the phrase catalog	exclusion_phrase_id, inspector_note
floor_plans	tenant-owned	A project's floor plan: sketch, redraw and approval	project_id, status (pending/sketch_uploaded/assigned/redrawn/approved), sketch_path, final_path, assigned_to_artist_id, version_number
checklist_items, room_presets	public reference	Field checklist and default room-name catalogs	name, tag, applicable_rooms; room_name, property_type

The stage a visit shows in the dispatch pipeline (Unscheduled, Contacting, Ready to schedule, Needs inspector, Assigned, In progress, Completed) is computed by dispatchStage from status, scheduled_date, assigned_inspector_id and outreach_status; there is no stored stage column. access_status records what happened at the door on the day (access_granted, knocked_no_answer, cannot_locate, occupant_refused); outreach_status (contacting, access_agreed, no_access) covers everything before it.

The Work tab matches a visit to a track through inspection_events: a visit belongs to a track when its linked event ids intersect the track's covered events and service_type / is_clearance match. A visit that matches no track is shown as unmatched, never deleted. Triggers: inspection_events_sync_owner (tenant and project from the inspection), inspections_record_changes, inspections_check_dispatch_permissions (only manage_inspections reassigns a job), inspection_contact_attempts_mark_contacting.

8. XRF

Instrument readings, the parsed CSV, the generated report and its review. See pdf-reports for the render path.

Table	Class	Purpose	Key columns
xrf_instruments	tenant-owned	The tenant's XRF guns	make, model, serial_number, calibration_rule_id, calibration_due_date, assigned_to_inspector_id
xrf_readings	tenant-owned	One reading or calibration row. Unique per inspection and sequence_index	inspection_id, room, component, side, substrate, pb_mg_cm2, pass_fail, is_calibration, tested, reason_for_no_test, paint_condition, sequence_index
xrf_report_data	tenant-owned	The canonical JSON after CSV parse	inspection_id, canonical, parser_version, template_version, source_format
xrf_report_versions	tenant-owned	One row per generated report	inspection_id, document_id, version_number, is_latest, supersedes
xrf_report_review	tenant-owned	QA on a report	status (pending/passed_review/passed_final/flagged), flagged_for_errors, reviewer_id
xrf_audit_findings	tenant-owned	Machine findings against the checklist, per version	version_id, missing_in_checklist, component_errors, error_summary
xrf_edit_log	tenant-owned	Append-only field-level edits to readings	target, field, old_value, new_value, actor_id
xrf_inspection_photos	tenant-owned	Photos taken during XRF work; the file is in the field-photos bucket	inspection_id, category, storage_path
xrf_report_assets	tenant-owned or shared	Artwork for the report: cover image, contents image, component diagram, firm certificate, CoC template. A row with no tenant_id is shared	asset_kind, storage_path, tenant_id, is_active
xrf_instrument_calibration_rules	public reference	Calibration cadence per instrument model	manufacturer, model, blocks_per_inspection, lead_std_min_mg_cm2, blank_max_mg_cm2, max_inspection_minutes
xrf_component_groups, xrf_component_group_members, xrf_component_sides	public reference	Required component sets and their testable sides	slug, component_slug, sides
xrf_exclusion_phrases	public reference	Allowed "not tested" phrases	phrase, applicable_scope, excludes_components
xrf_room_requirements	public reference	Required groups, components and sides per room type	room_type_slug, required_groups, match_priority

Classification (positive, negative, inconclusive) is computed in domain/src/field/xrf/, not stored as a rule in SQL. commit_xrf_ingest writes the readings and the report data in one transaction. The friction-surface rules live in frictionSurface.ts, not in a table.

9. Lab sampling

Dust-wipe and paint-chip samples, the chain of custody that carries them to a lab, and the labs.

Table	Class	Purpose	Key columns
dust_wipe_samples	tenant-owned	One wipe sample	inspection_id, sample_number, location, surface_type, length_in/width_in/surface_area, is_blank, lab_result_ug_per_sqft, pass_fail, chain_of_custody_id
paint_chip_samples	tenant-owned	One paint-chip sample	inspection_id, sample_number, component, color, lab_result_ppm, pass_fail, chain_of_custody_id
lab_chain_of_custody	tenant-owned	One chain-of-custody record	coc_number, sample_type (dust_wipe/paint_chip), status (pending/shipped/received_by_lab/results_received), laboratory_partner_id, collected_at, shipped_at, results_received_at, document_id
laboratory_partners	tenant-owned	The tenant's labs	name, certification_number, elap_number, nvlap_number, per-sample costs, turnaround days
lab_sample_photos	tenant-owned	Sample photos; files in field-photos	sample_id, sample_type, storage_path

A trigger (lab_chain_of_custody_check_document) requires a CoC's document_id to point at a COC document.

10. Abatement

What was removed or encapsulated, by whom, with what, and the safety record.

Table	Class	Purpose	Key columns
abatement_components	tenant-owned	One component in scope and its work	project_id, inspection_id, public_event_id, order_number, room, component, side, substrate, abatement_method, linear_footage/square_footage, completion_date, clearance_passed, xrf_reading_id
abatement_component_photos	tenant-owned	Before/after photos; files in field-photos	component_id, stage, storage_path
abatement_crew	tenant-owned	Crew on an abatement visit	inspection_id, profile_id, person_name, role, license_type, license_number, license_expiry
abatement_time_entries	tenant-owned	Clock-in and clock-out per crew member	crew_id, work_date, clock_in, clock_out
abatement_materials	tenant-owned	Materials used	component_id, category, name, quantity, unit, supplier
abatement_safety_checks	tenant-owned	Ticked safety items	item_key, checked_at, checked_by

add_abatement_material adds a material atomically. Abatement work is written with perform_abatement_work; crew membership also scopes which jobs a worker can see (my_inspection_ids()).

11. Licenses

Firm and personal certifications, and the audited overrides of a failed license check.

Table	Class	Purpose	Key columns
license_types	public reference	The catalog of certification types	slug, level (tenant/person), is_filing_evidence, typical_validity_months
licenses	tenant-owned	A firm or personal license	license_type_slug, license_number, issue_date, expiry_date, profile_id (null means the firm), document_id
license_validation_overrides	tenant-owned	Append-only record of shipping a report despite a failed license check	inspection_id, license_id, check_code, reason, overridden_by

A license's file is a CREDENTIAL document (tenant level for the firm, user level for a person); licenses_check_document enforces the link. Expiry is judged against the inspection date, not the render date. The Work tab's inspector picker lists only staff holding an active, unexpired license of a type the visit's service requires.

12. Documents and filing

Paperwork: one row and one PDF per document, with levels, sharing, versions and void. Full description in documents.

Table	Class	Purpose	Key columns
document_types	public reference	The type catalog; mirrored by domain/src/docs/documentTypes.ts	code (PK), category, access_class, allowed_levels, signer_role, requires_notary, reuse_policy, collaborator_roles, is_active
documents	tenant-owned (or platform, by level)	One document and its one file	level, anchors (tenant_id, client_id, building_id, unit_id, project_id, profile_id), type, status (draft/issued/signed/notarized/filed), storage_path (unique), client_visible, shared_client_id, supersedes_id, is_current, voided_at, void_reason
document_orders	tenant-owned	Which HPD order number a project document answers. Keyed on the order number printed on the form. Insert and delete only	document_id, order_number, project_id
filing_packages	tenant-owned	One point-in-time assembly of a project's filing documents	project_id, status, created_by
filing_package_documents	tenant-owned	Package membership	filing_package_id, document_id

View document_list (security_invoker) is the one query behind the /documents page and the portal: documents plus the type label, category, access class, owning client, building, unit and uploader names. Functions: document_writable, document_owner_client_id, building_client_id, tenant_tracks_building.

13. Money

What a job costs, what was quoted, what was billed, and what was paid out. Financial tables have no collaborator access.

Table	Class	Purpose	Key columns
rate_cards	tenant-owned	Flat prices per service type	service_type, base_price, unit_price, unit_label, is_default
abatement_rates	tenant-owned	Abatement price list: one flat price per component, substrate and method	component, substrate, method, price
proposals	tenant-owned	A priced proposal header	project_id, status (draft/sent/accepted/rejected/expired), subtotal, total, valid_until, document_id
proposal_line_items	tenant-owned	Proposal lines	proposal_id, service_type, description, quantity, unit_price, amount
invoices	tenant-owned	An invoice	invoice_number, status (draft/sent/paid), subtotal, total, sent_at, paid_at, document_id, billed_client_id
invoice_line_items	tenant-owned	Invoice lines	invoice_id, service_type, quantity, unit_price, amount
invoice_number_counters	tenant-owned	Last number issued per tenant and year	(tenant_id, year), last_number
vendor_payments	tenant-owned	Payments to inspectors, labs and subcontractors. Never visible to the portal	project_id, vendor_name, amount, status (pending/paid), paid_at

allocate_invoice_number(tenant_id) is the only way a number is drawn: atomic, SECURITY DEFINER, and it refuses a caller outside the tenant. The invoice number is the tenant slug, the year and a four-digit counter. The invoices_stamp_billed_client trigger stamps billed_client_id from the building's client the moment an invoice leaves draft; the portal reads that column.

14. Obligations

Recurring duties per apartment. The rules run on read in TypeScript (computeRpoNeeds, classifyTier, nextMonitoringDue in domain/src/programs/nyc-lead-paint/); these tables store only what a person observed or did. There is no evaluator job and no materialized worklist.

Table	Class	Purpose	Key columns
unit_rpo_records	tenant-owned	The annual-notice ladder and record-keeping duties, one row per tenant, unit and audit year	unit_id, audit_year, annual_notice_delivered, annual_notice_response_received, child_under_six_resides (three-state), access_attempts (jsonb), annual_investigation_*, turnover_*
unit_compliance_window	tenant-owned	Standing facts, one row per tenant and unit	child_under_6_present (nullable, no default), last_turnover_date, ll31_tested, ll31_test_date, ll31_qualifying_project_id
unit_exemptions	tenant-owned	Lead-Safe and Lead-Free applications. History is kept: a denial and a later grant are two rows	exemption_type, lifecycle_status (draft/applied/filed/granted/denied/withdrawn), granted_date, denied_date
unit_exemption_monitoring_visits	tenant-owned	Append-only log against a granted exemption	unit_exemption_id, visit_date, result (compliant/failed), evidence_document_id
obligations	tenant-owned	The ledger of actioned needs: a row exists only when a person spawns a project, snoozes or dismisses a need	need_kind, occurrence_key, status (open/snoozed/dismissed/project_created/completed), project_id, snoozed_until

A need that nobody has acted on has no row. (tenant_id, unit_id, need_kind, occurrence_key) is unique, which is how a freshly computed need finds its ledger row. child_under_6_present is nullable on purpose: null means nobody asked, false means somebody asked and the answer was no. Only a granted exemption suppresses anything. RPCs: obligation_facts_for_portfolio (a paged join, no rules) and obligation_unit_coverage (apartments on file versus dwelling units).

15. Branding and notifications

How a tenant looks, and how messages leave the system.

Table	Class	Purpose	Key columns
tenant_branding	tenant-owned	Letterhead, colors and sender identity. One row per tenant	company_name, primary_color, logo_storage_path, logo_icon_storage_path, sender_name, sender_email, reply_to_email, footer_text, license_numbers, is_active
client_branding	tenant-owned	Portal chrome only; PDFs ignore it	client_id, display_name_override, logo_storage_path, footer_text
notification_preferences	tenant-owned (personal)	One user's channel and digest settings; no tenant_id	user_id, email_enabled, sms_enabled, email_daily_digest, digest_time_of_day, event_type_overrides
notification_log	tenant-owned	In-app (and queued SMS) notifications. Written only by the notify function; the recipient may set read_at	recipient_user_id, channel, event_type, status (queued/sent/failed), read_at
email_outbox	platform	The polled email queue. No authenticated grant	status, attempts, next_attempt_at, template, payload, recipient_email, unsubscribe_token
email_send_log	platform	Provider attempts per outbox row	outbox_id, provider_message_id, status, error_message
email_suppressions	platform	Addresses that bounced or complained. Global by address, not per tenant	email, reason
email_unsubscribe_tokens	platform	One-time unsubscribe tokens	email, token, used_at
saved_views	tenant-owned (personal)	A user's named filter sets, per scope (for example the violations list and documents:staff)	user_id, scope, name, filters

enqueue_email() is the only insert path into email_outbox; only the service role may call it. email_outbox.status runs queued, sending, sent, failed, suppressed or queued_unsent (no provider configured).

---

Storage buckets

Bucket	Holds	Path	Access follows
documents	Every document PDF or upload	{tenant_id}/{document_id}/…	The documents row whose storage_path names the object
field-photos	XRF photos, sample photos, abatement photos, floor-plan sketches and finals	{project_id}/xrf/…, {project_id}/lab/…, {project_id}/floor-plans/…	The capture row and the caller's field permissions
report-assets	Report artwork (PDF, PNG, JPEG)	{tenant_id}/… or _shared/…	Read by the folder's tenant staff (anyone signed in for _shared/); written with manage_settings
report-fonts	Fonts the report renderer fetches	Flat	Public read (the renderer runs outside Supabase)
branding	Tenant and client logos	{tenant_id}/logo.*, {tenant_id}/{client_id}/logo.*	Read by the tenant's staff and, for its own logo path, that client's portal users; written with manage_settings

Functions and views at a glance

Object	Job
current_tenant_id(), current_client_id(), is_platform_operator()	Resolve the caller's scope from the JWT
has_permission(key), my_permission_keys()	The permission check and the list the browser loads
my_inspection_ids(), my_project_ids(), my_building_ids()	Field-job assignment scope, usable inside policies
has_active_collaboration_grant(project, tenant), has_active_collaboration_grant_for_role(project, tenant, roles)	Cross-tenant project access and role-scoped field writes
allocate_invoice_number, enqueue_email	Atomic numbering and the email queue entry
map_tenant_overlay, mark_map_stale, refresh_map_grid	Map support
commit_xrf_ingest, add_abatement_material	Multi-row writes that must be one transaction
document_list, sync_health_summary, unlinked_events_by_reason (views)	Read models