Skip to main content

Database

Connecting to the Database​

The database is not exposed publicly — connect via the API container or via SSH tunnel.

Via API container (for quick queries)​

# SSH into the server
ssh hermes  # or atlas for staging

# Find the Postgres container
sudo docker ps | grep postgres

# Connect to postgres
sudo docker exec -it <postgres_container> psql -U postgres -d pcmr_production

Via SSH tunnel (for GUI tools like DataGrip, TablePlus)​

ssh -L 5432:localhost:5432 hermes -N
# Then connect your GUI tool to localhost:5432

Databases:

  • Production: pcmr_production on Hermes
  • Staging: pcmr_staging on Atlas

Checking Migration Status​

sudo docker exec -it <api_container> sh -c \
  "node /app/node_modules/.pnpm/prisma@*/node_modules/prisma/build/index.js \
  migrate status \
  --config /app/prisma/prisma.config.deploy.js"

This shows which migrations have been applied and which are pending.

Running Migrations Manually​

Migrations normally run automatically via start.sh on container startup. If you need to run them manually:

sudo docker exec -it <api_container> sh -c \
  "node /app/node_modules/.pnpm/prisma@*/node_modules/prisma/build/index.js \
  migrate deploy \
  --config /app/prisma/prisma.config.deploy.js"

migrate deploy is idempotent — it's safe to run multiple times.

Setting Up a Fresh Database​

For new environments (fresh PostgreSQL instance):

# 1. Create the database
psql -U postgres -c "CREATE DATABASE pcmr_staging;"

# 2. Connect to the API container and mark migrations 1-8 as applied
sudo docker exec -it <api_container> sh

# Inside container — repeat for each migration name (check prisma/migrations/ for exact names)
node .../prisma/build/index.js migrate resolve \
  --applied "20231001000000_init" \
  --config /app/prisma/prisma.config.deploy.js

# ... repeat for migrations 2-8

# 3. Run migrate deploy — only migration 9 executes
node .../prisma/build/index.js migrate deploy \
  --config /app/prisma/prisma.config.deploy.js

Why: Migration 9 is a full baseline schema. Migrations 1–8 are superseded and should not be run on a fresh database (they may create tables that migration 9 drops/recreates).

See CI/CD — Baseline Migration Strategy for full context.

Restoring from Backup​

Daily backups are stored on Iris at /var/backups/pcmr/ (30-day retention).

# On Iris — find the backup
ls -la /var/backups/pcmr/

# Copy backup to Hermes
scp iris:/var/backups/pcmr/pcmr_production_2026-04-25.dump hermes:/tmp/

# On Hermes — restore
sudo docker exec -i <postgres_container> \
  pg_restore -U postgres -d pcmr_production --clean /tmp/pcmr_production_2026-04-25.dump

Warning: Restoring overwrites all current data. Always confirm this is what you want. Consider restoring to a temporary database first to verify the backup is good.

Backing Up Manually​

The daily cron on Hermes runs automatically, but you can trigger a manual backup:

# On Hermes
sudo docker exec <postgres_container> \
  pg_dump -U postgres -Fc pcmr_production \
  > /tmp/pcmr_manual_$(date +%Y%m%d_%H%M%S).dump

# Copy to Iris
scp /tmp/pcmr_manual_*.dump iris:/var/backups/pcmr/

Common Database Operations​

Find an order by claim code​

SELECT id, "orderStatus", "claimCode", "userId"
FROM "Order"
WHERE "claimCode" = 'NNN-NNNN-NNN';

View status history for an order​

SELECT "statusField", "fromValue", "toValue", note, "createdAt"
FROM "OrderStatusHistory"
WHERE "orderId" = '<order_id>'
ORDER BY "createdAt";

Check active sessions for a user​

SELECT id, "expiresAt", "ipAddress"
FROM "Session"
WHERE "userId" = '<user_id>'
AND "expiresAt" > NOW();

Revoke all sessions for a user (emergency)​

DELETE FROM "Session"
WHERE "userId" = '<user_id>';

Check pending Viva payments​

SELECT id, "vivaOrderCode", "paymentStatus", total, "updatedAt"
FROM "Order"
WHERE "paymentStatus" = 'awaiting_payment'
ORDER BY "updatedAt" DESC;

Schema Reference​

The Prisma schema is at apps/api/prisma/schema.prisma. 29 models, 16 enums. Key models:

  • User, Session, Account, Verification, TwoFactor — auth (Better Auth managed)
  • Order, OrderStatusHistory + 7 order item tables
  • Build, BuildTimelineStep, BuildEventLog, TestRun
  • Component, OperatingSystem, Service, Warranty, Shipping, Peripheral, Software — catalog
  • Attachment — polymorphic file references
  • ComponentPriceHistory
  • WaitlistEntry, CookieConsent