--- title: "DD-0017: Shared Themelio Database with Decoupled Schema Migrations — Implementation" --- # DD-0017: Shared Themelio Database with Decoupled Schema Migrations — Implementation **Related ADR:** [ADR-0017: Shared Themelio Database with Decoupled Schema Migrations](/adr/0017-shared-themelio-database-decoupled-migrations) **Status:** Draft **Date:** 2026-06-08 **Authors:** TBD ## Overview This document specifies how the `themelio` database schema is applied. The schema itself is defined in [DD-0012](/dd/0012-job-deployment-management-system); DD-0012 is silent on *how* migrations run, and this DD fills that gap. Migrations run once per deploy via an **ArgoCD PreSync hook Job**. As a fast temporary solution the Job **reuses the existing `k8s-root-actor` image** and invokes only its migrate entrypoint, while the long-running `k8s-root-actor` Deployment runs the same image and never migrates on boot. Exactly one migrator runs per deploy, and it clears any stale Knex lock on start so an interrupted migration self-heals. This is a stepping stone toward the ADR-0017 end goal of a dedicated migration component with its own image. ## Goals and Non-Goals ### Goals - Migrations run **exactly once per deploy** and complete successfully **before** the new application ReplicaSet rolls. - The long-running serving process **never** runs migrations or DDL. - A migrator interrupted mid-run (OOM, node death, SIGKILL) recovers **without manual intervention** on the next run. - **No new image or build artifact** — reuse the `k8s-root-actor` image (fast temporary solution). - Works uniformly across the `local`, `dev`, `staging`, and `prod` overlays. - The `themelio` database stays reachable by multiple services over `DATABASE_URL`. ### Non-Goals - **Defining the schema** — [DD-0012](/dd/0012-job-deployment-management-system) owns the table definitions; this DD does not restate them. - **Extracting migrations into a dedicated component/image** — that is the ADR-0017 end goal and is deferred (tracked in Open Questions). - **Data backfill** — the `themelio` database is new and empty; there is nothing to migrate. - **Changing the connection topology** — services continue to connect to the shared CNPG cluster via `DATABASE_URL`. ## Design ### 1. Migrate entrypoint in `k8s-root-actor` Add a dedicated entrypoint at `services/k8s-root-actor/src/db/migrate.ts`, compiling to `dist/db/migrate.js`. It reuses the same Knex `db` singleton and config as the service, so there is no duplicated connection setup: ```ts import { db } from './knex'; async function main() { // Clear a lock left stuck by a previous Job that died mid-migration. Safe // here because PreSync + hook-delete-policy: BeforeHookCreation + restartPolicy: // Never guarantee this is the ONLY migrator running. A no-op on a fresh DB. await db.migrate.forceFreeMigrationsLock(); const [batch, log] = await db.migrate.latest(); console.log(`migrations applied: batch ${batch}`, log); } main() .then(async () => { await db.destroy(); process.exit(0); }) .catch(async (err) => { console.error('migration failed', err); await db.destroy(); process.exit(1); }); ``` The service boot path (`start()` in `src/index.ts`) does **not** call `db.migrate.latest()`. It validates `DATABASE_URL` inside `start()` (so a misconfiguration surfaces through the graceful `process.exit(1)` path) and registers a Fastify `onClose` hook that calls `db.destroy()`. ### 2. One image, two entrypoints The `k8s-root-actor` image already contains everything the migrator needs: - The Deployment runs `pnpm start` → `node dist/index.js` (the service). - The PreSync Job runs `node dist/db/migrate.js` (migrations only). The build script already ships migrations into the image: ``` "build": "tsc && tsc-alias --resolve-full-paths && mkdir -p dist/db/migrations && cp -r src/db/migrations/. dist/db/migrations/" ``` `migrate.js` lands in `dist/db/` from the normal `tsc` output, so no build change is needed. This mirrors the existing precedent at `gitops/manifests/waldur-site-agent-cloud/base/job-load-components.yaml`, where a Job reuses a service image with a different `command`. ### 3. PreSync Job manifest Add `gitops/manifests/themelio/apps/k8s-root-actor/job-migrate.yaml`: ```yaml apiVersion: batch/v1 kind: Job metadata: name: k8s-root-actor-migrate namespace: themelio annotations: argocd.argoproj.io/hook: PreSync # Jobs are immutable; replace the prior Job each sync, leaving a failed one # in place for inspection until the next sync. argocd.argoproj.io/hook-delete-policy: BeforeHookCreation spec: backoffLimit: 1 activeDeadlineSeconds: 300 template: spec: restartPolicy: Never serviceAccountName: k8s-root-actor-sa imagePullSecrets: - name: regcred containers: - name: migrate # Image is pinned per-overlay via the kustomize images: transformer, # so the Job and the Deployment always run the same image. image: k8s-root-actor command: ["node", "dist/db/migrate.js"] env: # DB_USERNAME/DB_PASSWORD from the themelio-db secret + non-secret # DATABASE_ENDPOINT — same block as the deployment. The app composes # the connection URL from these, so the password is never rendered # into the manifest or the ArgoCD-resolved env. - name: DB_USERNAME valueFrom: secretKeyRef: name: themelio-db key: db_username - name: DB_PASSWORD valueFrom: secretKeyRef: name: themelio-db key: db_password - name: DATABASE_ENDPOINT value: pg-rw.databases.svc.cluster.local:5432/themelio ``` The `DB_USERNAME`/`DB_PASSWORD`/`DATABASE_ENDPOINT` env block is identical to the Deployment's; factor it (e.g. a shared snippet or kustomize component) so the two cannot drift. Keeping the password as a discrete `secretKeyRef` (rather than interpolating it into a `DATABASE_URL` value) means the credential never appears in the rendered manifest; `src/config.ts` assembles the URL at runtime. ### 4. Kustomize wiring - **Register the Job in the base** so every overlay inherits it. In `gitops/manifests/themelio/apps/k8s-root-actor/kustomization.yaml`: ```yaml resources: - deployment.yaml - service.yaml - job-migrate.yaml ``` - **Pin the image once per overlay.** Today each overlay patches the image inside its own `k8s-root-actor/deployment.yaml`. Switch to a kustomize `images:` transformer so a single per-env entry pins **both** the Deployment and the Job: ```yaml # overlays/dev/kustomization.yaml images: - name: k8s-root-actor newName: dev.registry.pharos.grnet.gr/test/k8s-root-actor newTag: dev ``` Staging/prod use their respective registries (`staging.registry.pharos.grnet.gr/themelio/...`, `registry.pharos.grnet.gr/themelio/...`). ### 5. Local overlay (source-mode) The `local` overlay runs the service from source off a `/code` hostPath rather than a built image, so the migrate Job cannot use `node dist/db/migrate.js`. Add a local patch for `k8s-root-actor-migrate` that runs the equivalent off the hostPath: ```yaml spec: template: spec: containers: - name: migrate image: k8s-root-actor:local imagePullPolicy: Never command: - "sh" - "-c" - "cd /srv/app && pnpm install --filter=k8s-root-actor --frozen-lockfile && cd services/k8s-root-actor && tsx src/db/migrate.ts" # DB_USERNAME/DB_PASSWORD/DATABASE_ENDPOINT env as in base volumeMounts: - name: workspace mountPath: /srv/app volumes: - name: workspace hostPath: path: /code type: Directory ``` Running `tsx src/db/migrate.ts` (rather than `npx knex migrate:latest --knexfile src/db/knexfile.cjs`) keeps the force-free self-healing behavior locally. The dependency-free `knexfile.cjs` remains available for ad-hoc CLI runs. ### 6. Stuck-lock self-healing The entrypoint force-frees the Knex lock on **start**, not on failure: a Job killed mid-migration never runs a `catch`/cleanup handler, so clearing the lock at the start of every run is the only approach that heals all death modes on the next run. `forceFreeMigrationsLock()` is a no-op against a fresh database (it checks the lock table exists first); the entrypoint may read `is_locked` beforehand to log a warning when a stale lock was actually cleared. This is safe because exactly one migrator ever runs — guaranteed by PreSync ordering, `hook-delete-policy: BeforeHookCreation`, `restartPolicy: Never`, and Argo's serial, wait-for-completion hook execution. The stuck window is bounded by `activeDeadlineSeconds` (~300) and `backoffLimit`, so a hung migration fails fast and the next run clears the lock. ### 7. Shared database access Services connect at runtime to `pg-rw.databases.svc.cluster.local:5432/themelio` (owner `themelio`), authenticating with the `db_username`/`db_password` keys from the `themelio-db` secret (an `Opaque` secret using namespaced keys). Each service composes its own `DATABASE_URL` in code from the discrete `DB_USERNAME`/`DB_PASSWORD`/`DATABASE_ENDPOINT` env vars so the password is never written into a manifest. Because the schema is applied out-of-band by the PreSync Job rather than by a service on boot, future services (for example `web` reading job/deployment data) can connect to the same database directly without proxying through `k8s-root-actor`. ## Data Model The schema is defined in [DD-0012](/dd/0012-job-deployment-management-system) — tables `jobs`, `deployments`, `job_logs`, `deployment_logs`, their status enums, and indexes. This DD does not restate them. The initial Knex migration creates those four tables; Knex additionally maintains its own `knex_migrations` and `knex_migrations_lock` bookkeeping tables in the same database. ## Deployment & Rollout 1. The Ansible `themelio` role provisions the `themelio` database (per [ADR-0016](/adr/0016-declarative-database-list-in-postgres-role)) and the `themelio-db` secret, **before** the Argo sync. 2. An ArgoCD sync fires. The PreSync hook runs `k8s-root-actor-migrate`, which applies pending migrations. 3. On **success**, the new application ReplicaSet rolls and begins serving against the current schema. 4. On **failure**, ArgoCD marks the sync failed and does **not** promote the new application version; the previous version keeps serving. The failed Job is left in place for inspection until the next sync. The image is pinned per environment via the `images:` transformer (§4). No data migration is involved — the database is new and empty. ## Testing Strategy - **Unit:** the migrate entrypoint exits `0` on success and `1` on failure; `forceFreeMigrationsLock()` is a no-op against a fresh database. - **Integration:** run the entrypoint against an ephemeral Postgres and assert the four DD-0012 tables exist; a second run is idempotent (no-op); a simulated mid-run kill leaves a lock that the next run clears and completes. - **Manual:** `kubectl logs job/k8s-root-actor-migrate -n themelio`; confirm the PreSync hook status in the Argo UI; run `make local-up` and confirm the schema is applied in source-mode. ## Open Questions 1. **When do we extract the migration/schema code into a dedicated component with its own image** (the ADR-0017 end goal)? The same-image approach here is the fast temporary solution; extraction is the trigger to revisit this DD. 2. ~~**Should the Job use a dedicated least-privilege ServiceAccount** instead of reusing `k8s-root-actor-sa`? The migrator needs only database access, not the cluster RBAC `k8s-root-actor-sa` carries.~~ **Resolved:** The Job reuses `k8s-root-actor-sa` for now. A dedicated least-privilege ServiceAccount can be introduced later (a natural companion to the dedicated-component extraction in Q1). ## References - [DD-0012: Job and Deployment Management System](/dd/0012-job-deployment-management-system) — the schema this migration applies. - [ADR-0017: Shared Themelio Database with Decoupled Schema Migrations](/adr/0017-shared-themelio-database-decoupled-migrations) - Existing PreSync-style Job precedent: `gitops/manifests/waldur-site-agent-cloud/base/job-load-components.yaml` - [Knex migrations and the migration lock](https://knexjs.org/guide/migrations.html) - [ArgoCD Resource Hooks (PreSync)](https://argo-cd.readthedocs.io/en/stable/user-guide/resource_hooks/)