Skip to content

SQL & Database Architectural Contracts

This document defines the canonical database design standards, PostgREST API conventions, and architectural boundaries between PostgreSQL and FastAPI for vibrant-platform.

Reviewers (both human engineers and automated agents) must evaluate all SQL migrations and RPC definitions against these rules.

1. Boundary of Responsibilities

What Belongs in PostgreSQL

  • ACID Transaction Boundaries & Invariants: State machine status transitions (e.g. invoicing.transition_receipt_status_for_worker), row locks (FOR UPDATE SKIP LOCKED), and relational consistency.
  • Relational Integrity: Foreign keys, unique indexes, nullability, table CHECK constraints.
  • Row-Level Security (RLS) & Access Control: Declarative security-invoker views in api and internal_api, policy helpers in access.* and invoicing.*.
  • Set-Based Calculations: Derived status checks and aggregate summations (e.g. bill_line_items_total).

What Belongs in FastAPI

  • Input Validation & Sanitization: Pydantic models for payload structure, regexes, and finite number bounds.
  • Payload Ingestion & DTO Parsing: Unpacking multi-line external documents (e.g. OCR/AI extraction output) into parameterized database queries.
  • External System Orchestration: Calling third-party APIs (OpenAI, Plaid, Connecteam, SendGrid), signing object-storage (S3/MinIO) URLs, and rendering/combining PDFs.
  • Composite Detail Aggregation: Querying multiple tables and assembling client DTOs (e.g. BillReviewRepository.load_bill).

Reads and Writes

PostgREST is the read path: exposed security-invoker views are queried directly and carry RLS. Move a write to FastAPI only when it needs real payload parsing or validation — simple inserts stay as RPCs. A relocated write keeps its authorization in a private SQL function so both entry paths enforce the same rules and cannot drift.

Scheduled jobs and background workers do not go through PostgREST at all: they call private *_for_worker functions over asyncpg with the service's constrained role. See docs/architecture/background-jobs.md for the decision rule and the *_for_worker function contract.

2. RPC Function Signatures

Prohibited: Ad-Hoc RETURNS jsonb / jsonb_build_object

Do not wrap standard CRUD or mutation results in arbitrary JSON objects (RETURNS jsonb with jsonb_build_object('changed', true, 'id', ...)).

Reason: PostgREST cannot reflect field types or nullability for jsonb return types in the generated OpenAPI schema, and client SDK generators lose TypeScript typings. Furthermore, clients cannot use PostgREST column projection (?select=...) or filtering on JSON return values.

Required: Strongly-Typed Returns

Choose the appropriate return type based on the operation:

  1. Entity Table Rows (Preferred for PostgREST CRUD/Mutations): sql CREATE FUNCTION internal_api.add_bill_line_item(...) RETURNS TABLE ( id uuid, bill_id uuid, position integer, description text, amount numeric, is_tax boolean ) Or return existing domain view/table records: RETURNS SETOF internal_api.bills or RETURNS api.user_devices.

  2. Simple Identifiers / Booleans: sql CREATE FUNCTION internal_api.create_vendor(p_name text) RETURNS uuid CREATE FUNCTION invoicing.bill_amounts_reconcile(p_bill_id uuid) RETURNS boolean

  3. Approved RETURNS jsonb Exceptions: RETURNS jsonb is permitted only for:

  4. auth.jwt(): Low-level access to full request JWT claims.
  5. Polymorphic Audit Snapshots: details jsonb fields in event tables (invoicing.receipt_events, invoicing.bill_events).
  6. Composite Inspection Checklists: Non-relational readiness checklists (e.g. invoicing.bill_readiness, internal_api.receipt_readiness).

Every function exception must be documented directly above its definition:

sql -- allow-jsonb-return: inspection - The keys form a heterogeneous readiness checklist. CREATE FUNCTION invoicing.bill_readiness(p_bill_id uuid) RETURNS jsonb

The accepted categories are inspection and polymorphic. Historical status alone is not an acceptable exception.

Prohibited: jsonb Payload Parameters

Exposed api / internal_api functions must not accept a payload as jsonb. Declare a composite type and take an array of it:

CREATE TYPE invoicing.bill_line_item_input AS (position integer, description text, amount numeric);

CREATE FUNCTION invoicing.replace_bill_line_items(
  p_bill_id uuid,
  p_line_items invoicing.bill_line_item_input[]
)

Reason: the same OpenAPI and projection losses as RETURNS jsonb, and the parser ends up in PL/pgSQL. A composite array is rejected at the wire on wrong arity instead of shifting columns, and asyncpg encodes list[tuple] with no codec registration.

Parameters named p_details or p_result are exempt by name — they are the polymorphic audit snapshots above. Anything else needs a signature-keyed entry in JSONB_PARAMETER_ALLOWLIST with a reason. Because the key is the full signature, a retype retires its own exemption: the stranded entry is then reported as a violation.

3. PL/pgSQL #variable_conflict use_column Invariant

When a PL/pgSQL function returns RETURNS TABLE(...), PostgreSQL creates output variables in the function scope for each returned column name. Any unadorned SQL identifier in INSERT ... ON CONFLICT, UPDATE ... SET, or WHERE clauses will conflict with the return column variable.

Mandatory Rule: Every PL/pgSQL function returning RETURNS TABLE(...) must declare #variable_conflict use_column directly below AS $$:

CREATE FUNCTION internal_api.create_project_budget(
  p_project_id uuid
) RETURNS TABLE (
  id uuid,
  project_id uuid
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$
#variable_conflict use_column
DECLARE
  v_actor_user_id uuid := auth.uid();
BEGIN
  ...
  RETURN QUERY
  INSERT INTO invoicing.project_budgets (project_id, created_by_user_id)
  VALUES (p_project_id, v_actor_user_id)
  ON CONFLICT (project_id) DO NOTHING
  RETURNING id, project_id;
END;
$$;

4. Security, Grants & Privilege Revocation

Explicit REVOKE ... FROM PUBLIC

PostgreSQL grants function EXECUTE privileges to PUBLIC by default. Creating or replacing a function without revoking execution from PUBLIC inadvertently exposes internal RPCs to anonymous callers.

Mandatory Rule: Every CREATE FUNCTION or CREATE OR REPLACE FUNCTION in api, internal_api, or domain schemas must include an explicit full-signature revocation after the definition and before granting access to named roles:

REVOKE EXECUTE ON FUNCTION internal_api.my_function(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION internal_api.my_function(uuid) TO internal_api_role;

Role Targets

  • authenticated: Signed-in public application users (used in public api schema).
  • internal_api_role: Internal operations, staff, and private invoicing workflows (used in internal_api schema).
  • service_role: Trusted service automation.
  • internal_anon / anon: No grants by default (fail closed).

Security Invoker vs. Security Definer

  • Views: Always specify WITH (security_invoker = true).
  • Functions: Default to SECURITY INVOKER.
  • SECURITY DEFINER: Use only when crossing schema boundaries or evaluating RLS helpers. When used, you must specify SET search_path = pg_catalog to eliminate search-path hijacking.

5. PostgREST OpenAPI & Documentation

  1. SQL Comments: Every view and function exposed in api or internal_api must include a descriptive comment. PostgREST extracts these comments as the endpoint summary and description in the OpenAPI specification: sql COMMENT ON FUNCTION internal_api.claim_receipt_extraction_jobs(integer, integer, text) IS 'Claims pending receipt extraction jobs with strongly-typed tabular rows for internal services.';

  2. OpenAPI Allowlist Verification: Every new or renamed RPC/view must be deliberately listed in the allowlist in scripts/test-db-local.sh.

6. Migration Standards

  1. Format: Every migration file under infrastructure/db/migrations/ must contain balanced -- migrate:up and -- migrate:down sections.
  2. Deterministic Replay: scripts/test-db-local.sh rolls the complete migration chain back to zero and replays it before running integration tests.
  3. schema.sql Synchronization: After running migrations, regenerate and commit infrastructure/db/schema.sql.
  4. Explicit Definition Changes: Declare complete target definitions with CREATE OR REPLACE FUNCTION or CREATE OR REPLACE VIEW in both migration directions instead of using pg_get_*def or dynamic EXECUTE. Repair deployed definitions in a new forward migration.
  5. Both Directions: Objects recreated in down migrations must satisfy the same return-type, privilege, comment, view-security, and search-path rules as up migrations.
  6. Static Verification: Run python3 scripts/check-sql-contracts.py --all before database replay.
  7. Enforcement Scope: check-sql-contracts.py reads migration source text (fix violations in place). check-schema-health.py validates the resolved schema.sql (fix via forward migrations).
  8. Unique Versions: Migration filenames must have unique numeric version prefixes to avoid collisions in schema_migrations.

7. Schema Health Rules

scripts/check-schema-health.py validates the resolved infrastructure/db/schema.sql during test-db-local.sh.

  1. Foreign key indexes: Every FK column requires an index leading with that column usable by referential-integrity scans (WHERE fk_col = $1). WHERE (deleted_at IS NULL) partial indexes do not qualify.
  2. Exemption: Parent tables with an unconditional row-level BEFORE DELETE guard trigger (aborts deletion before RI checks run).
  3. Single evaluation: No duplicate call expressions within a single statement.
  4. Explicit COST: Required on every function reachable from an RLS policy or exposed view (never COST 100, which pg_dump drops as language default).
  5. Monetary columns: Must be numeric(18,4) (enumerated in MONETARY_COLUMNS).
  6. No blocking FOR UPDATE: Disallowed in function bodies that use SKIP LOCKED to prevent worker serialization.
  7. No jsonb payload parameters: Exposed functions must take composite types or scalars (see §2). p_details/p_result are exempt by name; anything else needs a signature-keyed JSONB_PARAMETER_ALLOWLIST entry, which is itself reported once its signature no longer matches.
  8. Scope: exposed schemas only. Private helpers may take jsonb, since the rule is about the HTTP surface.

Notes & Pitfalls

  • COST is checked for presence, not accuracy: The rule cannot distinguish a good estimate from a bad one. Committed values were chosen by relative ordering; re-measure at production volume.
  • COST 100 is omitted by pg_dump: Use a non-default value so it persists in schema.sql.
  • ALTER FUNCTION ... COST resets: Replaced functions without a COST clause revert to default.
  • LATERAL deduplication: Requires an OFFSET 0 optimization fence to prevent subquery pull-up and re-evaluation.
  • Non-inlinable helpers: SECURITY DEFINER and SET search_path = pg_catalog prevent inlining. Prefer LANGUAGE plpgsql over LANGUAGE sql for non-inlinable helpers to avoid SQL executor overhead.

8. Reviewed, Not Linted

  1. Row-level UPDATE trigger without UPDATE OF: Often valid (append-only guards, immutability guards, set_updated_at, NOTIFY). Review per case.
  2. Trigger-maintained column without readers: Requires full query analysis to detect; audited manually.
  3. Derived-status coupling: receipts.workflow_status, bills.status, and projects.is_active/deleted_at status propagation:
  4. Trigger UPDATE OF lists must cover all columns read by their status derivation functions.
  5. Policy helpers (can_read_receipt, can_read_bill) rely on <> 'needs_attention' matching trigger-maintained column dependencies. Verified by tests.