Newer
Older
gnexus-book / 50-decisions / ADR-0002-postgres-storage-and-versioning.md

owner: gmikcon status: draft last_reviewed: 2026-11-19 review_interval: 30d confidence: medium

source_of_truth: analysis-2026-11-19

ADR-0002: PostgreSQL storage with built-in versioning

Status

Draft, proposed on 2026-11-19. Not implemented and not accepted.

Context

Gnexus Book currently keeps its knowledge base as Markdown and YAML inside a Git checkout. The FastAPI server reads those files, applies proposed changes, validates the whole repository, and creates local Git commits. The UI and MCP server are thin layers over the same file-backed repositories.

Current characteristics:

  • effectively single-user: no authentication, no actor attribution, no per-user data;
  • Git is both the storage format and the version-control mechanism;
  • every write is a filesystem mutation followed by a whole-repository validation;
  • inventory items are YAML records validated by JSON Schema;
  • pending changes are JSON files under 90-maintenance/pending-changes/;
  • the MCP server exposes 17 tools and 9 resources over stdio or SSE;
  • the Vue UI has four read-oriented tabs and two write paths (apply change, commit).

Owner direction (2026-11-19):

  1. Back up the current knowledge base before changing anything.
  2. Move primary storage to a database.
  3. Build version control into the service instead of relying on Git.
  4. Keep the existing knowledge base as-is until the new system is tested, then migrate it.

Platform-wide rules in gnexus-handbook also apply. They are not repeated here; the relevant sections are linked below.

Decisions

1. PostgreSQL is the primary store

PostgreSQL becomes the system of record for documents, inventory, versions, users, changesets, and audit data. Git stops being the write path.

Reasons: multi-user concurrency and transactions, relational integrity, full-text search, JSONB for the flexible inventory records, mature backup/restore tooling.

2. Versioning is application-level, not a Git reimplementation

The service stores immutable document and inventory versions grouped into changesets. It does not implement branches, merges, packfiles, remotes, or a general-purpose distributed VCS. The current functionality does not require them.

Required semantics:

  • every applied change creates a new immutable version;
  • a changeset groups one or more versions and is applied atomically;
  • author, actor, timestamp, summary, and reason are recorded;
  • diff is computed between two versions on demand;
  • rollback restores content by creating a new version, never by rewriting history;
  • a failed validation aborts the transaction and leaves no partial state.

3. Identity and access follow gnexus-handbook

  • User authentication is delegated to gnexus-auth (OAuth 2.0 PKCE). The service does not own a user database or a login page; it mirrors the sub principal and profile.
  • Browser sessions follow the cookie-session pattern (or the Synapse Bearer variant) described in 10-platform/auth.md.
  • user.*, global_logout, session.revoked, and role/permission webhooks are required, with HMAC verification.
  • Locale preference is stored per user and defaults to the gnexus-auth account locale.

4. MCP follows gnexus-handbook

  • The service is its own MCP server over streamable HTTP at /mcp-protocol/ (trailing slash significant). SSE is a legacy fallback only; stdio is not the production transport.
  • Agents authenticate with per-user mcp_* keys, not gnexus-auth access tokens.
  • Keys are stored as SHA-256 hash plus tail hint; one active key per (user_id, consumer); revocation is revoked_at, never deletion.
  • The UI exposes an "MCP keys" page. Internal /internal/keys provisioning endpoints are required (not published through nginx, service-to-service secret + allowlist).
  • The guard is a pure ASGI class, returns a uniform 401, and updates last_used_at.
  • Mutating tools accept is_user; REST accepts X-Actor: user; audit records actor_kind, actor_name, and via.
  • Tool implementations share the service layer with REST routes, including validation and error codes; tool errors are returned as data.

5. Backups

The database is dumped on a schedule to a separate host, with a documented restore procedure and periodic restore drills. A dump that has never been restored does not count as a backup. This follows 20-deployment/backups.md.

There is no Markdown/JSONL export from the database in the first version (owner decision). The dumps are the only safety net, so retention, off-host storage, and restore verification are mandatory.

6. Existing files remain canonical until cutover

The current repository stays the canonical source until the new backend passes its tests and a migration has been verified. The importer is a later task and must be repeatable.

Draft data model

users                 -- mirror of gnexus-auth principals
  id, sub (unique), email, display_name, profile jsonb, locale,
  created_at, updated_at, blocked_at

user_sessions         -- cookie-session pattern, if used
  id, user_id, created_at, expires_at

oauth_states          -- PKCE state

documents
  id, slug (unique), title, created_at, deleted_at

doc_versions
  id, document_id, version_no, parent_version_id, content_hash,
  raw text, body text, frontmatter jsonb,
  author_user_id, actor_kind, actor_name, via, changeset_id, created_at
  unique (document_id, version_no)

blobs
  hash pk, content text          -- content-addressed deduplication

inventory_types
  name pk, schema_id

schemas
  id, name, version, body jsonb, created_at

inventory_items
  id, type, item_id, data jsonb, current_version_id, deleted_at
  unique (type, item_id) among live rows

inventory_item_versions
  id, inventory_item_id, version_no, data jsonb,
  author_user_id, changeset_id, created_at

changesets
  id, author_user_id, summary, reason,
  status (draft | applied | rejected), created_at, applied_at, validation jsonb

change_items
  id, changeset_id, kind (doc | inventory-item), target, payload jsonb, status

validation_runs
  id, changeset_id, status, issues jsonb, created_at

mcp_tokens
  id, user_id, consumer, name, role, scopes jsonb,
  token_hash (unique), token_hint, created_at, last_used_at, revoked_at
  partial unique (user_id, consumer) where revoked_at is null

user_prefs
  user_id, locale

audit_log
  id, actor_user_id, actor_kind, actor_name, via,
  action, entity_type, entity_id, before jsonb, after jsonb, created_at

Search is a tsvector index on document versions plus a trigram/substring path to preserve the current "find a line containing the query" behavior. Relationships are computed from inventory_items.data as they are today; materialized edges are an optimization, not a v1 requirement.

Functional coverage

The new system must close the current functionality before the migration. This table is the acceptance checklist.

Capability Current implementation New implementation
List documents Recursive *.md scan, title from first H1 or file stem, frontmatter dict Query current document versions
Read document path → {path, frontmatter, body, raw} Lookup by slug/id; same response shape
Search Case-insensitive substring, up to 5 matching lines per document FTS plus substring/line-match path; keep response shape
Create/update document Pending change → write file → validate → rollback on failure Draft changeset → apply transaction → validate
Path safety Reject escapes, non-.md, denied directories Slug/route validation; no filesystem paths
Inventory types *.yml files inventory_types rows
Inventory read raw YAML, parsed list, item by id JSONB records; raw view is a defined serialization
Inventory create/update patch merge or create append, immutable id Change item applied in a transaction
JSON Schema validation Draft 2020-12, per-type mapping Same schemas stored in schemas
Unique inventory IDs Per-file scan Unique constraint per type among live items
Secret scanning Regex over Markdown/YAML/JSON/dotenv Same scan on every candidate version
Freshness Frontmatter presence, stale by interval, missing docs targets SQL queries over versions and inventory
Relationship graph Nodes <type>/<id>, edges from known fields, unresolved refs Same computation over inventory data; optional materialized edges
Pending changes JSON files, pending/applied/rejected, apply + rollback changesets + change_items, transactional apply
Git status/diff/commit Working tree, explicit file list, local commit Draft changesets, version diff, publish/apply changeset
MCP 17 tools, 9 resources, stdio/SSE, static environment Same domain tools over HTTP with per-user keys
UI Four tabs, metrics, apply/commit forms Same views plus history/diff, auth gate, MCP keys, i18n
Health {"status": "ok", "repo_root": ...} Handbook contract: no auth, cheap, status plus optional checks.db
Deployment systemd user units, nginx, scripts Postgres service, migrations, systemd, nginx
Tests 38 tests, real-repo fixtures Parameterized against DB fixtures; keep behavior contracts

Deliberately absent today and still absent unless separately requested: document delete/rename, full-file inventory replacement, reject/approval workflow, generated inventory index, visual topology, embedding search.

Handbook compliance gaps

Area Handbook requirement Current state Action
Auth gnexus-auth SSO, mirrored sub None Add PKCE client, session, webhooks
MCP transport Streamable HTTP /mcp-protocol/ stdio/SSE Add HTTP endpoint; keep stdio for dev/tests only
MCP keys Per-user mcp_*, hash + hint, revoke None Add mcp_tokens, guard, UI page
Key provisioning /internal/keys, service secret, allowlist None Add later, before external consumers
Actor is_user / X-Actor, audit fields None Add to write paths and audit
UI Kit, auth gate, drawer nav, GnPageHeader Kit only Add gate, nav, history views
i18n en/uk/ru, account locale English only Add i18n module and dictionaries
Health No auth, cheap, status contract Exposes repo path Remove topology, add DB check
Backups Separate host, verified restore Same disk, verified once Add cron dump, off-host copy, restore drill
Secrets gnexus-creds, .env, references only Scanner in validation Keep scanner; align docs
Monitoring hard-panel + Synapse alerts None Add when deployed
Notifications Synapse envelopes None Add only if the service emits alerts
Docs Service page in gnexus-book Page exists Keep current
Git commits No co-authorship lines No agent commits yet Follow 10-platform/git.md

API and MCP compatibility

Read contracts can be kept almost unchanged. Write contracts need an explicit decision because the working-tree model disappears:

  • propose_* → create a draft changeset/change item;
  • apply_pending_change → apply the changeset transactionally;
  • git_status → list draft changesets and pending items;
  • git_diff → diff a draft against the current version;
  • commit_changes → publish/apply a changeset (or become a deprecated alias).

The recommendation is to introduce the new names and keep the old tools as aliases for one transition period, so existing agents do not break. The alternative is a clean rename with updated MCP instructions and tests.

Migration plan

Phases, acceptance criteria, dependencies, and decision gates are in ../00-overview/postgres-migration-roadmap.md.

  1. Accept this ADR and resolve the open questions below.
  2. Add PostgreSQL, migrations, and a shared service layer; keep the file repositories as a development/test backend behind the same interfaces.
  3. Implement versioned documents and inventory, changesets, validation, audit.
  4. Implement SSO, user mirroring, MCP keys, provisioning, and actor tracking.
  5. Update the UI: auth gate, navigation, history/diff, MCP keys page, i18n.
  6. Write the importer: current Markdown/YAML → documents/inventory; optionally replay Git history as changesets.
  7. Dual-run and compare read results; fix discrepancies.
  8. Cut over writes, freeze the file write path, document backups and restore.
  9. Decommission the Git write path and archive the original repository.

Risks

  • The database becomes a single point of failure; dumps without off-host copies and restore drills are not sufficient.
  • Versioning scope can creep into a VCS rewrite; branches and merges stay out of v1.
  • Migration can silently change data (YAML formatting, frontmatter types, references).
  • Auth and MCP key handling add security-critical code that must not be improvised.
  • Losing filesystem access removes grep, IDE search, and ad-hoc scripting workflows.

Open questions

  1. Import the 34 Git commits as history, or import only the current state?
  2. Keep old MCP tool names as aliases, or rename and update consumers?
  3. May agents publish changesets directly, or do access/security/backup changes require owner review?
  4. Document identity: keep mutable path as the public key, or switch to UUID + slug with redirects?
  5. What should "raw inventory" return once comments and formatting no longer exist?
  6. Will the service stay LAN-only, or get a *.gnexus.space subdomain and public MCP endpoint?
  7. Are delete and rename in scope for the first database version?

References