Implement the following plan:

Plan: Database Sync and Backup Scripts

Context

The project has no way to pull production data into the local development database, and no database backup mechanism beyond K8s persistent volume replication. We need:

1. An on-demand script to sync production data into the local Docker postgres
2. A manual backup script that pushes pg_dump output to Box via rclone

New Files

Task 1: scripts/db-sync.ts

Command: bun run db:sync (defaults to prod), bun run db:sync dev

Approach: Plain SQL dump piped from kubectl exec to local Docker psql. Skip embedding tables (documents, chunks) by default since they can be regenerated with bun run embed.

Flow:

1. Parse arg (prod or dev, default prod)
2. Safety: block if NODE_ENV=production
3. Verify local Docker postgres container is running (same pattern as packages/db/scripts/reset.ts:15)
4. Confirmation prompt using ask() pattern from scripts/deploy.ts:43
5. Drop and recreate local schema + vector extension (same as packages/db/scripts/reset.ts:25-34)
6. Run kubectl exec postgres-0 -- pg_dump with --no-owner --no-privileges and --exclude-table=documents --exclude-table=chunks, pipe stdout to docker exec psql -U postgres -d usingandunderstanding
7. Verify: query pg_tables to show restored tables
8. Print reminder about bun run embed to regenerate embeddings if needed

Key patterns to reuse:

• NAMESPACE from scripts/lib/k8s.ts:5
• dbName() from scripts/lib/k8s.ts:19
• Container detection from packages/db/scripts/reset.ts:15
• Schema reset from packages/db/scripts/reset.ts:25-34
• step() and ask() helpers from scripts/deploy.ts:23,43

Task 2: scripts/db-backup.ts

Command: bun run db:backup (backs up both), bun run db:backup prod, bun run db:backup dev

Approach: Pipeline kubectl exec pg_dump | gzip | rclone rcat — streams directly to Box with no local temp file.

Flow:

1. Parse arg (prod, dev, or all, default all)
2. Verify rclone is installed and Box remote is configured (rclone about box: --json)
3. For each environment to backup:
   • Generate filename: {env}-{YYYY-MM-DD}-{HHMMSS}.sql.gz
   • Remote path: box:usingandunderstanding-backups/{YYYY-MM-DD}/{filename}
   • Spawn pipeline: kubectl exec pg_dump | gzip | rclone rcat {remotePath}
   • Check all exit codes, report success/failure
4. Print summary with remote paths

Key patterns to reuse:

• Same NAMESPACE, dbName() from scripts/lib/k8s.ts
• step() helper from scripts/deploy.ts

Package.json Changes

Add to root package.json scripts (after db:studio):

"db:sync": "bun run scripts/db-sync.ts",
"db:backup": "bun run scripts/db-backup.ts"

CLAUDE.md Updates

Add db:sync and db:backup to the Database section documentation.

Verification

1. db:sync: Run bun run db:sync and confirm local database has production tables (check with bun run db:studio)
2. db:backup: Run bun run db:backup prod and verify the file appears in Box under usingandunderstanding-backups/ folder
3. Confirm bun run db:sync dev also works for syncing from the dev database

If you need specific details from before exiting plan mode (like exact code snippets, error messages, or content you generated), read the full transcript at: ~/.claude/projects/-Users-challen-www-usingandunderstanding-ai/c6b53239-d4ad-4dc5-8e2a-09374840c3f6.jsonl

Cool! Can we add a package.json script to run the script, maybe bun db:sync?

I ran db:sync, but after embed does not work: [07:50:09 challen@blueshift:~/www/usingandunderstanding.ai] $ bun embed $ bun run --cwd tools/embedding src/embed.ts Discovering MDX files... Found 18 MDX file(s) 36 | async queryWithCache(queryString, params, query) { 37 | if (this.cache === void 0 || is(this.cache, NoopCache) || this.queryMetadata === void 0) { 38 | try { 39 | return await query(); 40 | } catch (e) { 41 | throw new DrizzleQueryError(queryString, params, e); ^ error: Failed query: select "id", "title", "source", "source_url", "type", "content", "metadata", "uploaded_by", "created_at", "updated_at" from "documents" where "documents"."source" = $1 params: mdx query: "select "id", "title", "source", "source_url", "type", "content", "metadata", "uploaded_by", "created_at", "updated_at" from "documents" where "documents"."source" = $1", params: [ "mdx" ],

Nice! That fix works.

Great! And it looks like the backup also works. However, I do want this to run automatically in both the dev and prod environments, meaning that we probably do need to add a cron task to one of the containers, unless we want to do this with k8s directly. However, rclone requires a token when we'll need to add to the container.

Note that, I'm fairly sure that rclone needs to pull a refresh token as well to continue to work unattended over time. If the backup fails due to rclone issues, we should probably send an email, probably to [email], although it's annoying that that's hardcoded. Can our mail system support a [email] alias (and [email]) and point that to [email]?