Database schema

All data lives in Supabase PostgreSQL across two schemas. The migrations are in supabase/migrations/.

No cross-schema SQL joins, ever. Assemble cross-schema data in application code with two queries.

`app` schema (application & config)

organisation, profile, system_admin, organisation_member, invitation, map, metric, saved_view, import_session, edit_session, atomic_change, revision.

Users vs profiles. User identities are Supabase-managed auth.users. app.profile holds display data, separated from the identity.
Maps are org-owned (organisation_id), never user-owned.
Fields replace the old Capability entity. A capability is a field with category = 'capability'. System-defined fields are global (org_id null, is_system_defined = true); org fields have an org_id. value_type is restricted to 'numeric' | 'boolean' — a free-text observation belongs in a node’s or connection’s properties, not a Field.
A boolean field is true-only. An explicit false is never more informative than the field being absent, so node_metric/connection never store one — application code (manual editing UI, CSV/GraphML import) enforces this by only ever writing the string 'true' for a boolean field and skipping/ignoring anything that would resolve to false, rather than the DB rejecting false outright.
metric.subject_type ('node' | 'connection') discriminates whether a field describes a node or a connection between two nodes — added for the typed-connections model (no more freeform connection strength/type; see below). Defaults to 'node'. No connection fields ship by default; orgs define their own via the map editor’s Fields panel.
metric.directionality ('undirected' | 'directed' | 'mutual', default 'undirected') binds direction to the field definition rather than to each connection instance — every connection observing a given field reads the same way, matching how Neo4j binds direction to relationship type and OWL binds symmetry to the predicate. Only meaningful when subject_type = 'connection'; metric_directionality_scope enforces this (a node field can’t be anything but 'undirected' with a null inverse_name). metric.inverse_name is an optional reverse reading for a 'directed' field (e.g. name funds, inverse_name funded by); metric_inverse_name_scope restricts it to directionality = 'directed'.
Revisions are session-collapse snapshots (snapshot jsonb).
import_session backs the map editor’s Import/Export panel (COLLAB-5508): filename, storage_path (into the map-imports Storage bucket), format ('csv' | 'graphml'), status (app.import_status: scanning → previewing/invalid → mapping (CSV) → ingesting → ingested/ failed), column_mapping jsonb (CSV column → role/value-type), scan jsonb (headers/sample rows/suggested mapping, or GraphML node/edge counts), result jsonb (node/connection counts, rows skipped, unmatched field keys), and error text (set when status = 'failed'/'invalid'). Ingestion itself runs as a detached async task on the app server — see API surface.
system_admin is global (not org-scoped), so it has its own table. The first system admin is bootstrapped via the service role.
Organisations have an immutable slug used in /org/$slug URLs instead of the raw id. A before insert trigger derives it from name (with a numeric suffix on collision) when not supplied; renaming an org never changes its slug, so existing links keep working.

`network` schema (graph)

node, map_node, connection, node_metric.

Nodes are many-to-many with maps via map_node. Position (pos_x/pos_y) is per-map and lives on map_node; properties/fields are global to the node.
A map_node row is per-map MEMBERSHIP, not necessarily placement (COLLAB-5512). pos_x/pos_y are nullable: a null position means the node belongs to this map but hasn’t been placed on the canvas yet (“unplaced”); a non-null position means it’s “placed”. Imported nodes (GraphML/internal JSON) are inserted unplaced by default — see API surface — and getGraph returns only placed nodes (and only edges between two placed nodes), so the map editor’s canvas never sees an unplaced node until a user places it (placeNodes) or “Place all” does. Unplacing (unplaceNodes) just nulls the position back out; it never deletes the node or its connections.
Connections belong to one map. A node cannot connect to itself (enforced by a check constraint).
A connection is a typed observation about a pair of nodes, not a single scalar. strength/type are gone. Instead a connection has a nullable metric_id (a plain uuid, no FK — same pattern as node_metric.metric_id) and a value (text, coerced by application code against the field’s value_type), plus a freeform properties jsonb bag. reason is a reserved properties key the inspector always offers a box for (never a Field, never a metric_id) — see RESERVED_CONNECTION_PROPERTY_KEYS (apps/app/src/types/connection.ts). There is no uniqueness constraint on (map_id, source_node_id, target_node_id), so multiple connections — each against a different field, or reason-only — may exist between the same pair; a composite index on those three columns supports pair lookups.
Connection “definedness” is a computed predicate, not a DB constraint. A connection counts as defined (solid line, enters visualisation) if it has a non-empty properties bag (e.g. a reason) or a complete metric_id+value pair — see isConnectionDefined (apps/app/src/graph/definedness.ts), the single source of truth for this rule. A row with neither is still storable (a placeholder), just flagged “needs defining” in the UI and excluded from the visualisation graph and weight function.
connection has no direction column. A connection’s direction is derived from its assigned field’s directionality at read time (see getGraph in API surface) — a connection with no field assigned (including a reason-only one) always reads 'undirected'. source_node_id/target_node_id still fix the drawn order, which is what a 'directed' field’s arrow points along.
Cross-schema references are plain uuid columns with no foreign keys into app (e.g. map_node.map_id, node_metric.metric_id). This preserves the clean cut so network can move to its own database later.
org_id is stamped on every network row. This lets RLS check org membership without touching app, and matches the rule that service-role writes must stamp org_id on every row.

Manual-editing SQL functions (Locality 01)

Two security invoker functions in network (20260713123246_manual_editing_functions.sql) wrap the transactional parts of node editing so a partial write can’t happen:

network.create_node_in_map(p_map_id, p_org_id, p_label, p_pos_x, p_pos_y, p_properties, p_node_id default null) — inserts a node then a map_node in one statement, both stamped with p_org_id. Always creates a new node (manual “add node” = new entity; label deduplication is an import-time, Locality 02 concern). Returns (node_id, map_node_id). p_node_id defaults to null and the insert does coalesce(p_node_id, gen_random_uuid()) — the client mints its own id for optimistic inserts (see Client state); added in 20260720120500_client_supplied_ids.sql, which drops and recreates the function to add this trailing parameter (create or replace can’t widen an existing signature’s arity without leaving the old one as a stale overload).
network.delete_node_from_map(p_map_id, p_node_id) — deletes this map’s connections touching the node, then this map’s map_node row, then — only if the node has no remaining map_node row in any map (the orphan rule) — the node itself (its FK cascades node_metric and any residual connections). Returns whether the node itself was deleted.
network.delete_map(p_map_id, p_org_id) — deletes this map’s network footprint: its connection/map_node rows, then any node left with no map_node row in any map (same orphan rule as above, scoped to the map’s org). deleteMap calls this before deleting the app.map row, because no cascade can do it: map_node.map_id/connection.map_id are plain uuids with no FK into app.map (see “Cross-schema references” above), so deleting app.map alone used to orphan the entire graph. This was the primary orphan-data bug — see the maintenance section below for the one-time/ongoing cleanup of data orphaned before this fix existed.

security invoker means RLS applies using the caller’s JWT inside the function body, same as a direct table write — the handler’s assertMapAccess is defense-in-depth on top of this, not a replacement for it.

app.edit_session/app.atomic_change needed no schema change — they’re now actively written by change capture (see API surface).

Maintenance: orphan purge & the org soft-delete lifecycle

deleteOrg (API surface) only sets app.organisation.deleted_at — org members, maps, and the org’s entire network graph are not cascade-deleted at that point; they simply become unreachable once every read path filters deleted_at IS NULL. Full purge happens later, via the maintenance sweep below, once the org has been soft-deleted for longer than a retention window (currently 30 days, see ORG_PURGE_RETENTION_MS in apps/app/src/server/lib/maintenance.ts) — this keeps org deletion recoverable without leaving the data around forever.

Two more security invoker network functions (20260720120100_network_purge_functions.sql), granted to service_role only, back the sweep:

network.purge_dead_maps(p_live_map_ids) — deletes map_node/ connection rows whose map_id isn’t in the passed live-map-id set (from app.map, fetched in app code — network has no FK to check against directly), then sweeps any now-orphaned node. Returns row counts for logging.
network.purge_org(p_org_id) — deletes every network.node for the org; its FK cascades (map_node.node_id, connection.source_node_id/ target_node_id, node_metric.node_id all ON DELETE CASCADE) remove everything else in one statement.

purgeOrphanedData (apps/app/src/server/lib/maintenance.ts) runs both, plus Storage cleanup for purged orgs’ map-imports files and a reaper for import_session rows stuck in ingesting past a timeout (no such reaper existed before). It’s exposed at POST /api/maintenance/purge (apps/app/src/routes/api/maintenance/purge.ts), a Server Route guarded by a shared-secret header (MAINTENANCE_PURGE_SECRET) rather than a user session — see environment variables — invoked daily by the network-mapping-purge Render cron job (render.yaml).

Row-Level Security

RLS is enabled on all 16 tables. Recursion is avoided with SECURITY DEFINER helpers in app: user_is_system_admin(), user_is_org_member(org_id), user_has_org_role(org_id, roles[]), and user_can_access_map(map_id) (an app-only join). Policy columns (org_id, map_id, user_id) are indexed, and auth.uid() is wrapped as (select auth.uid()) for per-statement caching. The service role bypasses RLS and must stamp tenant columns itself.

The four network table policies (node_rw, map_node_rw, connection_rw, node_metric_rw) are the one exception to the SECURITY DEFINER-helper pattern: they inline the membership check as org_id in (select organisation_id from app.organisation_member where user_id = (select auth.uid())) instead of calling user_is_org_member(org_id) (20260720120400_network_rls_membership_set.sql). Postgres never inlines SECURITY DEFINER functions, so the function form ran once per row on any multi-row network scan — on node_metric in particular (typically the largest table, one row per node per field) this dominated read cost as data grew. The inlined subquery form is semantically identical (literal membership only, same as the helper) but evaluates once per statement instead of once per row. If this ever needs reverting, wrap the original predicate as (select app.user_is_org_member(org_id)) instead — still per-statement, but keeps the helper.

Performance indexes beyond the FK/filter defaults

Two indexes address hot paths that were missing coverage (20260720120200_perf_indexes.sql):

network.connection (metric_id) — metric_id was added as a plain column (20260714065823_connection_typed_metric.sql, no cross-schema FK to index off of) but is filtered directly in getFieldUsage and deleteField.
app.atomic_change (session_id, stack_index) — captureChange does .eq(session_id).order(stack_index desc).limit(1) on every edit; only session_id was indexed, so the top-of-stack lookup had to sort as a session’s change history grew.

app.get_user_emails(p_user_ids) (20260720120300_batch_user_emails.sql) is a security definer batch lookup against auth.users, granted to service_role only. It replaces a per-member auth.admin.getUserById REST call in getMembers/isEmailAlreadyMember (an N+1 that scaled with org size) — the GoTrue Admin API has no bulk get-users-by-id endpoint, so this does the batch lookup in SQL instead.

Storage

One private bucket, map-imports (20260718072042_map_imports_storage_bucket.sql), holds import source files. No storage.objects RLS policies are defined: uploads authorize via a short-lived signed upload token issued by the service role (never a Supabase Auth session), and every server-side read uses the service-role client, which bypasses RLS regardless. See Data upload & async ingestion.

Exposing a custom schema is two steps, not one

RLS governs row-level access, but PostgREST also needs the coarser SQL-standard grant to let a role touch a schema’s objects at all — RLS alone is not sufficient. 20260706170000_grant_app_network_schema_privileges.sql grants USAGE + ALL on app/network (tables, routines, sequences, and default privileges for future objects) to anon, authenticated, and service_role. Without it every query 42501s regardless of RLS — service_role needs the grants too, since BYPASSRLS skips policy checks, not the GRANT system.

This still isn’t the whole story for a new Supabase project: PostgREST’s “exposed schemas” list (public, graphql_public by default) is a project-level setting, not something a migration can reach. A fresh project needs app/network added to it, plus the Auth redirect allow-list, before any of this schema is queryable — scripts/supa-configure-project.sh does both, and runs automatically from scripts/supa-first-run.sh. See Branching & deployment for what it covers and what it can’t (email template customisation, which needs a plan/SMTP decision made by hand).