Schema — every table

Generated against src/integrations/supabase/types.ts (the live rebuild schema). Why is taken from migration comments and REQUIREMENTS.md.

Three RLS classes: platform, public reference, tenant-owned. Views at the end.

Conventions you will see everywhere:

• Tenant-owned rows: non-null tenant_id → tenants, plus a trigger that copies tenant_id from the parent project/client so the client cannot spoof it.
• id is uuid unless noted (permissions.key is the PK; invoice counters are composite).
• created_at / updated_at omitted below unless they are the point of the table.

---

1. Platform

tenants

The white-label organization. Isolation root.

Column	Meaning
name, slug	Display + URL key (secureenv, abated)
is_active	Soft disable

Why: D3/D5/D14. There is no firms table.

platform_operators

Complied employees. Global, not tenant-scoped.

Column	Meaning
id	Auth user id
email, full_name, is_active

Why: HQ / impersonation. Gated by is_platform_operator(). No rebuilt HQ UI.

impersonation_sessions

Time-bounded "act as this tenant."

Column	Meaning
operator_user_id	Who
target_tenant_id	Which tenant
reason	Required
started_at, expires_at, ended_at	Clock

Why: REQUIREMENTS §5.2. Written by start_impersonation / stop_impersonation.

audit_log

Append-only actions (impersonation, collaborator acts, sensitive writes).

Column	Meaning
actor_user_id, acting_tenant_id	Who, as whom
target_tenant_id	Tenant affected
action, entity_type, entity_id	What
reason, metadata

Why: Defensibility years later. Not a full change-data-capture log of every row.

---

2. Identity and permissions (tenant)

profiles

Staff login. One user, one tenant. Presence of a row is what AuthProvider uses to pick staff vs portal.

Column	Meaning
id	auth.users id
tenant_id	Home tenant
email, full_name, is_active
permission_bundle_id	Named set of keys, not a role enum

Why: D5 + owner ruling: roles are toggleable bundles. Policies call has_permission(key), never bundle.name.

permissions

Catalog of keys (manage_projects, advance_projects, manage_inspections, manage_documents, manage_licenses, manage_invoices, manage_notifications, manage_tenant, manage_buildings, …).

permission_bundles

Per-tenant named sets. Seeded presets match the old role labels: Tenant Admin, Project Manager, Inspector, Back Office. Names are labels only.

permission_bundle_grants

(bundle_id, permission_key) membership.

clients

A customer of the tenant (owner / managing agent).

Column	Meaning
tenant_id, name, is_active

Why: D7. Projects do not store client_id; join through tenant_buildings.

client_users

Portal accounts. No profiles row. user_id → Auth.

Column	Meaning
client_id, user_id, email, full_name, is_active

Why: Separate account type (D5). RLS uses current_client_id().

---

3. Citywide registry (public reference)

buildings

One row per real NYC building. Not tenant-owned.

Column	Meaning
bin, bbl	City keys
address_line, normalized_address, borough, zip	Address; normalized form is the third ingest resolver
latitude, longitude	Map
total_units, year_built	PLUTO-ish facts
open_hpd_lead, overdue_hpd_lead, open_dob, open_ecb	Layer 2b rollups
last_compliance_sync_at	Last summary refresh

Why: D10. Two tenants servicing the same BIN share this row. Seed migration loads a handful of real buildings; Layer 1 (fetchBuildingIdentities) can fill citywide identity locally — not yet run against prod.

units

Apartments on a building. Also public reference.

Column	Meaning
building_id, apt_label, floor

Tenant-specific facts (exemption, RPO) were specified as separate tenant-scoped tables. Those tables were not built. Do not hang tenant data on this row.

tenant_buildings

"This tenant watches/services this building, optionally for this client."

Column	Meaning
tenant_id, building_id	Pair (unique in practice per tenant+building)
client_id	Which client this portfolio item belongs to
is_active, notes

Why: The only place a tenant's portfolio lives. Portal visibility of buildings flows from here.

compliance_programs

Lookup for projects.program_id. Not the rulebook.

Column	Meaning
id	'nyc-lead-paint' (only seed)
name, jurisdiction, primary_agency, participating_agencies

Rules live in domain/src/programs/nyc-lead-paint/.

---

4. Compliance intelligence (public reference + ops)

public_events

Canonical agency event. The application must not read legacy per-agency tables; they are gone.

Column	Meaning
agency, event_type, source_id	Identity; unique (agency, source_id)
building_id	NOT NULL. Unresolvable events never land here
unit_id	Optional apartment
status_norm	OPEN / CLOSED / DISMISSED
status_detail	Agency-native status text
citation_code	HPD order # etc.
issued_date, due_date, closed_date
description, raw_data	Human + original JSON
tombstoned_at, tombstone_reason	OPEN in DB but missing from latest full fetch

Why: D19. Ingestion is agency-wide; workflow is program-scoped. A DOB façade violation can sit here without a façade program.

unlinked_events

Quarantine when BIN/BBL/address cannot resolve.

Column	Meaning
agency, event_type, source_id, raw_data	Same identity as events
attempted_bin, attempted_bbl, attempted_address	What we tried
reason	Why it failed
first_seen_at, last_seen_at	Idempotent upsert
status, resolved_at, resolved_building_id	Manual resolve path (resolve_unlinked_event)

Why: Silent null building_id was the old defect. This table is the visible failure.

sync_runs

One row per feed execution.

Column	Meaning
feed_id, agency, batch_id
triggered_by	cron | manual
started_at, finished_at, duration_ms
rows_fetched, rows_upserted, rows_quarantined, rows_tombstoned
outcome, error_message

Why: Ingest without run logs is how feeds die silently.

Views

sync_health_summary — last outcome and recent failure count per feed.
unlinked_events_by_reason — quarantine grouped for an ops dashboard that does not exist yet.

---

5. Projects and pipeline (tenant-owned)

projects

Leaf of D7. Unit of billable work.

Column	Meaning
tenant_id	Owner tenant (D4)
building_id	Always set
unit_id	Null = building-scoped work
origin	Immutable: violation | obligation | occupant_request
program_id	FK to compliance_programs, default nyc-lead-paint
phase	intake…closed
phase_substate	Within-phase progress, not a gate
side_state	blocked | on_hold | cancelled
created_by

Why: The whole OS is "turn a trigger into a project and prove you finished it." No client_id column — see tenant_buildings.

Work tab rework (2026-09): project_type, assigned_inspector_id, scheduled_date, and access_arranged are dropped. Each existed for exactly one consumer — the deleted domain/src/phases/gates.ts's intake→scheduling and scheduling→field gate conditions — and the three scheduling columns, being project-level singletons, couldn't express a project worked by two tenants on two separate visits. Project type is now derived live (projectTypeLabel(servicesForTracks(...)), domain/src/field/serviceRequirements.ts); per-visit scheduling lives on inspections (below), joined to the order(s) it addresses via inspection_events.

project_event_links

Which public_events this project addresses.

Why: Named to avoid the old deployments / project_violations fight. No lifecycle columns; status lives on the event and the project separately (File-and-Resolve auto-diff was not ported). Its inspection_id column (1:1 visit↔event) was dropped in the Work tab rework — superseded by inspection_events' many-to-many shape, which can express a track needing both an abatement visit and a separate clearance visit.

project_phase_transitions

Append-only history. Written by the projects_log_phase_transition trigger on every phase/side_state update (setProjectPhase / setProjectSideState write the columns from the client; Rung 6 deleted advance-project-status). See GATING.md and docs/history/DESIGN-C.md for candidate preconditions — design only, no gating behaviour shipped.

Column	Meaning
from_phase, to_phase, from_side_state, to_side_state
changed_by, changed_at

Why: Proof trail of ops-label changes. Phase is not an authority.

project_collaborators

Cross-tenant invite on one project.

Column	Meaning
project_id	Owner's project
collaborator_tenant_id	Invitee
role	field_execution | abatement | clearance_sampling | lab_coordination (code catalog; column is free text)
invited_by, revoked_at

Why: D4 — the only cross-tenant surface. Financial tables ignore this grant. Invite roles are a TypeScript catalog (field_execution, abatement, clearance_sampling, lab_coordination). Staff pick a tenant via collaboration_tenant_directory() rather than pasting a UUID.

project_order_decisions

Append-only resolution path for one linked HPD order (event_id → public_events). Changing your mind inserts a new row with supersedes_decision_id; there is no UPDATE/DELETE policy.

Column	Meaning
event_id	The linked public_events row
path	cure | contest | postpone | dismiss
ground	Contest ground, when path is contest
cert_option	HPD cert Option 3/4/5 (nullable; UI does not always write it)
instance_state	known_positive_prior_test | contest_came_back_positive — column exists; domain does not consume it yet
rationale, decided_by, decided_at

Why: Decide tab (Rung 3). Current decision = latest decided_at per (project_id, event_id). Drives buildDecidedFormBundles → derived services.

---

6. Field (tenant-owned unless noted)

inspections

One field visit. Replaces legacy deployments.

Column	Meaning
project_id, tenant_id
service_type	Matches catalog
status	scheduled | in_progress | completed | cancelled
scheduled_date, completed_date
assigned_inspector_id, instrument_id
is_clearance	Post-abatement dust wipe (CoC / independent-lab rule)
access_status, access_notes	Includes occupant_refused (never tenant_refused)

assigned_inspector_id/scheduled_date/access_status are set per visit from the Work tab's Tracks panel (updateInspection), with the inspector picker filtered to staff holding an active, unexpired licenses row of a type the visit's service_type requires (listEligibleInspectorsForService) — not every tenant profile.

inspection_events

Many-to-many: which public_events a field visit addresses. Keyed on event_id (an FK into public_events), not order_number — events are per-unit, so two events sharing an order number can be two different apartments, and the decision engine (project_order_decisions.event_id) keys the same way. Mirrors document_orders' SHAPE (denormalized tenant_id/project_id, BEFORE INSERT trigger deriving them from the owning inspections row, no UPDATE policy) but not its key — a document prints violation numbers on its face and needs order_number's permanence; a field visit prints nothing.

Column	Meaning
inspection_id, event_id	unique together
tenant_id, project_id	Denormalized from the owning inspection

Why: The Work tab's buildProjectTracks matches a visit to a track's field-visit task only when the visit's linked event ids intersect the track's coveredEventIds AND service_type/is_clearance match — scoped per track, not project-wide string equality. A visit whose links don't currently match any track surfaces as "Not on any current track" (never deleted, never destructive) rather than silently vanishing when a decision changes.

inspection_rooms

Rooms in the visit (room_name, room_type, display_order, notes).

inspection_checklist_responses

Checklist ticks, optionally per room or "throughout apartment."

inspection_notes

Free-text / reason-coded notes (note_type, reason_code, room_label).

inspection_apartment_exclusions / inspection_room_exclusions

Selected xrf_exclusion_phrases at apartment or room scope (why a component was not tested).

xrf_instruments

Tenant gun inventory (make, model, serial_number, calibration_rule_id, calibration_due_date, assignee).

xrf_readings

One instrument reading (or calibration row).

Notable columns: room, component, side, substrate, pb_mg_cm2, pb_pf, pb_uncertainty, pass_fail, is_calibration, calibration_block / calibration_matrix, tested, reason_for_no_test, paint_condition, sequence_index, test_number, plus device metadata.

Why: Defensible XRF file. Classification is computed in domain/src/field/xrf/determination.ts (rulebook routing table; legacy ingest was binary Positive/Negative only).

xrf_report_data

Canonical JSON after CSV parse (parser_version, template_version, source_format, canonical).

xrf_report_versions

Versioned generated report. document_id → Storage-backed PDF. is_latest, supersedes, version_number.

xrf_report_review

QA on a version (status, flagged_for_errors, reviewer, notes).

xrf_edit_log

Append-only field-level edits (target, field, old_value, new_value, actor_id).

xrf_audit_findings

Machine findings vs checklist (missing_in_checklist, component_errors, error_summary).

dust_wipe_samples / paint_chip_samples

Lab-bound samples: sample_number, location/surface, lab_result_ppm, pass_fail, dates, optional chain_of_custody_id.

abatement_components

What was removed/encapsulated: room, component, method, footage, waste bags, clearance_passed.

laboratory_partners

Tenant's labs (cert numbers, ELAP/NVLAP, pricing, turnaround). No manage UI.

lab_chain_of_custody

Required for lab services. Wizard can list CoC rows; createChainOfCustody has no UI caller. Does not gate phase.

Column	Meaning
coc_number, sample_type, status
laboratory_partner_id
collected_at / by, shipped_at, received_by_lab_at, results_received_at	Chain

floor_plans

One current plan per project (version_number). document_id is sketch or final depending on status — not two FKs. Artist assignment columns exist; revision-history table does not.

Field catalogs (public reference, USING (true) read)

Table	Why
room_presets	Default room lists by property type
checklist_items	Field checklist catalog
xrf_instrument_calibration_rules	SciAps X-550 / Viken Pb200i cadence (blocks, blanks, lead-std, max minutes)
xrf_component_groups + xrf_component_group_members	Required component sets
xrf_exclusion_phrases	Allowed "not tested" phrases
xrf_room_requirements	Per room-type required groups/components/sides

Friction-surface rules stay in frictionSurface.ts — there is no xrf_friction_components table (Contradiction 8b).

---

7. Documents and money (tenant-owned; no collaborator RLS)

licenses

EPA firm (profile_id null, license_type = 'epa_firm') or personal inspector/supervisor licenses.

Column	Meaning
license_type, license_number, issue_date, expiry_date
issuing_body, is_active, notes
profile_id	Null = firm-level on the tenant

Lab certs stay on laboratory_partners.

documents

Generated or uploaded PDF metadata. Types are free text; codes live in domain/src/docs/documentCodes.ts (PROPOSAL, INVOICE, INSP-RPT, XRF-AFF, AF-5, CONTEST, …).

Column	Meaning
project_id, type, status
storage_path	{tenant_id}/{project_id}/{uuid}.pdf
signer_name, signed_at, notary_name, notarized_at	No DocuSign
created_by

Order linkage is document_orders, not a column on documents. (governing_order was dropped.)

Why: Paperwork is the product. Client portal sees these rows (and Storage objects, after the Phase 8 bucket-RLS fix).

document_orders

Which HPD order number a document answers. Keyed on order_number (what prints on the form), not event_id. Denormalized tenant_id/project_id; no UPDATE policy.

Column	Meaning
document_id, order_number
tenant_id, project_id	From the owning document

Why: Rung 5 document slots — File-tab checklist is reuse-policy aware per order.

filing_packages

One docs_qa submission bundle per project (not one row per HPD form).

filing_package_documents

M2M filing_packages ↔ documents.

rate_cards

Flat price per service_type (unit_price, unit_label, is_default). No tax/discounts/per-reading vs per-visit split.

proposals / proposal_line_items

Priced proposal header (status, subtotal, total, valid_until, optional document_id) plus per-service lines. Generated by generate-proposal-pdf. Unpriced services are skipped — total can be $0 with HTTP 200.

invoice_number_counters

(tenant_id, year) → last_number. Drawn only via allocate_invoice_number() (atomic, SECURITY DEFINER).

invoices

Column	Meaning
invoice_number, status	draft / sent / paid / …
subtotal, total
sent_at, paid_at	Billing gate
document_id	PDF

Portal: client sees non-draft only.

invoice_line_items

description, service_type, quantity, unit_price, amount.

vendor_payments

Pay inspectors/labs/subs (vendor_name, amount, status, paid_at). Never portal-visible. Billing gate has_vendors_paid.

---

8. Branding and notifications

tenant_branding

Letterhead + portal colors + sender identity. One row per tenant.

Column	Meaning
company_name, primary_color, secondary_color, logo_storage_path
sender_name, sender_email, reply_to_email	Email
phone, website, address, footer_text, license_numbers
portal_domain	Record-keeping only
is_active	Inactive → domain merge uses neutral (non-Complied) fallback

client_branding

Portal chrome only: display_name_override, logo_storage_path, footer_text. PDFs ignore this table.

notification_preferences

Owned by user_id = auth.uid() (no tenant_id). Email/SMS toggles, digest time, per-event overrides.

notification_log

In-app (and stub SMS) record. No INSERT policy for authenticated — notify under service_role is the only writer. Recipients may set read_at.

email_outbox

Polled queue (status, attempts, next_attempt_at, payload, template, unsubscribe_token). Zero policies for authenticated. Writes via enqueue_email().

email_send_log

Provider attempts (provider_message_id, status, error_message).

email_suppressions / email_unsubscribe_tokens

Global by email address, not tenant-scoped. A bounce suppresses every tenant.

saved_views

Per-user named filter sets (scope, name, filters JSON). Wired on /violations via SavedViewsMenu. Not a citywide intelligence dashboard.

---

9. RPCs that are part of the model

Function	Job
current_tenant_id() / current_client_id()	JWT → scope
has_permission / is_platform_operator	AuthZ
has_active_collaboration_grant	Project row visibility
has_active_collaboration_grant_for_role	Field writes; role arrays vary by table (field_execution, plus lab_coordination / abatement where applicable)
collaboration_tenant_directory / project_collaborator_tenants	Invitable tenants + names already on a project
map_buildings_in_view	Viewport query for /map
refresh_building_compliance_summary / upsert_building_identities / find_or_create_building_by_bin	Layer 1/2b ingest helpers
allocate_invoice_number	Atomic invoice numbers
enqueue_email	Only caller-reachable insert into outbox
start_impersonation / stop_impersonation
resolve_unlinked_event	Quarantine → public_events
normalize_building_address / normalize_borough_name	Ingest keys
update_own_profile	Safe profile self-edit

---

10. What you will not find (and might look for)

Expected from REQUIREMENTS / old app	Reality
obligations, unit_compliance_*, unit_rpo_records, follow_ups	Not created
project_services	Dropped — services are derived
photos / before-after	Not created
floor_plan_revisions	Deferred
workflow_*	Retired
firms	Retired
gates.ts / advance-project-status	Deleted in Rung 6
invoices Stripe columns	Retired
Per-agency violation tables	Retired; use public_events.agency

Column-level detail for a single table is always in the creating migration under supabase/migrations/ (search create table public.<name>).