---
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](https://git.gnexus.space/root/gnexus-handbook/raw/master/README.md)
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

```text
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](../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

- [gnexus-handbook README](https://git.gnexus.space/root/gnexus-handbook/raw/master/README.md)
- [Auth](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/auth.md)
- [MCP](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/mcp.md)
- [Key provisioning](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/key-provisioning.md)
- [Secrets](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/secrets.md)
- [UI](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/ui.md)
- [i18n](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/i18n.md)
- [Health](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/health.md)
- [Git](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/git.md)
- [Backups](https://git.gnexus.space/root/gnexus-handbook/raw/master/20-deployment/backups.md)
- Current implementation: `server/app/main.py`, `server/app/mcp_server.py`,
  `server/app/pending_changes.py`, `server/app/validation.py`, `server/app/freshness.py`,
  `server/app/relationships.py`, `ui/src/main.js`
