SQL layer & schema
The Postgres schema and migration contract.
pgmem's SQL layer lives entirely in the pgmem Postgres schema namespace —
tables, indexes, and functions installed by versioned migrations. There's no graph
database and no separate service; it's plain PostgreSQL plus pgvector. This page
is an overview; DESIGN.md and sql/migrations/README.md in the repo are the full
spec.
Shape
- Namespace: everything is under the
pgmemschema. - Vectors:
pgvectorwith HNSW indexes; embedding dimension fixed at 1536. - Bi-temporal: facts carry validity bounds as generated
tstzrangecolumns — see The bi-temporal model. - Full-text: generated
tsvectorcolumns with GIN indexes (English regconfig); an optional BM25 backend is used when present.
Main tables
| Table | Tier | Holds |
|---|---|---|
entities | 4 | Semantic graph nodes. |
entity_edges | 4 | Relationships, bi-temporally bounded. |
episodes / episodic_edges | 4 | Source episodes and their mentions. |
communities / community_nodes | 4 | Community detection output. |
sessions | 0 | Session rows (group, user, MAGE pointers). |
session_turns | 1 | Per-turn buffer. |
session_state_nodes | 2 | The MAGE execution tree. |
session_compactions | 2 | Compaction audit trail + validation verdicts. |
memory_claims / memory_evidence | 3 | Authoritative scoped claims and immutable provenance. |
memory_claim_indexes / vectors / graph edges | 4 | Rebuildable lexical, vector, and graph claim projections. |
sagas / saga edges | 5 | Narrative threads and their session/episode links. |
promotion_jobs | — | The async Tier 3 → Tier 4 promotion queue. |
Session child tables (session_turns, session_state_nodes,
session_compactions) deliberately carry no group_id —
tenant isolation is transitive through the session handle. See
Multi-tenancy.
Migrations
The schema evolves through immutable, append-only migrations in sql/migrations/:
| Range | What it adds |
|---|---|
001–006 | Core graph schema, indexes, functions, HNSW tuning. |
007–024 | Sessions, claim ledger/evidence, narratives, projection queues, claim projections, and scope-isolation indexes. |
The current schema version is recorded in pgmem.control (0.20.0), decoupled from
the app version. Apply the schema with the plain-SQL installer
(psql -f sql/migrations/install.sql); see which migrations are applied by
querying pgmem.schema_migrations.
Migration files are immutable once released, and the packaged extension SQL
(sql/extension/pgmem--*.sql) is generated from the migrations, never edited by
hand. See Versioning & migrations.
Related
- Versioning & migrations — the upgrade contract
- Installation — applying the schema
- The bi-temporal model — the temporal columns
- The five-tier model — which tables back which tier