Phase 3: pal-e-docs Owns Its Postgres

phase-postgres-3-migrate-pal-e-docs Phase

Project

Goal: pal-e-docs switches from SQLite to the already-running CNPG Postgres cluster. App code migrates to Postgres dialect, k8s manifests updated, Litestream removed, data migrated.

Owner: Dev agent

Repos: pal-e-docs (primary), pal-e-platform (Terraform — DB secret in app namespace)

Issues:

forgejo_admin/pal-e-platform #22 — Terraform DB secret (Part A)
forgejo_admin/pal-e-docs #76 — Code + manifests + data migration (Parts B/C/D)

Architecture (verified 2026-03-06)

Key facts discovered during pre-issue research:

Component	Current State
CNPG Cluster	<code>pal-e-postgres</code> running in <code>postgres</code> namespace. Healthy. Postgres 17.4, 1 instance, 5Gi storage.
Database	<code>paledocs</code> database, owned by <code>paledocs</code> user. Bootstrapped by CNPG initdb.
Credentials	<code>paledocs-db-credentials</code> secret in <code>postgres</code> namespace (manual kubectl). Contains username + password.
Connection endpoint	<code>pal-e-postgres-rw.postgres.svc.cluster.local:5432</code>
ArgoCD Application	Sources directly from <code>pal-e-docs/k8s/</code> (NOT a deployments overlay). Auto-sync + prune + self-heal.
Current app secrets	<code>pal-e-docs-secrets</code> (manual kubectl), <code>litestream-creds</code> (manual kubectl), <code>harbor-creds</code> (Terraform)
WAL archiving	Configured but failing (<code>ContinuousArchivingFailing</code>). Phase 4 concern, not blocking.
SOPS+Age	Age keypair exists in Salt pillar but is NOT deployed to ArgoCD. No SOPS decryption wired up. Future work.

Secrets Strategy

Pattern: Terraform-managed k8s secret — matches how harbor-creds, cnpg-s3-creds, and other platform secrets are managed today.

Add a kubernetes_secret_v1 resource to pal-e-platform/terraform/main.tf that creates a paledocs-db-url secret in the pal-e-docs namespace containing the full Postgres DSN. The password is sourced from a new tfvar (stored in k3s.tfvars, gitignored, sourced from Salt pillar).

Future: When SOPS+Age is wired into ArgoCD, migrate this secret to SOPS-encrypted YAML in the repo. Document as tech debt.

Connection String

Steps

Part A: Terraform — DB secret in app namespace (pal-e-platform #22)

Add variable "paledocs_db_password" to terraform/variables.tf (sensitive, string)
Add kubernetes_secret_v1.paledocs_db_url to terraform/main.tf — creates secret paledocs-db-url in pal-e-docs namespace with key DATABASE_URL containing the full DSN
Add value to terraform/k3s.tfvars
Run tofu plan + tofu apply

Part B: App code — SQLite → Postgres (pal-e-docs #76, PR 1)

Update src/pal_e_docs/config.py: add database_url: str | None = None setting. When set, takes precedence over database_path.
Update src/pal_e_docs/database.py: use database_url if set, otherwise fall back to SQLite path. Remove SQLite-specific pragma listener when using Postgres.
Update alembic/env.py: use database_url setting when available, fall back to SQLite for local dev.
Add psycopg2-binary to dependencies.
Fix SQLite-isms in migration files: (CURRENT_TIMESTAMP) → sa.func.now(), boolean defaults, CHECK constraints.
Test locally against Postgres.

Part C: k8s manifests — deployment update (pal-e-docs #76, PR 2)

Update k8s/deployment.yaml: replace env var, remove Litestream containers, remove volumes
Update k8s/kustomization.yaml: remove pvc.yaml and litestream-configmap.yaml
Delete k8s/pvc.yaml and k8s/litestream-configmap.yaml

Part D: Data migration (one-time, maintenance window)

Data migration script in scripts/migrate_sqlite_to_postgres.py
Lucas runs Alembic + migration during maintenance window
Deploy PR 2 after verification

Deployment Sequence (CRITICAL)

Merge PR 1 (code) → ArgoCD deploys. App still uses SQLite.
Lucas runs tofu apply on pal-e-platform (creates DB secret)
Lucas runs alembic upgrade head against Postgres
Lucas runs data migration script
Merge PR 2 (manifests) → ArgoCD deploys. App switches to Postgres.

Risks

Data migration window: Short downtime while SQLite data is loaded into Postgres. Sessions fail-open.
Alembic dialect: Existing migrations have SQLite-isms (3 patterns identified). Must be fixed.
ArgoCD prune: Auto-prune will delete PVC when removed from kustomization. Data migration must be verified BEFORE merging PR 2.
Cross-namespace: No NetworkPolicy blocking. Future-proof with egress rule if policies are added.

Depends on

Phase 2b (platform cleanup) — COMPLETED.