Skip to content

Conventions

This page covers cross-cutting conventions that apply to every domain in the schema. Domain-specific patterns live in each domain file.

Base Columns

Almost every table follows the same audit columns:

  • id UUID PRIMARY KEY DEFAULT gen_random_uuid()
  • created_at TIMESTAMPTZ NOT NULL DEFAULT now()
  • updated_at TIMESTAMPTZ NOT NULL DEFAULT now() — kept current by the update_updated_at_column() trigger function attached to per-table BEFORE UPDATE triggers
  • created_by_user_id UUID DEFAULT auth.uid() — FK to users(id)
  • updated_by_user_id UUID DEFAULT auth.uid() — FK to users(id)

Pure junction tables typically omit updated_at / updated_by_user_id (they are created/deleted, never updated).

Soft Delete

User records are never hard-deleted. On auth.users delete:

  • Invited placeholder users (is_invite_pending = true) are hard-deleted along with their conversations, Personal entity, user_settings, and Personal organisation via delete_invited_user.
  • Accepted users are soft-deleted by setting auth_deleted = true and suffixing email / phone with a timestamp so the unique indexes are free for re-use.
  • Person records are preserved for historical data.

The same soft-delete approach is used for production_days.is_removed and the is_active = false patterns on most reference tables.

Row Level Security

  • All tables containing user data have RLS enabled.
  • All RLS policies explicitly target TO authenticated — the anon role has zero grants and zero policies on public tables.
  • Anon-facing features (phone existence check, notification token validation) use createServiceRoleClient() server-side, bypassing RLS entirely.
  • Service role bypasses RLS — no service_role-specific policies are needed.

RLS policy design by table category

  • User data tables (projects, transactions, budgets, etc.): full CRUD policies scoped to authenticated users via project membership or ownership checks.
  • Reference / config data tables (SELECT-only for authenticated, writes via service_role): tax_rates, tax_schemes, departments, countries, currencies, languages, role_definitions, role_definition_categories, permission_roles, permission_role_links, permissions, menu_items, menu_item_categories, categories, category_groups, notification_template_categories, rate_limit_configs, integration_feature_dependencies.
  • addresses table: SELECT + INSERT + UPDATE only — DELETE intentionally omitted to preserve address history for audit trails and linked records (addresses may be referenced by entities, transactions, etc.).

Tables classified as reference or configuration data are pre-seeded via migrations and managed exclusively through service_role operations. The absence of write policies for authenticated users is intentional — it prevents client-side mutation of system reference data. Service role bypasses RLS entirely, so no additional policies are needed for administrative writes.

Explicit grants

Every new public.<table> must receive explicit GRANT statements. The Supabase auto-grant default for the public schema is being removed, and tables without explicit grants will return PostgREST 42501 regardless of RLS.

Common Patterns

Denormalised project_id for RLS fast paths

Several child tables (budget_headers, budget_items, budget_item_daily_allocations, budget_allocation_transaction_items) carry a denormalised project_id so the SELECT policy can gate directly on the row instead of walking back to the parent (budgets, etc.). Triggers populate project_id on INSERT from the parent. Write paths still walk the chain — they are low volume and the cost is acceptable.

Preferred SELECT-policy pattern for permission scoping: the bounded set-based form.

AND project_id = ANY (SELECT unnest(public.caller_accessible_project_ids(ARRAY['budget:view'], false)))

caller_accessible_project_ids(text[], boolean) depends only on auth.uid() and constant args, so this uncorrelated subquery is evaluated independently of row count (InitPlan / hashed subplan) in every plan shape — precisely, once per executor process; the function is PARALLEL SAFE, so parallel workers may each build the subplan, bounding evaluation by worker count rather than a global once-per-statement. The earlier per-row caller_has_permission(project_id) form is bounded only when the planner happens to pick an index-condition plan; in a scan/filter plan it re-executes the whole permission walk per candidate row (measured: hundreds of ms and >100k buffers vs low-single-digit ms and low-hundreds of buffers on the five budget/production SELECT policies plus the storage.objects files policy migrated in DEV-779). STABLE alone does not guarantee row-count-independent evaluation — only the uncorrelated-subquery form does. Keep caller_has_permission(...) for single-record checks and WITH CHECK clauses. Full rationale, the invalid = ANY ((SELECT fn(...))) shape to avoid, and the evidence-capture workflow are in .claude/rules/rls-policies.md § "Bounded Access-Primitive Evaluation".

Computed totals via views

Most aggregations (balance, total, actual_total, estimated_cost_total) are not stored on the base tables. They are computed on read by a v_<table> view that joins to the relevant aggregate. See Views & Functions for the full list. Voided / rejected transactions are filtered out of every actuals path so drill-downs reconcile with v_budget_items.actual_total.

Junction-table RLS

Many-to-many junction tables (permission_role_links, project_categories, role_definition_categories, menu_item_categories, message_attachments, etc.) typically use USING (true) for authenticated SELECT and rely on parent-table RLS to gate access to the actual referenced rows.

Polymorphic ownership

Some tables (attachments, tags, taggings, sensitive_rules) use a parent_entity_type + parent_entity_id pair to reference any source row. Queries that need to be polymorphic over record_type should look up the masking view via TABLE_TO_VIEW_MAPPING.

Advisor Warnings

Supabase advisor warnings reviewed and accepted as intentional are listed in SUPABASE_ADVISOR_ACCEPTED_WARNINGS.md.