Database functions and indexes

This page lists the SQL functions in app and network, the indexes added beyond the basic foreign-key and filter ones, and the daily maintenance sweep. Tables, columns and RLS policies are on Data model.

Each function’s current definition is in the latest migration that creates it. Find it with grep -l "function network.delete_map" supabase/migrations/*.sql | tail -1.

Who can execute a function

20260706170000_grant_app_network_schema_privileges.sql grants ALL on every routine in app and network to anon, authenticated and service_role, as a default privilege for future functions too. Postgres also grants EXECUTE to PUBLIC on every new function. So a new function is callable by everyone through PostgREST unless its migration says otherwise.

grant execute ... to service_role on its own restricts nothing. A function meant only for the service role must revoke the others explicitly:

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

Do this for every security definer function that reads data the caller couldn’t read directly.

security invoker functions run with the caller’s privileges, so RLS applies inside them as it would to a direct table write. Server functions still call assertMapAccess first to turn an RLS failure into a clear error.

A new Supabase project also needs app and network added to PostgREST’s exposed schemas. A migration can’t do that. scripts/supa-configure-project.sh does it (see First-run setup).

RLS helpers

All are language sql, stable, security definer, with search_path = ''. They read auth.uid(), so they return false (or nothing) for an anonymous caller. Executable by everyone (default grants), except where noted.

Function	Returns	What it does
app.user_is_system_admin()	boolean	Whether the caller has a row in app.system_admin.
app.user_is_org_member(target_org_id uuid)	boolean	Whether the caller is a member of the org, in any role.
app.user_has_org_role(target_org_id uuid, required_roles app.org_role[])	boolean	Whether the caller’s role in the org is one of required_roles. No system-admin bypass.
app.user_can_access_map(target_map_id uuid)	boolean	Whether the caller is a member of the org that owns the map. Joins app tables only.
app.user_map_ids()	setof uuid	Every map id the caller reaches through org membership. No system-admin bypass.
app.user_can_edit_map(target_map_id uuid)	boolean	Whether the caller is an org_admin or member of the map’s org, or a system admin. Not executable by anon.

user_map_ids() backs every network policy as map_id in (select app.user_map_ids()). The subquery is uncorrelated, so Postgres runs it once per statement, not once per row.

server/lib/authz.ts wraps the first four as assertOrgMember, assertOrgRole, assertMapAccess and assertSystemAdmin, and user_can_edit_map as canEditMap and assertCanEditMap. assertOrgRole also passes a system admin.

Other app functions and triggers

Function	Security	Execute	What it does
app.get_user_emails(p_user_ids uuid[]) returns table (user_id uuid, email text)	definer	service_role only	Reads emails from auth.users for a batch of user ids. Used by getMembers and isEmailAlreadyMember.
app.map_recipe_state(p_map_id uuid, p_data_event_types text[]) returns table (data_changed_at timestamptz, stale boolean)	invoker	authenticated, service_role	When the map’s data last changed (its newest atomic_change of one of the given types), and whether that’s later than the recipe’s validated_through. A blank recipe is never stale. See Editing internals.
app.check_invitation_not_existing_member() returns trigger	definer	trigger	Raises an exception if the invited email already belongs to a member of the org (case-insensitive).
app.organisation_set_slug() returns trigger	invoker	trigger	Fills slug from name when an insert doesn’t supply one, adding -1, -2, … on collision. An empty result becomes org.

Triggers:

• invitation_not_existing_member: before insert on app.invitation. It doesn’t fire on update, because acceptInvitation marks the invitation accepted after inserting the membership row.
• organisation_set_slug_trigger: before insert on app.organisation.

network functions

All are plpgsql with search_path = '' unless noted.

Function	Security	Execute	What it does
create_node_in_map(p_map_id uuid, p_label text, p_pos_x float8, p_pos_y float8, p_properties jsonb default '{}', p_node_id uuid default null) returns table (node_id uuid)	invoker	everyone	Inserts one node. Null positions make it unplaced. Uses p_node_id when given, so the client can mint ids for optimistic inserts. Called by createNode.
delete_node_from_map(p_map_id uuid, p_node_id uuid) returns table (node_deleted boolean)	invoker	everyone	Deletes the node if it is in that map. Composite FKs cascade its connections, measures and tags. Returns false and changes nothing for a node in another map. Called by deleteNode.
delete_map(p_map_id uuid) returns void	invoker	everyone	Deletes the map’s nodes (cascading their values and connections), any stray connections, then its connection_type, measure, tag and tag_group rows. deleteMap calls it before deleting app.map, since network.map_id has no FK into app.
set_node_positions(p_map_id uuid, p_positions jsonb) returns integer	invoker	everyone	Sets pos_x/pos_y for an array of {node_id, pos_x, pos_y} in one statement. Nulls unplace. Touches only nodes in the map and returns the row count. placeNodes, moveNodes and unplaceNodes reject the request with NODE_NOT_IN_MAP when the count is short.
set_connection_type_value_type(p_map_id uuid, p_type_id uuid, p_value_type network.metric_value_type) returns void	invoker	authenticated, service_role	Changes whether a connection type records a value and rewrites its connections in the same transaction. Boolean to numeric sets values to null. Numeric to boolean sets values to 'true' and clears value_label and value_unit. Raises P0002 if the type isn’t in the map. Called by updateFieldForMap and the CSV import.
is_period_start(p_date date, p_precision network.date_precision) returns boolean	invoker, sql immutable	everyone	True when the date is the first day of its year or month (always true for day). Used by the connection date check constraints.
period_end(p_date date, p_precision network.date_precision) returns date	invoker, sql immutable	everyone	The last day the date covers, for example 2025-12-31 for the year 2025. Used by connection_valid_period_order.
purge_dead_maps(p_live_map_ids uuid[]) returns table (deleted_connections int, deleted_nodes int, deleted_definitions int)	invoker	service_role only	Deletes every node, connection and catalogue row whose map_id isn’t in the list. Raises an exception on a null or empty list, which would otherwise wipe the graph.
purge_maps(p_map_ids uuid[]) returns void	invoker	service_role only	Deletes the given maps’ whole network footprint, like delete_map for many maps. An empty list does nothing.

“Everyone” means the default grants: PUBLIC, anon, authenticated and service_role. For the security invoker functions RLS still decides which rows a caller touches.

Indexes beyond the basics

Most FK and policy columns have a plain index. These indexes serve specific paths:

Index	Serves
network.node (map_id, id) unique	The target of the composite FKs, and whole-map reads.
network.node_measure (map_id, id), network.node_tag (map_id, id)	Whole-map value reads and their fetchAllRows paging order.
network.node (map_id, created_at, id) where pos_x is null	The paged Unplaced list (getUnplacedNodes).
network.connection (map_id, metric_id)	The composite FK to connection_type, and deleteField’s filter.
network.node_measure (map_id, measure_id), network.node_tag (map_id, tag_id), network.node_tag (map_id, tag_group_id), network.tag (map_id, tag_group_id)	The composite FKs into the catalogue, and deletes that filter on them.
network.connection (map_id, source_node_id, target_node_id)	Finding every connection between one pair of nodes.
network.connection (map_id, occurred_on), network.connection (map_id, valid_from, valid_to)	Date filters on connections.
app.atomic_change (session_id, stack_index)	captureChange’s top-of-stack lookup on every edit.
app.api_key (org_id, user_id)	The cascade that deletes a member’s API keys when they leave the org.

Where the code is: supabase/migrations/20260720120200_perf_indexes.sql, 20260928170000_map_scoped_nodes.sql, 20260929120000_catalogue_map_scoped.sql, 20260930160000_connection_dates.sql.

Maintenance sweep

purgeOrphanedData(serviceClient) runs three steps with the service-role client, in order:

1. Dead maps. Reads every app.map id and passes them to network.purge_dead_maps. Skipped when there are no maps at all.
2. Soft-deleted orgs. For each org whose deleted_at is more than 30 days old (ORG_PURGE_RETENTION_MS): reads its map ids from app.map and calls network.purge_maps, removes its files from the map-imports Storage bucket ({orgId}/{mapId}/{file}), then deletes the app.organisation row. App-schema FKs cascade its members, maps, saved views, import and edit sessions, and revisions.
3. Stuck imports. Marks app.import_session rows still ingesting 6 hours after created_at (STUCK_IMPORT_TIMEOUT_MS) as failed.

It returns { deletedConnections, deletedNodes, deletedDefinitions, purgedOrgs, failedStuckImports }. A failing step throws and stops the sweep.

deleteOrg only sets deleted_at. Every read path filters on deleted_at is null, so the org disappears at once and its data stays recoverable until the sweep purges it.

The sweep runs through POST /api/maintenance/purge, a server route with no user session. It returns 503 when MAINTENANCE_PURGE_SECRET is unset and 401 when the x-maintenance-secret header doesn’t match. The network-mapping-purge cron job in render.yaml calls it daily at 03:00 UTC with curl.

Where the code is: apps/app/src/server/lib/maintenance.ts, apps/app/src/routes/api/maintenance/purge.ts, render.yaml.