Data model

All data is in one Supabase Postgres database, in two schemas:

• app holds organisations, people, maps and app state.
• network holds the graph: nodes, connections, their values, and each map’s catalogue of measures, tags, tag groups and connection types.

Migrations are in supabase/migrations/. Functions, triggers and indexes are listed in Database functions and indexes.

Diagram

Solid lines are foreign keys. Dotted lines are map_id links from network to app.map, which are plain uuid columns with no foreign key. Columns pointing at auth.users (such as created_by, invited_by and updated_by) are left out.

erDiagram
    auth_users {
        uuid id PK
    }

    app_organisation {
        uuid id PK
        text slug
        text name
        timestamptz deleted_at
    }
    app_profile {
        uuid id PK
        uuid user_id FK
        text display_name
    }
    app_system_admin {
        uuid user_id PK
    }
    app_organisation_member {
        uuid id PK
        uuid organisation_id FK
        uuid user_id FK
        org_role role
    }
    app_invitation {
        uuid id PK
        uuid organisation_id FK
        text email
        invitation_status status
    }
    app_map {
        uuid id PK
        uuid organisation_id FK
        text name
        map_visibility visibility
        uuid default_group_by "no FK -> network.tag_group; nullable"
    }
    app_saved_view {
        uuid id PK
        uuid map_id FK
        integer step "nullable: place in the reveal"
        timestamptz updated_at
    }
    app_map_recipe {
        uuid map_id PK, FK
        text body_md
        timestamptz updated_at
        timestamptz validated_through
    }
    app_import_session {
        uuid id PK
        uuid map_id FK "nullable: null = new map on ingest"
        import_status status
    }
    app_edit_session {
        uuid id PK
        uuid map_id FK
        uuid user_id FK
        edit_session_status status
    }
    app_atomic_change {
        uuid id PK
        uuid session_id FK
        integer stack_index
    }
    app_revision {
        uuid id PK
        uuid map_id FK
        uuid session_id FK "nullable"
    }
    app_api_key {
        uuid id PK
        uuid org_id FK "with user_id -> organisation_member"
        uuid user_id FK
        text key_prefix
        text key_hash "sha-256, unique"
        timestamptz last_used_at
    }

    network_node {
        uuid id PK
        uuid map_id "no FK -> app.map; unique (map_id, id)"
        text label
        float pos_x "nullable = unplaced"
        float pos_y "nullable = unplaced"
        text external_key "nullable; unique (map_id, external_key)"
    }
    network_connection {
        uuid id PK
        uuid map_id "no FK -> app.map"
        uuid source_node_id FK "with map_id -> node (map_id, id)"
        uuid target_node_id FK "with map_id -> node (map_id, id)"
        uuid metric_id FK "with map_id -> connection_type (map_id, id), nullable"
        text value
        date occurred_on "nullable; + _precision"
        date valid_from "nullable; + _precision"
        date valid_to "nullable; + _precision"
    }
    network_node_measure {
        uuid id PK
        uuid map_id "copy of node's; FK with node_id"
        uuid node_id FK "with map_id -> node (map_id, id)"
        uuid measure_id FK "with map_id -> measure (map_id, id)"
        numeric value
    }
    network_node_tag {
        uuid id PK
        uuid map_id "copy of node's; FK with node_id"
        uuid node_id FK "with map_id -> node (map_id, id)"
        uuid tag_id FK "with map_id -> tag (map_id, id)"
        uuid tag_group_id FK "with map_id -> tag_group (map_id, id), nullable = ungrouped"
    }
    network_measure {
        uuid id PK
        uuid map_id "no FK -> app.map; unique (map_id, id)"
        text category
    }
    network_tag {
        uuid id PK
        uuid map_id "no FK -> app.map; unique (map_id, id)"
        uuid tag_group_id FK "with map_id -> tag_group (map_id, id), nullable = ungrouped"
        text icon "nullable; lib/tag-style.ts TAG_ICONS or 'none'"
        text colour "nullable; lib/tag-style.ts TAG_COLOURS"
    }
    network_tag_group {
        uuid id PK
        uuid map_id "no FK -> app.map; unique (map_id, id)"
        text name
    }
    network_connection_type {
        uuid id PK
        uuid map_id "no FK -> app.map; unique (map_id, id)"
        metric_value_type value_type
        metric_directionality directionality
        text inverse_name
        text value_label "numeric types only"
        text value_unit "numeric types only"
    }

    auth_users ||--|| app_profile : has
    auth_users ||--o{ app_organisation_member : "belongs to orgs"
    auth_users ||--o| app_system_admin : "may be"
    auth_users ||--o{ app_edit_session : "edits as"

    app_organisation ||--o{ app_organisation_member : has
    app_organisation ||--o{ app_invitation : has
    app_organisation ||--o{ app_map : owns
    app_organisation_member ||--o{ app_api_key : "acts through"

    app_map ||--o{ app_saved_view : has
    app_map ||--o| app_map_recipe : "described by"
    app_map |o--o{ app_import_session : "imports into"
    app_map ||--o{ app_edit_session : has
    app_map ||--o{ app_revision : snapshots
    app_edit_session ||--o{ app_atomic_change : records
    app_edit_session |o--o{ app_revision : "collapsed from"

    network_node ||--o{ network_node_measure : "has measures"
    network_node ||--o{ network_node_tag : "has tags"
    network_node ||--o{ network_connection : source
    network_node ||--o{ network_connection : target

    app_map ||..o{ network_node : "owns"
    app_map ||..o{ network_connection : "scoped to"
    app_map ||..o{ network_measure : "defines"
    app_map ||..o{ network_tag : "defines"
    app_map ||..o{ network_tag_group : "defines"
    app_map ||..o{ network_connection_type : "defines"
    network_tag_group |o--o{ network_tag : "groups (exclusive)"
    network_measure ||--o{ network_node_measure : "measured by"
    network_tag ||--o{ network_node_tag : "tagged by"
    network_connection_type |o--o{ network_connection : "typed by"

Tenancy

• An organisation owns maps, members, invitations and API keys.
• A map belongs to one organisation (app.map.organisation_id). That’s the only link from graph data to an organisation.
• Everything in network belongs to a map. Every table has a NOT NULL map_id and no org_id.
• No foreign keys cross from network into app, so network can move to its own database. Deleting a map therefore can’t cascade. deleteMap calls network.delete_map before deleting the app.map row.
• No SQL joins across schemas either. See the hard rules.

app schema

Table	Holds
organisation	Name, an immutable slug used in /org/$slug URLs, deleted_at
profile	Display name and settings jsonb (e.g. activeOrgId), one per user
system_admin	Users with deployment-wide admin rights
organisation_member	Membership, with role
invitation	Email invitations, with status and expiry
map	Name, visibility, owning organisation, and default_group_by: the tag group the editor groups nodes by for everyone (a display setting, see Editing internals)
saved_view	A Visualise view: viewport_state and visualisation_config jsonb, step (its place in the map’s progressive reveal, or null), and updated_at, which the app stamps on every write
map_recipe	A map’s recipe: body_md markdown, who edited it last, and when it was last confirmed. One row per map, deleted with it.
import_session	One editor or API import: files, status, column_mapping, scan, result, error
edit_session, atomic_change	Change capture: sessions of changes, one row per change with forward and reverse payloads (see Editing internals)
revision	A snapshot to restore. Nothing writes it yet.
api_key	Public API keys: hash, prefix, last_used_at

Notes:

• Identities are Supabase’s (auth.users). profile holds display data only.
• The slug is set by a trigger from the name when an organisation is created, with a number added on collision. Renaming never changes it, so links keep working.
• Deleting an organisation sets deleted_at. Its members, maps and graph stay until the maintenance sweep purges them, 30 days later. Read paths must filter deleted_at is null; getOrgs, getOrg, getOrgBySlug and API-key auth do.
• import_session.map_id is null while a “new map from file” import has no map yet. A two-file CSV import keeps its second file in connections_filename and connections_storage_path. A check requires both or neither, and only for format = 'csv'. See Import and export pipeline.
• api_key (org_id, user_id) is a foreign key to organisation_member, on delete cascade, so removing a member removes their keys. Only the service role can read the table.
• map_recipe.validated_through is the created_at of the map’s newest data change when the recipe was last confirmed or first saved. validated_by and validated_at stay null until someone confirms it. body_md is at most 20,000 characters (map_recipe_body_length), and an empty body means no recipe. How staleness is worked out is in Editing internals.

Enums

Enum	Values
app.org_role	org_admin, member. (system_admin is also in the enum, but membership rows don’t use it; system admins are in their own table.)
app.invitation_status	pending, accepted, expired, revoked
app.map_visibility	private, public
app.import_status	scanning, invalid, previewing, mapping, ingesting, ingested, failed
app.edit_session_status	active, idle, countdown, collapsing, collapsed, discarded
network.metric_value_type	numeric, boolean
network.metric_directionality	undirected, directed, mutual
network.date_precision	day, month, year

network schema

Table	Holds
node	label, properties jsonb, pos_x/pos_y, external_key
connection	source_node_id, target_node_id, metric_id (its connection type), value, properties jsonb, dates
node_measure	A node’s value for a measure (numeric)
node_tag	A node having a tag. No value column: the row is the fact.
measure	A map’s measures, with an optional category
tag	A map’s tags, with an optional tag_group_id
tag_group	A map’s tag groups
connection_type	A map’s connection types: value_type, directionality, inverse_name, value_label, value_unit

Every value belongs to its own map

Each table has unique (map_id, id), and every reference uses a composite foreign key on (map_id, …):

• node_measure, node_tag and both ends of connection reference node (map_id, id), on delete cascade.
• node_measure (map_id, measure_id) references measure, and node_tag (map_id, tag_id) references tag, both on delete cascade.
• node_tag and tag reference tag_group on (map_id, tag_group_id). Deleting a group sets the group column to null.
• connection (map_id, metric_id) references connection_type. Deleting a type sets metric_id to null.

So a value or connection can never point at another map’s node or definition. The server reports an attempt (Postgres error 23503) as FIELD_NOT_IN_MAP. Moving a node between maps isn’t supported.

Nodes

• Position may be null. pos_x and pos_y are both null (the node is unplaced) or both set (placed). Imports create unplaced nodes. Unplacing a node clears its position and nothing else.
• external_key is what the public API’s import matches on: 1–255 characters, unique (map_id, external_key). Nulls don’t clash. Only API imports set it.
• Labels are 1–255 characters. Several nodes can share one.

Connections

• A connection is one typed observation about a pair of nodes. Its metric_id points at one connection type, or is null. Any number of connections can join the same pair; there’s no unique constraint on the pair. A node can’t connect to itself (connection_no_self).
• There’s no direction column. Direction comes from the connection type’s directionality, read at query time. A connection with no type reads as undirected. source_node_id → target_node_id is the drawn order, which a directed type’s arrow follows.
• value is text, checked by the app against the type’s value_type. A numeric type may have a number on each connection. A boolean type stores 'true'. value_label and value_unit are allowed only on numeric types.
• A note is properties.reason. The UI keeps a connection to a type or a note. The schema allows both, and imports can produce both.
• “Defined” means it has a type or a non-empty properties. That’s a computed rule (isConnectionDefined in src/graph/definedness.ts), not a constraint.
• Dates: occurred_on (a one-off event) and valid_from/valid_to (a period; a null valid_to is ongoing). Each has a *_precision of day, month or year, set exactly when its date is.
  • A date is stored as the first day of its period: “2019” is 2019-01-01, precision year.
  • valid_from must not be after the end of valid_to’s period.
  • Dates never affect import matching.

Catalogue rules

• Measures are numbers and tags are membership. That’s enforced by shape: node_measure.value is numeric, and node_tag has no value. There’s no text-valued measure. Free text belongs in properties.
• A tag group is exclusive. node_tag keeps a copy of the tag’s tag_group_id so that unique (node_id, tag_group_id) can allow one tag per group per node. Nulls are distinct, so ungrouped tags are unlimited. The app keeps the copy in step when a tag is regrouped (fields.ts).
• Names aren’t unique within a map. Anything that creates a definition must match existing names itself, ignoring case, to avoid duplicates.
• Changing a connection type’s value_type goes through network.set_connection_type_value_type, which converts its connections in the same transaction. Boolean → numeric clears the 'true' values. Numeric → boolean replaces every value with 'true' and clears the label and unit, which loses data. The editor confirms first; the public API refuses.
• Code reads all three catalogue tables through one Field type (server/functions/fields.ts). See the Glossary.

Row-Level Security

RLS is on for every table.

network: every table has one policy:

using (map_id in (select app.user_map_ids()))
with check (map_id in (select app.user_map_ids()))

app.user_map_ids() returns the maps the caller can reach through organisation membership. The subquery doesn’t depend on the row, so Postgres runs it once per statement. System admins get no bypass here.

app:

Table	Who
organisation	Members read. Org admins and system admins write and delete.
organisation_member	Members read. Org admins and system admins write.
invitation	Org admins and system admins. Invitees read their own through the service role.
map	Members read (and anyone, for a public map). Members and org admins write.
saved_view, edit_session, revision, atomic_change	Anyone who can access the map.
map_recipe	Anyone who can access the map, or a system admin, reads. Members, org admins and system admins insert and update (app.user_can_edit_map). No delete policy: it goes with its map.
import_session	Its creator, or anyone who can access its map.
profile	The user themselves.
system_admin	System admins.
api_key	Nobody. RLS is on with no policies, so only the service role reaches it.

The helpers behind these are SECURITY DEFINER functions in app, which avoids recursive policy checks. auth.uid() is written (select auth.uid()) so it’s evaluated once per statement.

The service role bypasses RLS. See Auth and permissions.

Grants

RLS controls rows, but a role also needs ordinary SQL grants to touch a schema at all. A migration grants USAGE and ALL on app and network, including default privileges for future objects, to anon, authenticated and service_role. Without that, every query fails with 42501, even as the service role.

Those default privileges make every new function executable by anon and authenticated. Postgres also grants new functions to PUBLIC. So a function meant for the service role only must revoke the others:

revoke execute on function app.fn(args) from public, anon, authenticated;
grant execute on function app.fn(args) to service_role;

A new Supabase project also has to expose app and network to PostgREST. That’s a project setting, not SQL (see Deployment).

Storage

One private bucket, map-imports, holds uploaded import files. It has no Storage RLS policies. Uploads use short-lived signed tokens issued by the service role, and every server-side read uses the service role. A map’s files are deleted with the map.

Not modelled yet

• Node dates. Only connections have dates.
• Typed attributes per connection type, beyond one value.
• A shared library of vocabulary across maps, and a directory linking the same entity across maps (see Constraints and planned work).