gnomonlearn
Docs/PostgreSQL/RFC-0051

RFC-0051 · PostgreSQL Storage Adapter

Versioned draft snapshot

RFCs, reference catalogs, and evidence reflect Spec 3b2e3e7a114fb826b1445d39a48756b8fff629cd. Counts and pending/status notes belong to that snapshot. Read the dated project update for later progress.

Source repositories are private during staging. Links marked “repository access required” need access to the linked repository.

Status Draft corpus. Reference catalogs and RDF projections have generation drift checks; Gnomon proofs and projection semantics remain unverified for release.

View the generated reference catalog →

Read design questions and decisions →


Reference catalog: the diagnostics, linked-RFC graph, defined terms, and normative-rule index for this RFC are in the reference catalog, generated from the structured Lean module. This document carries the prose: design rationale, entity-shape definitions in context, and migration notes.


1. Purpose

RFC-0037 defined the abstract GraphBackend service contract — eight services, query-as-data, transaction primitive — without specifying any concrete implementation. This RFC specifies the PostgreSQL implementation: which schemas, which tables, the RLS contract, the GUC session-context binding, the audit trigger framework, the reserved-namespaces enforcement, the dedicated-PG-roles model, and the Gnomon-type → Postgres-type mapping.

This RFC is one of multiple possible adapter implementations. Future RFCs MAY specify SQLite, DuckDB, in-memory, or distributed-KV adapters — they implement the same RFC-0037 contract differently. PostgreSQL is the canonical adapter for hosted Gnomon deployments and the one against which all integration tests run.

This RFC supersedes the assumed Postgres specifics that RFC-0037 hand-waves at, and is the foundation for the platform module catalog (RFC-0052), the view-mapping layer (RFC-0053), and the DDL emission pipeline (RFC-0054).


2. Definitions

TermMeaning
AdapterThe Postgres-specific implementation of RFC-0037’s GraphBackend interfaces.
Platform schemaOne of the six PostgreSQL schemas this adapter installs at bootstrap: graph, audit, auth, ids, queries, webhook.
Module schemaA PostgreSQL schema named after a module’s namespace (savvi.studio.team → savvi_studio_team), used as the container for the module’s view-mapping outputs (RFC-0053).
GUCA PostgreSQL “Grand Unified Configuration” parameter — session-bound key-value state. Used to carry runtime context (principal, tenant, admin flag, template marker).
Bootstrap.sqlThe committed SQL artifact (KP24) that creates the minimal Phase-1 schema needed before the install pipeline can run.
Reserved namespaceAn ltree path under which only privileged paths may create gnomons (graph.*, core.*).

3. PostgreSQL Extensions

The adapter requires the following PostgreSQL extensions to be available:

ExtensionPurpose
ltreeThe kind/predicate path system (RFC-0036 §3.2). Load-bearing — the adapter cannot function without it.
pgcryptoSHA-256 hashing for argHash (RFC-0047, RFC-0050 §4.2), HMAC for cursor tokens (RFC-0038 §5.2), session signing keys.
uuid-osspUUIDv1, v3, v4, and v5 generation for legacy identifiers. It does not provide UUIDv7.
hstoreInternal use by some legacy tables; non-load-bearing for the data model but expected by ids schema.

The adapter MUST refuse to boot if any required extension is unavailable (M51010). Extensions MUST be installed by a privileged operator before the adapter’s first boot — they are NOT installable from within the adapter’s transaction.


4. Platform Schemas

The adapter installs six platform PostgreSQL schemas. Each is owned by a dedicated PG role (KP28).

PG schemaOwner roleModulePurpose
graphstudio_graph_ownerdb.graphResource, statement, predicate, predicate_policy, permission_cache, external_link tables; CRUD/traversal/collection/permission functions
auditstudio_audit_ownerdb.auditaudit.events table + install_audit_trigger(regclass) framework
authstudio_auth_ownerdb.authSessions, secrets, JWT verification, key rotation
ids(no specific owner; functions are SECURITY DEFINER)db.idsDistributed ID generators, generator_metadata, domain types
queriesstudio_queries_ownerdb.queriesShared query subsystem
webhookstudio_webhook_ownerdb.webhookWebhook providers, endpoints, event mappings

The db.* modules that own these schemas are catalogued in RFC-0052.

4.1 Schema-namespace correspondence

The adapter establishes a 1:1 correspondence between Gnomon namespaces and PG schemas for application module views:

Gnomon namespace          PG schema
savvi.studio.team   ↔   savvi_studio_team
savvi.studio.product ↔  savvi_studio_product
ext.clickup         ↔   ext_clickup

Dots become underscores; the schema is created at install time when the first view-mapping output (RFC-0053) for the module is emitted. Empty modules (no entity types) get no PG schema.

4.2 Schema ownership

Module schemas are owned by role_<module_namespace_underscored> (e.g., role_savvi_studio_team). The install engine (RFC-0041) SET ROLE to the module’s role before executing module-scoped DDL. This is the SH3 enforcement (KP28) for module isolation:

SET ROLE role_savvi_studio_team;
-- All DDL emitted by this module's compile pipeline
RESET ROLE;

A module’s role can write to its own schema and read from graph.* (RLS-filtered) but cannot write to other modules’ schemas. Cross-module reads via shared views go through graph.* and the RLS layer — never via direct schema access.

4.3 Development fallback

In development, dedicated roles per module are operationally heavy. The adapter MAY use a single studio_app_connector role + SET LOCAL gnomon.current_module = '<namespace>' GUC + RLS/trigger checks (KP28 fallback). Production MUST use dedicated roles.


5. The graph.resource and graph.statement Tables

Verbatim from the legacy db/migrations/000_initial_schema/050_graph.sql:

CREATE TABLE graph.resource (
    id          BIGINT PRIMARY KEY DEFAULT ids.gen_resource_id(),
    kind        LTREE NOT NULL,
    external_id TEXT,
    data        JSONB NOT NULL DEFAULT '{}'::JSONB,
    creator_id  BIGINT REFERENCES graph.resource (id) ON DELETE SET NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at  TIMESTAMPTZ,
    CONSTRAINT unique_resource UNIQUE (kind, external_id)
);

CREATE INDEX idx_resource_kind        ON graph.resource USING gist (kind);
CREATE INDEX idx_resource_external_id ON graph.resource (external_id) WHERE external_id IS NOT NULL;
CREATE INDEX idx_resource_data        ON graph.resource USING gin (data);
CREATE INDEX idx_resource_creator     ON graph.resource (creator_id) WHERE creator_id IS NOT NULL;
CREATE INDEX idx_resource_active      ON graph.resource (id) WHERE deleted_at IS NULL;
CREATE TABLE graph.statement (
    id           BIGINT PRIMARY KEY DEFAULT ids.gen_statement_id(),
    subject_id   BIGINT NOT NULL REFERENCES graph.resource (id) ON DELETE CASCADE,
    predicate_id BIGINT NOT NULL REFERENCES graph.resource (id) ON DELETE RESTRICT,
    object_id    BIGINT NOT NULL REFERENCES graph.resource (id) ON DELETE CASCADE,
    data         JSONB NOT NULL DEFAULT '{}'::JSONB,
    creator_id   BIGINT REFERENCES graph.resource (id) ON DELETE SET NULL,
    valid_from   TIMESTAMPTZ NOT NULL DEFAULT now(),
    valid_to     TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT unique_statement UNIQUE (subject_id, predicate_id, object_id)
);

These tables are normative for the PostgreSQL adapter. The Gnomon data model (RFC-0036 §4) maps directly:

RFC-0036 fieldPG columnNotes
idid BIGINTSnowflake ID from ids.gen_resource_id() / ids.gen_statement_id()
kindkind LTREEIndexed via gist
externalIdexternal_id TEXTIndexed when present
datadata JSONBIndexed via gin for arbitrary key queries
creatorIdcreator_id BIGINTFK to graph.resource
createdAtcreated_at TIMESTAMPTZDefault now()
updatedAtupdated_at TIMESTAMPTZMaintained by trigger
deletedAtdeleted_at TIMESTAMPTZNull = active
subjectId/predicateId/objectIdcorresponding *_id BIGINTFK with ON DELETE CASCADE for subject/object, ON DELETE RESTRICT for predicate
validFrom/validTovalid_from/valid_to TIMESTAMPTZEdge-level bitemporal

6. Distributed ID Generation

6.1 The ids schema

ids.gen_resource_id() and ids.gen_statement_id() are Snowflake-style 64-bit ID generators implemented in plpgsql:

id = (timestamp_bits * 2^(machine_bits + sequence_bits))
   + (machine_id * 2^sequence_bits)
   + sequence

Default config (legacy parity):

ComponentBitsRange
epoch_ms—Custom epoch ms-since-Unix-epoch
timestamp_bits41~69 years from epoch
machine_bits101024 machines
sequence_bits124096 ids per ms per machine

Sum of timestamp + machine + sequence MUST equal 63 (one bit reserved for sign — IDs are positive BIGINT).

6.2 Per-kind generators

A new generator can be created via ids.create_generator(name, config). The legacy db.graph declares resource_id and statement_id; new generators MAY be declared by other db.* modules for their own ID streams (e.g., audit_event_id).

6.3 ID ranges and module isolation

The install engine (RFC-0041) MAY allocate ID ranges per module to enable offline plan computation: each module’s machine_id is set to a deterministic value derived from the module’s manifest hash. This is optional; the default single-machine-id is correct for non-distributed deployments.


7. RLS Contract

7.1 Session GUCs

The adapter lists five PostgreSQL GUC parameters used by the runtime and install pipeline:

GUCTypeSet byPurpose
app.current_user_idBIGINTRuntime at request entry (RFC-0048)Bound principal id; RLS reads from this
app.current_user_typeLTREERuntime at request entryBound principal kind (e.g., identity.user)
app.is_adminBOOLEANRuntime at request entryTrue if session is admin-elevated
gnomon.current_moduleTEXTInstall engine per-moduleActive install-module namespace (KP28 fallback)
gnomon.template_markerTEXTTemplate executor (RFC-0050 §3.3)Active template invocation marker

The adapter exposes graph.set_session_user(user_id, user_type, is_admin) to set the first three atomically. The runtime calls this at the start of every request transaction.

7.2 Permission evaluation

graph.has_permission(subject_id, subject_type, resource_id, resource_kind, required_bits) is the single permission oracle. Implementation:

1. If is_admin_session() → return true.
2. If subject_id is the resource's creator → grant write/admin permissions per workspace policy.
3. For each predicate-policy whose scope matches (resource_kind, resource_id):
     find statements where (subject_id, policy.predicate, ...) holds (matchMode-aware)
     accumulate policy.permission_bits
4. Return (accumulated_bits & required_bits) == required_bits.

This is the runtime form of the RFC-0042 §4.2 evaluation algorithm. It MUST match the abstract algorithm exactly; divergence is a defect (M51020).

7.3 RLS policies on graph.resource and graph.statement

ALTER TABLE graph.resource  ENABLE ROW LEVEL SECURITY;
ALTER TABLE graph.statement ENABLE ROW LEVEL SECURITY;

CREATE POLICY resource_select ON graph.resource
FOR SELECT USING (
  deleted_at IS NULL
  AND graph.has_permission(graph.current_user_id(), graph.current_user_type(),
                           id, kind, 1::SMALLINT)
);

CREATE POLICY resource_insert ON graph.resource
FOR INSERT WITH CHECK (
  graph.is_admin_session()
  OR current_setting('gnomon.template_marker', true) IS NOT NULL
  OR (NOT (kind <@ 'graph'::ltree) AND NOT (kind <@ 'core'::ltree))
);

CREATE POLICY resource_update ON graph.resource
FOR UPDATE USING (
  graph.has_permission(graph.current_user_id(), graph.current_user_type(),
                       id, kind, 3::SMALLINT)
);

CREATE POLICY resource_delete ON graph.resource
FOR DELETE USING (
  graph.has_permission(graph.current_user_id(), graph.current_user_type(),
                       id, kind, 3::SMALLINT)
);

Statement policies follow the same pattern, evaluating has_permission on the underlying subject/object resources.

7.4 Kind immutability trigger (SH1)

Per kernel-primitives.md §SH1:

CREATE TRIGGER resource_enforce_kind_immutability
BEFORE UPDATE ON graph.resource
FOR EACH ROW EXECUTE FUNCTION graph.enforce_kind_immutability();

The function rejects any UPDATE that changes kind. Kind immutability is load-bearing for permission caching, RLS, and traversal correctness.


8. Reserved Namespaces

graph.reserved_namespaces carries ltree paths that may not be modified by user code:

graph.*       — graph platform schema
core.*        — Gnomon structural meta-gnomons
db.*          — platform module schemas
gnomon.*        — runtime control namespaces

graph.is_reserved_type(kind) checks if a kind is reserved (subtree-match against the table). The trigger prevent_reserved_type_modification rejects UPDATE/DELETE on reserved-type rows.

The reserved-namespaces table is bootstrapped by bootstrap.sql and extended by the db.graph module install. Application modules MUST NOT add entries (M51030).


9. Audit Trigger Framework

9.1 The audit.events table

Every mutating write to a graph table produces a row in audit.events:

audit.events {
  id           BIGINT PRIMARY KEY DEFAULT ids.gen_audit_event_id(),
  table_oid    OID NOT NULL,
  operation    TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
  row_id       BIGINT NOT NULL,
  before_data  JSONB,
  after_data   JSONB,
  principal_id BIGINT,
  tenant_id    TEXT,
  occurred_at  TIMESTAMPTZ NOT NULL DEFAULT now()
}

9.2 audit.install_audit_trigger(table_oid)

The framework function. Called once per table at install time, it adds a trigger that emits to audit.events on every INSERT/UPDATE/DELETE. The trigger reads app.current_user_id and app.current_tenant_id GUCs to populate the audit row.

The platform schema tables (graph.resource, graph.statement, the predicate-policy tables, etc.) all carry audit triggers from bootstrap. New tables emitted by the DDL pipeline (RFC-0054) get audit triggers automatically when they declare the audit-trail aspect (db.pg.audit-trail).

9.3 Audit retention

Audit events are kept indefinitely by default; partitioning and retention are operator-configured via defaultConfig (RFC-0046). The adapter does NOT auto-purge audit events.


10. PG Type Mapping

The Gnomon type model (RFC-0006) maps to PostgreSQL types per db.pg’s 28-type catalog (q2 plan §sql-ast-track-findings.md §4):

Gnomon typePG typeNotes
TextTEXTDefault; no length limit
Text(N)VARCHAR(N)When length is significant
IdentifierTEXTBranded text alias (RFC-0035 §6)
BigintBIGINTUsed for IDs
IntegerINTEGERDefault integer
NumberNUMERICArbitrary-precision decimal
FloatDOUBLE PRECISIONIEEE 754 double
BooleanBOOLEAN
Instant, TimestampTIMESTAMPTZUTC by convention; runtime sets timezone = 'UTC'
DateDATE
Map<K, V>JSONBAlways stored as JSONB
List<T>JSONB (default) or T[]JSONB for indexability with gin; arrays for high-cardinality numeric lists
BytesBYTEA
UuidUUID
Email, URL, Locale, CurrencyCodeTEXTBranded text aliases (RFC-0035 §6)
LtreePathLTREEUsed for kind, predicate.path
MoneyNUMERIC(20, 4)Precision is conventional; configurable per module

The mapping is canonical for the Postgres adapter. Other adapters (SQLite, etc.) provide their own mapping in their own RFC.


11. RFC-0037 Service → PG Function Mapping

This table specifies how each RFC-0037 backend service operation lowers to a PostgreSQL function call:

11.1 ResourceService

RFC-0037 queryPG call
Get { id }graph.get_resource(id)
GetByExternalId { kind, externalId }graph.get_resource_by_external_id(kind, externalId)
Query { filters }parameterized SELECT FROM graph.resource WHERE ... with cursor
Create { input }graph.create_resource(...)
Update { id, input }graph.update_resource(id, ...)
Delete { id }graph.delete_resource(id) (soft delete)

11.2 StatementService

RFC-0037 queryPG call
Get { id }graph.get_statement(id)
Query { filters }parameterized SELECT FROM graph.statement WHERE ...
Create { input }graph.create_statement(...)
CreateMany { inputs }graph.batch_create_statements(...)
Update { id, input }graph.update_statement(id, ...)
Invalidate { id }graph.invalidate_statement(id)

11.3 PermissionService

RFC-0037 queryPG call
GetPermission { params }graph.has_permission(...)
GetSubjectPermissions { params }graph.get_subject_permissions(...)

11.4 CollectionService

The eight CollectionService operations map to graph.create_list, graph.list_append, graph.list_insert, graph.list_remove, graph.create_set, graph.set_add, etc. — see legacy 050_graph.sql for full signatures.

11.5 TraversalService

RFC-0037 queryPG call
GetOutgoing { resourceId, predicatePattern }graph.get_outgoing(resource_id, predicate_pattern)
GetIncoming { resourceId, predicatePattern }graph.get_incoming(resource_id, predicate_pattern)
Traverse { startId, options }graph.traverse(start_id, options::JSONB)

11.6 PredicateService

RFC-0037 queryPG call
GetId { predicateUri }graph.get_predicate_id(predicate_uri)

11.7 WorkspaceService

Workspace operations are read-only views over graph.resource filtered to kind <@ 'workspace'::ltree. The legacy adapter implements them as standalone PG functions; this RFC normatively maps them to projection-style read queries (RFC-0049) with materialize: live.

11.8 TransactionService

RFC-0037 methodPG equivalent
begin()BEGIN (returns a session-scoped tx-id from pg_current_xact_id())
commit(id?)COMMIT
rollback(id?)ROLLBACK
transaction(callback)savepoint-aware wrapper

12. Bootstrap.sql Contract

12.1 Purpose

bootstrap.sql is a frozen, committed SQL artifact that creates the minimal Phase-1 schema needed before the install pipeline can run (KP15, KP24). On a fresh database, it’s the first SQL that executes; the install pipeline then runs and observes install(db.graph, bootstrap_db) = no_change.

12.2 Contents

bootstrap.sql contains:

  1. CREATE EXTENSION for ltree, pgcrypto, uuid-ossp, hstore.
  2. The six platform schemas + their owner roles.
  3. The ids.gen_resource_id() and ids.gen_statement_id() functions.
  4. graph.resource, graph.statement, graph.predicate_policy, graph.reserved_namespaces tables (plus their indexes).
  5. graph.has_permission(), graph.set_session_user(), graph.is_admin_session() functions.
  6. The bootstrap RLS policies.
  7. The audit framework (audit.events, audit.install_audit_trigger()).
  8. Initial reserved-namespace rows (graph, core, db, gnomon).
  9. The system installer principal record.

12.3 Generation

bootstrap.sql is generated from the db.graph, db.audit, and db.ids modules’ compiled output by the DDL emission pipeline (RFC-0054). It is committed to source like a lockfile.

CI MUST regenerate from current source and fail on diff (M51040). Operators reviewing graph-substrate changes see the SQL diff explicitly.

12.4 Versioning

bootstrap.sql is tagged with the formatVersion field (RFC-0002). Changes to the Phase-1 schema bump the format version. The runtime refuses to boot if the bootstrapped DB’s format version is incompatible (M51011).


13. Invariants

  1. The adapter requires ltree, pgcrypto, uuid-ossp, hstore extensions (§3, M51010).
  2. The six platform schemas exist with their declared owner roles after bootstrap (§4).
  3. graph.resource and graph.statement schemas match the legacy DDL exactly (§5).
  4. IDs are generated by ids.gen_*_id() Snowflake functions (§6).
  5. Session GUCs (app.current_user_id, etc.) are set at the start of every request transaction (§7.1).
  6. graph.has_permission matches RFC-0042 §4.2 evaluation algorithm exactly (§7.2, M51020).
  7. kind is immutable after creation; SH1 trigger enforces (§7.4).
  8. RLS gates direct INSERT into graph.* and core.* kinds without a template marker (§7.3, ties to RFC-0050 §9.2).
  9. bootstrap.sql is a generated artifact regenerated by CI and verified by diff (§12.3, M51040).
  10. RFC-0037 service queries map deterministically to PG function calls per §11.

14. Compatibility and Migration

14.1 From the legacy schema

The legacy schema at /Volumes/Workspace/savvi-studio-full/db/migrations/000_initial_schema/ is a faithful subset of what this RFC normatively requires:

Legacy fileThis RFC
000_prelude.sql§3 (extensions)
010_ids.sql§6 (ID generators)
020_audit.sql§9 (audit framework)
040_auth.sqlRFC-0048 (auth schema) — adapter consumes its tables
050_graph.sql§5, §7, §8 (graph schema, RLS, reserved namespaces)
051_graph_policies.sql§7 (policy tables — see RFC-0042)
060_webhook.sqlRFC-0052 (webhook module)
070_util.sqlRFC-0052 (db.util module)
999_grants.sqlderived from §4.2 (role hierarchy)

Existing deployments need no schema migration; the legacy schema satisfies this RFC.

14.2 New requirements

  • gnomon.template_marker GUC is new (§7.1). The runtime MUST be updated to set/clear it around template executions (RFC-0050 §3.3).
  • Updated resource_insert policy to gate on template_marker presence (§7.3) — replacing the legacy permissive INSERT.
  • Format-versioned bootstrap.sql (§12.4) — legacy bootstrap is unversioned; this RFC requires the version field and CI diff check.

14.3 Multi-tenancy

The adapter supports single-tenant and multi-tenant deployments. In multi-tenant mode (GNOMON_TENANT_MODE=multi per RFC-0045), an app.current_tenant_id GUC scopes all queries; tenant-scoped RLS predicates are AND’d into the policies in §7.3.

The full tenancy model is the subject of a future RFC (multi-tenancy & isolation, mentioned in docs/spikes/proposal-runtime-and-persistence-rfcs.md (see spec source)).


Diagnostics

The diagnostic catalog for this RFC is authored in the structured Lean module and rendered in reference catalog § Diagnostics. Inline mentions in the prose above (e.g., “rejected with MNNxxx”) cross-link to the catalog at render time.

Linked References

The cross-RFC dependency graph is rendered in reference catalog § Linked References. Out-of-KG pointers to legacy code or external resources, when present, remain in this document under “Source-tree references” below.