owner: gmikcon status: draft last_reviewed: 2026-11-19 review_interval: 30d confidence: medium
Execution plan for ADR-0002: move Gnexus Book from file + Git storage to PostgreSQL with built-in versioning, handbook-compliant authentication and MCP, and a later one-time migration of the existing knowledge base.
Platform rules are not repeated here. Read the gnexus-handbook and the sections linked from the ADR before starting a phase.
The migration is complete when:
Browser (cookie session) AI agent (Bearer mcp_* key)
| |
+----------------+-----------------+
v
FastAPI / ASGI
+----------------+-----------------+
| |
REST /api/* MCP /mcp-protocol/
| |
+------------ service layer --------+
|
repositories / unit of work
|
PostgreSQL
|
+----------------+-----------------+
| | |
pg_dump cron gnexus-auth hard-panel / Synapse
-> separate host (SSO, webhooks) (health, alerts)
Implementation rules:
| Stream | Covers | Main phases |
|---|---|---|
| A. Storage and schema | PostgreSQL, migrations, repositories, JSONB inventory | 1, 3 |
| B. Versioning and change management | Versions, changesets, diff, rollback, audit | 2 |
| C. Validation, search, relationships | Schemas, secret scan, FTS, freshness, graph | 3 |
| D. Identity and access | gnexus-auth SSO, users, roles, actor | 4 |
| E. MCP | Streamable HTTP, keys, provisioning, tool parity | 5 |
| F. UI | Auth gate, navigation, i18n, history/diff, MCP keys | 6 |
| G. Migration and cutover | Importer, dual-run, reconciliation, freeze | 7 |
| H. Operations and documentation | Backups, health, monitoring, docs, decommission | 1, 8 |
Resolve these before the phase that depends on them. On 2026-11-19 the owner instructed implementation to proceed, so the recommended defaults below are adopted as implementation defaults. They can still be overridden before the dependent phase.
| ID | Decision | Recommended default | Needed by |
|---|---|---|---|
| D1 | Import Git history or current state only? | Import the current state as one import changeset; optionally replay Git history later | Phase 7 |
| D2 | Keep old MCP tool names? | New names primary, old names as deprecated aliases for one transition period | Phase 5 |
| D3 | May agents publish without review? | Publish content changes; require owner review for access, security, network exposure, backup, and critical-service changes | Phase 4 |
| D4 | Document identity | UUID primary key + mutable slug with a redirect table; keep path in the API as an alias |
Phase 1 |
| D5 | What is "raw inventory"? | Canonical YAML serialized from JSONB, explicitly marked as generated | Phase 3 |
| D6 | Service exposure | LAN-only first; decide on a *.gnexus.space subdomain and public MCP later |
Phase 6 |
| D7 | Delete/rename scope | Rename with redirects and soft delete in v1; hard delete out of scope | Phase 2 |
Effort is relative: S (days), M (1–2 weeks), L (several weeks). These are planning sizes, not commitments.
Goal: start from a known, recoverable state.
Tasks:
stale-document warnings. Do not fake last_reviewed.Acceptance:
Rollback: none needed; this phase only adds safety.
Goal: serve every read from PostgreSQL while the file backend remains canonical for writes.
Deliverables:
.env configuration; DB credentials from the platform secret process.documents, doc_versions, blobs, inventory_types, schemas, inventory_items, inventory_item_versions, changesets, change_items, validation_runs, audit_log, plus reserved users/actor columns./docs, /docs/read, /search, /inventory, /relationships, /validate, /health/freshness) switchable by configuration.pg_dump job to a separate host, retention policy, and a restore script.Acceptance:
Rollback: change the configuration back to the file backend; the database can be dropped and re-imported.
Goal: replace the document write path and Git commit semantics.
Deliverables:
POST /changes, GET /changes, GET /changes/{id}, POST /changes/{id}/apply; add explicit changeset/publish endpoints.propose_doc_change, apply_pending_change, update_doc keep working; commit_changes maps to publish or becomes a deprecated alias (D2).Acceptance:
Rollback: keep the file write path behind a feature flag until Phase 7.
Goal: reach full functional parity for inventory and derived views.
Deliverables:
schemas.(type, item_id) among live items; immutable item IDs.docs targets.Acceptance:
validate_repository is clean on the imported database.Rollback: database mode remains read-only for inventory writes until parity is signed off.
Goal: make the service genuinely multi-user and handbook-compliant.
Deliverables:
/auth/login, /auth/callback, /auth/logout, GET /me.sub; profile is the source of truth.actor_kind, actor_name, via; X-Actor: user for REST and is_user for MCP write tools.Acceptance:
/health stays public.Rollback: authentication can be disabled in development (auth-off pattern), but the production database must not be exposed without it.
Goal: make agents first-class, per-user consumers of the service.
Deliverables:
/mcp-protocol/ (trailing slash preserved).401, WWW-Authenticate, last_used_at.mcp_tokens storage with hash, hint, consumer, role/scopes snapshot, revoke.user_id, consumer)./internal/keys provisioning endpoints with service-to-service auth, allowlist, rate limiting, and audit; not exposed through nginx.is_user; tool errors returned as data.Acceptance:
401.409./internal is unreachable from outside the allowed network.Rollback: keep the old MCP entry point available read-only during transition; revoke new keys if necessary.
Goal: the UI covers the current functionality and follows the platform shell.
Deliverables:
/me, denied/ready states, and explicit login click.GnPageHeader on every page; kit components only.en, uk, ru, account locale as default, per-service override.Acceptance:
Rollback: the UI can keep using the old endpoints until the new ones are proven; no data is at risk.
Goal: move the existing knowledge base into the new system and make it canonical.
Deliverables:
Acceptance:
Rollback: pre-cutover dump + file backend still present and readable.
Goal: operate the new system sustainably and remove the old path.
Deliverables:
status, checks.db, service/version/timestamp, no topology or personal data.Acceptance:
Rollback: retain the final Git archive and the last pre-decommission dump.
| Milestone | Meaning |
|---|---|
| M0 | Off-host verified backup and decisions D1–D7 recorded |
| M1 | All reads served from PostgreSQL with parity tests |
| M2 | Document writes, history, diff, rollback work in the database |
| M3 | Inventory, relationships, search, validation, freshness at parity |
| M4 | SSO, roles, actor attribution, audit enforced |
| M5 | MCP over streamable HTTP with per-user keys and provisioning |
| M6 | UI parity plus auth, i18n, history/diff, key management |
| M7 | Existing knowledge base migrated; cutover rehearsed and signed off |
| M8 | Operations, documentation, and decommission complete |
/health under 500 ms.| Risk | Impact | Mitigation |
|---|---|---|
| Database loss | Loss of the knowledge base | Off-host dumps from Phase 1, retention, restore drills |
| Versioning scope creep | Delays, bugs | No branches/merges; only versions and changesets |
| Import drift | Silent data corruption | Checksums, dry runs, parity harness, pre-cutover dump |
| Broken agent contracts | Agents fail after release | Keep old MCP names as aliases; contract tests |
| Auth/key implementation bugs | Security incident | Use gnexus-auth client library; follow handbook tests; no fallbacks |
| Loss of filesystem workflows | Harder ad-hoc search/editing | Keep a read-only Git archive; provide search and history in the UI |
| Performance regressions | Slow UI/agents | Indexes from the start; FTS; measure current corpus in Phase 3 |
| Operational overload | Missed backups or alerts | Automate from Phase 1; monitor the backup job itself |
| Date | Item | Status |
|---|---|---|
| 2026-11-19 | Off-host backup of the knowledge base | Copied to ~/NetTera8/service/backups/gnexus-book/2026-11-19_181859; Git bundle, archive, and SHA256 verified on the NFS host |
| 2026-11-19 | Decision gates D1–D7 | Adopted as implementation defaults |
| 2026-11-19 | Phase 1: PostgreSQL foundation | Dev cluster on 127.0.0.1:5433; SQLAlchemy models; Alembic initial migration; database-backed read repositories; importer v0 |
| 2026-11-19 | Phase 1: import and parity | 29 documents, 84 inventory items, 12 inventory types, 13 schemas imported; parity check reports 0 differences |
| 2026-11-19 | Phase 1: backups | scripts/backup-db.sh produced a custom-format dump on the off-host NFS; scripts/restore-db.sh restored it into a temporary database with matching counts |
| 2026-11-19 | Phase 2: versioned writes | Database-backed draft/apply changesets; immutable document and inventory versions; transactional validation with rollback; audit entries |
| 2026-11-19 | Phase 2: history, diff, restore | API and MCP expose document_history, document_version, document_diff, and restore_document_version; restore creates a new version |
| 2026-11-19 | Phase 2: validation | GET /validate and the MCP validation tool validate the database in db mode |
| 2026-11-19 | Phase 2: tests | Full suite: 56 passed, 1 known stale-freshness failure |
| 2026-11-19 | Phase 3: inventory and relationships | Database read/write for inventory, schema validation from stored schemas, unique live item IDs, relationship graph over database records |
| 2026-11-19 | Phase 3: freshness | Database freshness report matches the file backend issue-for-issue |
| 2026-11-19 | Phase 3: search | Substring/line-match path at parity; PostgreSQL FTS index deferred to a performance phase |
| 2026-11-19 | Phase 3: tests | Full suite: 57 passed, 1 known stale-freshness failure |
| 2026-11-19 | Phase 4: auth schema and sessions | users mirror fields, user_sessions, oauth_states, webhook_events; hashed session tokens; 7-day TTL |
| 2026-11-19 | Phase 4: gnexus-auth integration | OAuth 2.0 PKCE login/callback, /me, locale override, logout, HMAC-verified and idempotent webhooks; auth disabled when client credentials are absent |
| 2026-11-19 | Phase 4: route guard and actor | Anonymous requests rejected when auth is enabled; actor recorded on changesets, document versions and inventory versions |
| 2026-11-19 | Phase 4: tests | Full suite: 66 passed, 1 known stale-freshness failure |
| 2026-11-19 | Phase 5: streamable HTTP MCP | Guarded /mcp-protocol/ endpoint via app.mcp_http; stdio remains for tests/local development |
| 2026-11-19 | Phase 5: personal keys | mcp_tokens with hash + hint, one active key per (user_id, consumer), session key API, /internal/keys provisioning, rate limits |
| 2026-11-19 | Phase 5: actor and revocation | Mutating tools accept is_user; REST accepts X-Actor; blocked users lose all keys; role changes mark keys stale |
| 2026-11-19 | Phase 5: tests | Full suite: 75 passed, 1 known stale-freshness failure |
| 2026-11-19 | Phase 6: shell and auth gate | Drawer navigation, GnPageHeader on every page, gnexus-auth gate, settings, logout |
| 2026-11-19 | Phase 6: pages and i18n | Docs search/history/diff/restore, inventory JSON/raw, changesets/apply, status reports, MCP keys page, en/uk/ru |
| 2026-11-19 | Phase 6: verification | Production build plus headless Chromium smoke in file mode, database mode and auth-gate mode; no browser console errors |
| 2026-11-19 | Phase 7: importer v1 | Source revision and content-manifest digest, post-import validation, database manifest verification, drift detection |
| 2026-11-19 | Phase 7: reconciliation | File/DB comparison now covers documents, search, inventory, relationships, validation and freshness |
| 2026-11-19 | Phase 7: cutover tooling | cutover-db.sh, rollback-cutover.sh, 30-runbooks/cutover-postgres.md, systemd EnvironmentFile |
| 2026-11-19 | Phase 7: rehearsal | Fresh gnexus_book_cutover: 30 documents, manifest match, 0 reconciliation differences, off-host dump, API smoke, file rollback verified; rehearsal database dropped |
| 2026-11-19 | Phase 7: tests | Full suite: 78 passed, 1 known stale-freshness failure |
| 2026-11-19 | Local gnexus-auth: client | Owner-provided local client gnexus-book-local, all_active_users, local redirect URIs, editor role assigned to the test user, signed webhook; previous test client disabled; secrets only in server/.env |
| 2026-11-19 | Local gnexus-auth: end-to-end | Real login → authorize → callback → session → /me with roles: ["editor"] and empty client permissions; /mcp-keys 200; logout verified |
| 2026-11-19 | Local gnexus-auth: webhook | A webhook.test delivery from gnexus-auth reached POST /webhooks/gnexus-auth with 200 OK and was recorded in webhook_events |
| 2026-11-19 | Local gnexus-auth: UI | Auth gate rendered in headless Chromium against the live local gnexus-auth |
Notes:
file. Set GNEXUS_BOOK_STORAGE_BACKEND=db to use PostgreSQL for reads and writes.commit_changes is a no-op and POST /commit is blocked.client_id, client_secret, redirect URI) and its webhook secret, all supplied through .env/gnexus-creds.read/write scopes.GNEXUS_BOOK_STORAGE_BACKEND=db and the database URL in server/.env, restart the backend and MCP units, then run the post-cutover checks in cutover-postgres.md. The Git checkout is untouched and remains the rollback source.~/backup/gnexus-book/ and the off-host dump on NetTera8.gnexus-book-local client, including a dedicated test user, the editor role and a real signed webhook delivery. Credentials live only in server/.env and ~/.config/gnexus-book/, never in the repository.all_active_users access mode for testing. Production must use an owner-registered client with HTTPS URLs, a narrower access mode and defined client permissions; role-specific publish policy is still open.