pal-e-docs

Plan: Shared Postgres (CloudNativePG)

plan-2026-02-26-tf-modularize-postgres Plan

plan completed
Project

Vision

Transform pal-e-docs from a note storage app into an AI-native knowledge engine.
Act 1 — Enterprise Postgres (Phases 1-4, COMPLETED): Migrate from SQLite to CloudNativePG on k3s. Platform provides the operator and shared infra. Apps own their database lifecycle — Cluster CRDs, credentials, backups, and migrations live in app repos, deployed by ArgoCD. This eliminated production outages from SQLite's DDL handling and laid the foundation for everything that follows.
Act 2 — Knowledge Engine (Phases 5-8, ACTIVE): Today, AI agents interact with platform knowledge by brute force — enumerate notes, fetch full HTML blobs, reason over thousands of tokens to find one answer. That's unsustainable. Act 2 builds the intelligence layer: full-text search so agents can find knowledge instead of enumerating it, semantic search so they can find related knowledge even without exact terms, structured content so knowledge is queryable at the block level, and optimized MCP tools that exploit all of it. The end state: any agent can ask a question and get a precise, ranked answer with context — without reading every document in the system.

Why Now

Act 1 trigger: Two production outages from SQLite's auto-committed DDL crashing Alembic migrations. Sprint tables (plan-2026-03-01-pal-e-sprints) added another migration. Postgres eliminated the root cause.
Act 2 trigger: Act 1's completion enables Act 2. With Postgres live, we have access to tsvector, pgvector, GIN indexes, triggers, and the full SQL query engine. The real problem is now exposed: 246 notes stored as HTML blobs with no search capability. Every knowledge lookup burns 12+ MCP calls and thousands of tokens. Session context injections alone consume a significant fraction of the context window. As the knowledge base grows, this gets worse — linearly more tokens per query, linearly more calls per search. Search-first architecture inverts that: one query, ranked results, snippets. Estimated 80-90% token reduction per knowledge interaction.

Architecture Revision (2026-03-02)

Original plan put everything in pal-e-platform/main.tf — operator, Cluster CRD, secrets, backups. This caused:
  • Terraform + CRD friction — kubernetes_manifest provider can't handle CNPG webhook mutations (32 injected params, broke on every apply)
  • Wrong ownership — platform repo owned app-level concerns (database config, credentials, backup schedules)
  • Cross-repo migrations — Alembic lives in pal-e-docs but database definition lived in pal-e-platform
New pattern: platform provides capability, apps consume it.
Layer Repo Deployed by Resources
Platform pal-e-platform Terraform CNPG operator (Helm), postgres namespace, MinIO bucket + IAM, S3 creds secret
App pal-e-docs + deployments ArgoCD Cluster CRD, SOPS-encrypted secrets, ScheduledBackup, Alembic migrations

Decisions Made

Decision Rationale
CloudNativePG operator CNCF project. K8s-native CRDs, automated failover, built-in WAL archiving, PgBouncer integration.
Per-app Cluster CRDs, platform provides operator Each app defines its own CNPG Cluster in its repo. Platform installs operator + shared infra.
ArgoCD deploys app-level CRDs ArgoCD tolerates webhook mutations naturally. No Terraform CRD friction.
SOPS + Age for CNPG secrets Encrypted in Git, decrypted at deploy by ArgoCD. Reproducible. See <code>sop-secrets-management</code>.
WAL archiving to MinIO Continuous backup with point-in-time recovery.
Short maintenance window for SQLite cutover Sessions fail-open. One-time migration, dual-write not worth the complexity.
Qwen3-Embedding-4B via Ollama GPU-accelerated on GTX 1070. Near-8B quality, instruction-aware, 768 dims. See <code>decision-phase6-vector-search-architecture</code>.
Per-block embedding (not per-note) Section-level semantic search. Requires Phase 7 blocks before Phase 6 vectors.
SDK-first MCP architecture API → SDK → MCP. Integration tests at SDK layer. Proven with woodpecker-sdk, forgejo-sdk. Phase 8 pulled forward — doesn't need Phase 6.

Projects & Repos Touched

Project/Repo Platform Role
pal-e-platform Forgejo CNPG operator Helm release + shared infra only. Ollama deployment (Phase 6).
pal-e-docs (app) Forgejo Cluster CRD, secrets, backup, SQLAlchemy + Alembic, search endpoints, blocks API
pal-e-docs-sdk Forgejo Typed Python client for pal-e-docs API. Published to Forgejo PyPI v0.2.0. Phase 8.
pal-e-docs-mcp Forgejo MCP tools — rewritten in Phase 8f to wrap SDK instead of raw httpx. v0.2.0 live.
claude-custom Forgejo Claude Code config — hooks, agent personalities, session injection. Phase 7e-3.
forgejo_admin/deployments Forgejo Kustomize overlay with SOPS-encrypted CNPG secrets

# Phase Status Slug
{"html" => "<strong>Act 1 — Enterprise Postgres</strong>", "colspan" => 4}
1 TF Modularization DEFERRED <code>phase-postgres-1-tf-modularize</code>
2 Platform CNPG Foundation COMPLETED <code>phase-postgres-2-deploy-cnpg</code>
2b Clean Up Platform TF COMPLETED <code>phase-postgres-2b-cleanup-platform</code>
3 pal-e-docs Owns Its Postgres COMPLETED <code>phase-postgres-3-migrate-pal-e-docs</code>
4 Backup Verification + Restore SOP COMPLETED <code>phase-postgres-4-backup-restore</code>
{"html" => "<strong>Act 2 — Knowledge Engine</strong>", "colspan" => 4}
5 Full-Text Search (tsvector) COMPLETED <code>phase-postgres-5-fulltext-search</code>
7 Block-Structured Content Model COMPLETED (7a-7d) <code>phase-postgres-7-block-content</code>
8 SDK + MCP Rewrite + Integration Tests COMPLETED (8a-8g) <code>phase-postgres-8-mcp-optimization</code>
7e Compiled Page Architecture COMPLETED <code>phase-postgres-7e-compiled-pages</code>
6 Vector Search (pgvector) COMPLETED (6a-6e) <code>phase-postgres-6-vector-search</code>
7f Doc Cleanup + SOP Hardening COMPLETED <code>phase-postgres-7f-doc-cleanup-sop</code>
{"html" => "<strong>Epilogue</strong>", "colspan" => 4}
E Post-Plan Cleanup IN PROGRESS (items 1,2,3,6,9,11 resolved) <code>phase-postgres-epilogue-cleanup</code>

Dependency Chain




          
  
Lessons Learned



          
  
    
      
  • kubernetes_manifest + CNPG webhook = broken. Webhook injects 32 default params, provider errors on every apply.
    • 
      
  • Dev agents must run tofu plan for TF changes — format/validate is not enough.
    • 
      
  • CRD resources belong with the apps that consume them, not in platform Terraform.
    • 
      
  • kubectl port-forward unreliable on k3s (CNI drops connections). Use kubectl cp + kubectl exec for DB operations.
    • 
      
  • ArgoCD Image Updater creates ghost overrides (.argocd-source-*.yaml) that silently pin image tags. Add to .gitignore. See concept-argocd-ghost-override.
    • 
      
  • Squash merge SHA ≠ branch SHA. Woodpecker ${CI_COMMIT_SHA} is the merge commit on main. See incident-phase5-deployment-outage-2026-03-06.
    • 
      
  • Pod env var is PALDOCS_DATABASE_URL, not DATABASE_URL. Backfill script needs: sh -c 'DATABASE_URL="$PALDOCS_DATABASE_URL" python /tmp/backfill_blocks.py'
    • 
      
  • Always run ruff format --check after manual nit fixes — CI catches it but by then the deploy is blocked.
    • 
      
  • Claude Code hook permissionDecision valid values: allow, deny, ask. Invalid values cause silent fail-open. See bug-merge-hook-silent-error.
    • 
      
  • Convention shifts need more than code — 7e-3 became 4 deliverables (convention note, agent personalities, SOP, hook). Patterns must be encoded everywhere agents get instructions.
    • 
      
  • Merged ≠ deployed ≠ data consistent — 7e-1 fixed future writes but 58 gap notes had no blocks. Always verify data consistency after schema changes.
    • 
      
  • Plan broadly, execute narrowly — 7f-4 session compressed 3 planned subphases (7f-4, 7f-5, 7f-6) into one through aggressive parallelization. Plan structure defines scope; execution finds natural parallelism.
    • 
      
  • k3s nvidia runtime is NOT default — pods must set runtimeClassName: "nvidia" explicitly. Without it, NVML can't discover the GPU and device plugin reports 0 capacity. (Phase 6a, PR #27)
    • 
      
  • Forgejo auto-closes issues when PR body contains Closes #N — enabled by default since Gitea. 19 stale issues accumulated because Dev agents never used this keyword. Enforce via check-pr-template.sh hook. (Phase 6c-1)
    • 



          
  
Concept Notes



          
  
    
      
  • concept-phase5-database-side-intelligence — why we put intelligence in Postgres, not application code
    • 
      
  • concept-phase5-self-hosted-rag — how Act 2 builds a self-hosted RAG system
    • 
      
  • benchmark-phase5-knowledge-baseline — baseline measurements before search (11 calls / ~44K chars / ~11K tokens for 5 queries)
    • 
      
  • concept-argocd-ghost-override — ArgoCD Image Updater ghost override pattern
    • 
      
  • incident-phase5-deployment-outage-2026-03-06 — deployment outage root cause + resolution
    • 
      
  • decision-phase6-vector-search-architecture — embedding model research, hardware analysis, Phase 6 architectural decisions
    • 
      
  • qa-phase7c-backfill-2026-03-07 — QA report for Phase 7c backfill run
    • 
      
  • convention-block-first-access — block-first convention for agent knowledge access (91.1% token reduction)
    • 



          
  


          
  
    
      
  • sop-secrets-management — secrets strategy for Phase 3 CNPG credentials
    • 
      
  • todo-pal-e-docs-deployment-reliability — the incident analysis that triggered this
    • 
      
  • plan-2026-03-01-pal-e-sprints — sprint tables need Postgres for safe migrations
    • 
      
  • service-onboarding-sop — update when services start consuming shared Postgres
    • 
      
  • phase-postgres-4a-barman-plugin-migration — Barman Cloud Plugin migration (before CNPG 1.29 upgrade)
    • 
      
  • plan-2026-02-28-woodpecker-sdk-mcp — the SDK→MCP pattern Phase 8 follows
    •