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 aBEFORE INSERTtrigger copiestenant_id(and oftenproject_id) from the parent row, so a client cannot spoof it. idisuuid. Exceptions:permissions.key,document_types.code,license_types.slugandcompliance_programs.idare text keys;invoice_number_countersis 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, field_services (services the office set them up for; with the licence, decides who a visit can be assigned to) |
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. name is unique per tenant, trimmed and case-insensitive (clients_tenant_name_unique) | tenant_id, name, is_active, runs_notice_cycle_for_client, requires_notarized_affidavit; from setup answers: legal_name, entity_type, main_contact_name, billing_same_as_main, billing_contact_* |
client_contacts | tenant-owned | The named people at a client company (owner, managing agent, accounts payable). Staff read with view_clients and write with manage_clients; the client's own portal logins read their company's (client_contacts_select_portal). tenant_id is stamped from the client | client_id, name, role (free text), email, phone, is_primary (at most one per client; changed only through set_primary_client_contact(), one transaction under the caller's RLS) |
client_users | tenant-owned | A client's portal login. No profiles row. is_active = false is "access off"; Invited = invited_at set and accepted_at null | client_id, user_id, email, phone, is_active, invited_at, accepted_at, invited_by |
work_orders | tenant-owned | A job a client requested from the portal. SELECT policies only; written by submit_work_order(), cancel_work_order(), accept_work_order(), decline_work_order() | client_id, building_id, unit_id, services, source_event_id, preferred_dates, access_notes, status (submitted/accepted/declined/cancelled), project_id, decline_reason |
building_requests | tenant-owned | A building a client asked to add. SELECT policies only; written by submit/cancel/approve/decline_building_request(). Never writes buildings or units | client_id, building_id or typed address_line/borough/zip, bin, bbl, relationship, superintendent_*, occupant_*, apartments (free text), status, decline_reason, requested_by_name |
client_onboarding | tenant-owned | A client's "Setting up" answers from the portal, staged for staff, one row per client. SELECT policies only (staff: view_clients); written by save_client_onboarding() / submit_client_onboarding(). Sending runs apply_client_onboarding(): company details to clients, the super to the client's tenant_buildings, child under 6 and move-in to unit_compliance_window. Never writes buildings, units or public_events | client_id (PK), answers (jsonb, one key per step; shapes in domain/src/portal/onboarding.ts), submitted_answers (as last sent), last_step, attested_at, submitted_at (cleared by an edit), last_submitted_at, submitted_by_name |
client_onboarding_reviews | tenant-owned | Staff marks on a client's setup answers (verified, invited, project, requested, dismissed), one per answer path. Staff-only SELECT (view_clients); written by review_client_onboarding() | client_id, path, status, note, ref (what the action made), reviewed_by_name |
client_document_requests | tenant-owned | Papers staff asked a client for, answered by a portal upload. SELECT for staff (view_clients) and the client; written by request_client_document(), cancel_client_document_request(), portal_finish_upload() | client_id, building_id, document_type (COI / CLIENT-REC), title, status (open/received/cancelled), document_id |
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, year_built/total_units and PLUTO's lot attributes (num_floors, bldg_area, num_bldgs, bldg_class, landmark_name, historic_district, condo_no; Layer 1, read by client onboarding), HPD's own hpd_building_id/hpd_registration_id/hpd_house_range, 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, apt_key (generated: apartment_key(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/property_manager_name/property_manager_contact (written only with manage_buildings, tenant_buildings_guard_contacts) |
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.
HPD spells one apartment several ways (1C, APT1C, 1-C, .1C), so units match on apt_key,
not the raw label: public.apartment_key(text) is the SQL twin of apartmentLabelKey
(domain/src/ingestion/apartments.ts). The rule strips a leading APT/APARTMENT (with or without
a separator), UNIT/SUITE/STE or NO/NUMBER before a separator, and #; then spaces,
dots, # and commas, and every - except one between two digits (2-2E ≠ 22E). / is kept
(12 1/2 ≠ 1212). formatApartmentLabel is the one display form ("Apt 1C", "Floor 2",
"Penthouse", "Rear"); what HPD wrote stays on each record as public_events.unit_label, and
violation documents print that. unit_label_variants(unit_id) lists a unit's HPD spellings (the
unit page's "Also known as"). Units split before the rule existed are folded together by
ingest merge-units, which logs each fold in unit_merges (service-only). (building_id, apt_key) is
unique, so a building has one unit per apartment key, and ingest's unit insert returns the
existing unit for another spelling of it.
Pairs the rule can't settle (2FL/2, 1A/A1, 2-2E/22E) share an apartment_loose_key; a
trigger on units records each such pair in apartment_match_pairs (backfilled for existing units)
for review. The suggest-apartment-matches edge function asks TypeSafe's Jev (typesafe/jev, a decision
model, through Workers AI and AI Gateway) one true/false question per pair and writes the answer
(ai_verdict, ai_confidence, ai_reason) onto it; only a person changes status, through
decide_apartment_match (manage_buildings, one of the caller's buildings). "Same" runs
merge_unit_into, the one merge path (ingest merge-units uses it too): it repoints every
unit_id reference, the pairs and aliases, logs to unit_merges and deletes the merged unit. When
the two keys differ, the merged key goes in unit_label_aliases, which ingest reads so the next
record spelled that way links to the survivor. merged_unit_survivor(unit_id) follows unit_merges
from a deleted unit to the one that holds its records, so the unit page can redirect an old link.
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, tombstoned_at; agency facts as columns: hpd_class, hpd_rent_impairing, hpd_certify_by, hpd_certified_date, penalty_amount, balance_due, hearing_result, issuing_agency, agency_order_code, agency_status_text, unit_label |
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.
portfolio_events (view, security_invoker) is public_events on the caller's tracked buildings:
joined to tenant_buildings for the caller's tenant (staff) or client's tenant (portal), so
tenant_buildings RLS decides which buildings a caller sees, and each appears once. It adds
client_id, building_address and apt_label. My Buildings and the portal read violations here, so
no request URL carries the portfolio's building ids; an obligation facet's matching buildings go in
the body of portfolio_events_in(p_building_ids), the view on just those buildings.
count_portfolio_events(p_where, p_buckets) returns
many exact counts over it in one pass: each bucket is a list of the conditions the browser compiles
for the list (ViolationCond, as JSON), translated against a fixed column list.
group_portfolio_events(p_where, p_group, p_buckets, p_limit) answers the same buckets once per
group: the levels are columns from a fixed list (client, building, apartment, agency, class, order
code…) or derived levels made of the same conditions ({key, cases, else}, the first matching case
names the row), and it returns [{k, n, b}], biggest group first, at most p_limit groups. It
backs every figure that describes the whole filtered portfolio while the rows themselves are paged:
the violations tree's group headers, breakdowns and lookups, and the Buildings and Clients lists'
per-row violation counts. Both RPCs are security invoker over the view, so they count nothing
the caller could not list.
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_sources | public reference | One row per public_events source-id prefix the map shows: department, fallback category, which columns are its order code and agency status | src, dept, fallback_category |
map_event_source (view, security_invoker) | n/a | A public_events row shaped as a map record, through map_event_sources; 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 | Retired pre-aggregated grid counts. Nothing reads or rebuilds it any more; it is dropped with refresh_map_grid() (#102) | 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/client_request, immutable; only accept_work_order() makes client_request), 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 (616/624 only: known positive from a prior test, or contest came back positive; set from the Decide panel, closes the contest and routes to abatement), 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_reviews | tenant-owned | Append-only compliance sign-offs: who, when, a note, and a snapshot of the reports on file and the facts behind them. Read with view_projects or review_xrf_reports, written with review_xrf_reports as the caller (reviewed_by = auth.uid()). No update or delete | project_id, reviewed_by, reviewed_at, note, report_versions (jsonb), facts (jsonb) |
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 violations
and their current decisions (project_plan_inputs, then planProject's tracks, then
servicesForTracks); the current decision for a violation 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_check_revive_scope (below),
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).
A violation's scope is project_scope(public_events), a computed field readable and filterable
through PostgREST (public_events?select=id,project_scope&project_scope=eq.<unit>): null means the
building (the violation has no unit_id, or it is an HPD records order, map_category_orders
category hpd.lead_rpo), otherwise its unit_id. It is the one place this rule lives.
A project is one apartment or the building, never several apartments, and a violation sits in at
most one live project (phase not closed, not cancelled) per tenant; another tenant may hold the
same public violation. create_projects_from_violations(p_building_id, p_event_ids, p_project_reason, p_request_method, p_client_notes) creates one violation-origin project per
project_scope among the ticked violations, each with its links, in one transaction, and returns
{ project_id, unit_id, event_count } per project (the building's first). SECURITY INVOKER, so
the caller's own manage_projects policies write the rows. It creates nothing when an id is not a
violation on that building, or when a violation is already in a live project of the caller's
tenant: project_already_linked (errcode 23505, the message names the project, DETAIL is its
id). It advisory-locks each violation id (project_event_links:<id>) so two callers can't both
place it. accept_work_order() takes the same lock and, when the work order's violation is
already in a live project of the tenant, attaches the work order to that project (adding a field
job only for a requested service it has none scheduled or in progress for) instead of opening a
second one. submit_work_order() refuses a work order whose source violation's scope isn't the
chosen apartment (or no apartment, for a building-scope one).
The database itself refuses a link that breaks the rule. project_event_links_check_scope, a
BEFORE INSERT trigger on project_event_links, raises project_scope_mismatch (errcode 23514)
when the violation's project_scope is not the project's unit_id, or when the violation is
already in another live project of the same tenant. It takes the same per-violation advisory lock,
so two concurrent inserts queue and the second is refused, and it is SECURITY DEFINER, so it sees
every project of the tenant whatever the writer's own row security shows. Its message never names
another tenant's project; a signed-in writer from another tenant is left to the insert policy.
projects_check_revive_scope refuses making a cancelled or closed project live again while one of
its violations is now in another live project of the tenant. Links made before the rule (#405
B-2's report) are not touched; unit merges move public_events and projects together and add no
link.
project_plan_inputs(p_project_ids uuid[]) is the read behind a project's plan (planProject in
domain/src/programs/nyc-lead-paint/projectPlan.ts, whose PlanInputs type matches it column for
column). It answers one row per project the caller can read: project_id, unit_id, violations
(the linked public_events, narrowed, as jsonb), decisions (the latest project_order_decisions
row per linked violation, as jsonb) and open_orders_in_scope (the order numbers of open, live,
non-legacy HPD lead orders whose project_scope equals the project's unit_id, linked to it or
not). One row per project, so PostgREST's 1,000-row cap never cuts a project's violations short.
SECURITY INVOKER: the browser reads under its own row security, an edge function's service role
reads any project. Code reads it through one loader per side, loadPlanInputs in
src/data/projectPlan.ts and supabase/functions/_shared/projectPlan.ts; each file also has
loadRequirementOverrides, the tenant's document_requirement_overrides, the plan's other input.
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. Append-only | contacted_party, method (call/text/email/letter/certified_mail/in_person), attempted_at, logged_by, contact_name, outcome (no_answer/left_message/reached/access_agreed/refused/wrong_contact/sent; null on older rows), notes |
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 Field 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. assigned_to_inspector_id is who carries it; an XRF visit assigned to them takes it as inspections.instrument_id | 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. applicable_scope orders the pickers: a room lists the room phrases, "Throughout Apartment" lists all of them, apartment-wide first | 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.
Counts of these rows are read from the server, never by listing them, because a list stops at
PostgREST's 1,000-row cap (reading lists).
field_job_signal_counts(p_inspection_ids, p_project_ids) returns one jsonb value with the
readings (real and calibration), photos and lab samples (total, in custody, with a result) per
visit, and the abatement photos per project by stage. The field workspace's job checklists and the
crew's containment page use it. xrf_reading_counts(p_tenant_id) returns the readings per visit
for one firm, for Performance. Both are security invoker, so they count only rows the caller
could list.
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, order_number / hpd_description / public_event_id (the violation surface a violation-driven chip was taken for) |
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 Field 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_requirement_overrides | tenant-owned | The firm's changes to the rulebook's required documents (Settings -> Document requirements), at scope route, order, family or all; the most specific wins (resolveOrderRequirements in domain/src/programs/nyc-lead-paint/documentRequirements.ts). No row = the rulebook. Read with view_projects, view_documents or manage_settings; written with manage_settings | scope_kind, scope_key (* for all), doc_code, requirement (required/optional/removed), reuse_policy (scope all only; the five rulebook policies plus on_file), updated_by; unique on (tenant_id, scope_kind, scope_key, doc_code) |
document_orders | tenant-owned | Which HPD order number a project document answers, and which violation (event_id), since a project can have several violations with one order number. event_id is null for an order-only link: older links where the project had several violations with that order, and writers that don't pass one yet. Unique per document, order and violation. Insert and delete only | document_id, order_number, event_id, 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 |
signature_requests | tenant-owned, portal-readable | A document sent to a client's portal logins to e-sign, and the signature (client e-signature). One pending per document. Written only by request_document_signature, cancel_signature_request, record_document_signature (service role) and a documents trigger; read by staff who can read the document and by the client's portal unless cancelled | document_id, client_id, project_id, recipient_ids (client_users ids), status (pending/signed/cancelled), signer_user_id, signer_name, signer_email, signed_at, signer_ip, document_sha256, signed_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, document_sent_to_client_for_signature.
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 | Prices per service type: the firm's default (client_id null) or one client's own card, which overrides the default for that client's projects | service_type, base_price, unit_price, unit_label, is_default, client_id |
abatement_rates | tenant-owned | Abatement price list: one flat price per component, substrate and method | component, substrate, method, price |
abatement_component_prices | tenant-owned | One scoped component's price for its job (Scope tab); wins over the price list on the job's proposal and invoice. Separate from abatement_components because that table is collaborator-shared and financials are not (D4.3) | abatement_component_id (unique), price |
abatement_material_prices | tenant-owned | One material's unit price on its job (Materials tab), shown × quantity. Separate from abatement_materials for the same reason | abatement_material_id (unique), unit_price |
proposals | tenant-owned | A priced proposal header | project_id, status (draft/sent/accepted/rejected/expired), subtotal, total, valid_until, document_id, service_group (inspection / abatement / all for one saved before the split: one proposal per firm) |
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, due_date (sent date in New York + 30 when not given, editable), paid_at, document_id, billed_client_id |
invoice_line_items | tenant-owned | Invoice lines | invoice_id, service_type, quantity, unit_price, amount |
invoice_payments | tenant-owned | Money received against a sent invoice, partial or full. Never visible to the portal | invoice_id, amount, received_on (date), method (check/ach/card/cash/other), reference, note, created_by |
invoice_collection_notes | tenant-owned | The collections log on an unpaid invoice. Append-only; never visible to the portal | invoice_id, note, contact_method, contact_name, next_follow_up, created_by |
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.
invoices_default_due_date fills due_date when an invoice is sent without one.
invoice_payments_settle_invoice marks a sent invoice paid once its payments reach the total
(paid_at = the last payment's date); removing a payment never reopens it.
invoice_payments_require_sent_invoice refuses a payment on a draft.
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_*; from the same notice's reply, window_guards_required, window_guards_installed_date, stove_knob_covers_offered_date, stove_knob_covers_requested, stove_knob_covers_installed_date (all nullable: null = not recorded) |
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, friction_surfaces_remediated (nullable), friction_surfaces_remediated_date |
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, surface_method (Lead-Safe only: encapsulated / contained / both; picks the monitoring ladder, null keeps the encapsulated one) |
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; p_unit_id narrows it to one apartment and p_building_id to one building, which is what the unit and building pages ask for) 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, portal_sidebar (light/black: the client portal's sidebar, by logo), is_active |
client_branding | tenant-owned | Portal chrome; also the letterhead of the client's reports when white_label_reports is on | client_id, display_name_override, logo_storage_path, footer_text, white_label_reports |
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 by the notify function and, for the client portal, by notify_in_app() triggers on work orders, building requests, visits, shared documents and sent invoices (payload.link is where the bell sends you); 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.
The portal's staff notices go through enqueue_portal_staff_email(), which skips them while
tenants.portal_staff_emails_enabled is off (the default; Settings → Client portal).
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 | No longer read: the reports load their fonts from Google Fonts | Flat | Public read |
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 |
field_job_signal_counts, xrf_reading_counts | Per-visit counts as one jsonb value, under the caller's RLS |
project_scope(public_events), project_plan_inputs(ids) | A violation's scope (apartment or building); a project's plan inputs, one row per project |
create_projects_from_violations | One project per scope from ticked violations, in one transaction |
document_list, sync_health_summary, unlinked_events_by_reason (views) | Read models |