nick-doc/08 - Operations/Database Operations.md

title, tags

title	tags
Database Operations	operations

Database Operations

[!important] MongoDB Removed (2026-06-06 / v2.9.12) — PostgreSQL is the sole database. MongoDB operational procedures below are retained as historical reference.

Day-to-day operations for stateful services: PostgreSQL (sole runtime data store as of v2.9.12), and Redis 8 (cache, rate-limit counters, ephemeral session data).

For schema details see Data Models. For backup procedures and disaster recovery see Backup & Recovery.

---

PostgreSQL Operations

Connection

PG_URL env var is required. MongoDB env vars (MONGO_URI, MONGODB_URI, MONGO_CONNECT_MODE) are obsolete and ignored.

Env	Example DSN
Dev	postgres://amanat:<password>@postgres:5432/amanat_dev
Prod	postgres://amanat:<password>@postgres:5432/amanat

Connect from a shell:

docker exec -it amanat-postgres psql -U amanat -d amanat_dev

Run migrations

cd backend && npx drizzle-kit migrate

19 migrations have landed (0000–0019), covering 32 tables. Application startup does not apply migrations automatically — run them explicitly before starting the backend after a version upgrade.

Schema files

backend/src/db/schema/*.ts

Each file declares one or more Drizzle table definitions. Migrations in backend/drizzle/ are generated from these schema files via npx drizzle-kit generate.

Repositories

backend/src/db/repositories/drizzle/Drizzle*.ts

All domain repositories are Drizzle-backed. The repository factory returns Drizzle repos exclusively; there is no runtime fallback to MongoDB.

Key facts:

• IDs are PostgreSQL UUIDs (.id string field), not MongoDB ObjectIds
• User._id is kept as legacy_object_id column for backwards-compat; marketplace FKs use user.pgId (UUID)
• Chat is stored in the chats table with messages/participants as JSONB arrays
• PaymentDTO.amount is a decimal string
• PurchaseRequest does not have a top-level paymentId field

Docker volume layout

postgres:
  image: postgres:18-alpine
  environment:
    POSTGRES_DB: amanat_dev
    POSTGRES_USER: amanat
    POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
  volumes:
    - /var/data/escrowDev/postgres_data:/var/lib/postgresql

Mount at /var/lib/postgresql (not /var/lib/postgresql/data) — Postgres 18 stores data under a version-specific subdirectory.

For a disposable dev reset:

docker rm -f amanat-postgres 2>/dev/null || true
rm -rf /var/data/escrowDev/postgres_data
mkdir -p /var/data/escrowDev/postgres_data

Backup

Standard PostgreSQL tooling:

docker exec amanat-postgres pg_dump -U amanat -d amanat_dev --format=custom \
  > backups/amanat_dev_pg_$(date +%F).dump

Restore:

docker exec -i amanat-postgres pg_restore -U amanat -d amanat_dev --clean \
  < backups/amanat_dev_pg_2026-06-06.dump

For production use managed backups or WAL archiving/PITR. See Backup & Recovery.

Seeding

Seeds are Postgres-only, store-aware, and idempotent. Run against a running backend container:

docker exec -it nickapp-backend node -e "require('./dist/seeds/seedCategories.js')"
docker exec -it nickapp-backend node -e "require('./dist/seeds/seedLevels.js')"

[!warning] Never run seed:all or seed:users against production. These are destructive.

Common admin queries

-- Row counts
SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

-- Active connections
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Slow queries (requires pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

---

1. MongoDB

[!note] Historical — MongoDB has been removed. The content below is retained as a reference for data archaeology, incident retrospectives, or backfill tooling. Do not use these procedures against the live application.

1.1 Connection

[!note] Historical — MongoDB has been removed.

Env	URI in compose	Auth
Dev	mongodb://mongodb:27017	none
Prod	mongodb://mongodb:27017 (private network) or with creds via .env	typically none on the private network, but enable --auth if exposed

The DB name comes from DB_NAME (e.g. marketplace). See Environment Variables#database.

Connect from a shell inside the host:

# Dev
docker exec -it nickdev-mongodb mongosh

# Prod
docker exec -it nickapp-mongodb mongosh
> use marketplace
> show collections

If auth is enabled:

docker exec -it nickapp-mongodb mongosh \
  -u "$MONGO_INITDB_ROOT_USERNAME" -p "$MONGO_INITDB_ROOT_PASSWORD" \
  --authenticationDatabase admin

1.2 Init scripts (mongo-init/)

[!note] Historical — MongoDB has been removed.

The production compose bind-mounts ./mongo-init into /docker-entrypoint-initdb.d. Mongo runs *.js and *.sh from this folder only on a fresh datadir (first boot of a new volume). Use this to:

• Create application users (db.createUser({...}))
• Bootstrap collections + indexes that must exist before the app starts

Example mongo-init/01-create-user.js:

db = db.getSiblingDB('marketplace');
db.createUser({
  user: 'marketplace_app',
  pwd: process.env.MARKETPLACE_APP_PWD,
  roles: [{ role: 'readWrite', db: 'marketplace' }],
});

[!warning] These scripts do not run when you restart an existing container. To force re-init, drop the mongodb_data volume — which destroys all data. Plan accordingly.

1.3 Indexes

[!note] Historical — MongoDB has been removed. Indexes are now declared in Drizzle schema files under backend/src/db/schema/.

Indexes were declared in Mongoose schemas under backend/src/models/. The app called Model.createIndexes() on connection. Highlights:

Collection	Key indexes
users	email (unique), googleId (sparse), role, createdAt
addresses	userId + compound for primary lookup
purchaserequests	buyerId, status, createdAt, text index on title+description
selleroffers	requestId, sellerId, status
payments	providerPaymentId (unique sparse), userId, status, createdAt, transactionHash
chats	participants (array), updatedAt
notifications	userId + read, createdAt
tempverifications	TTL on expiresAt (auto-deletes expired OTPs)

1.4 TTL indexes

[!note] Historical — MongoDB has been removed.

Used on tempverifications.expiresAt (5-minute auto-purge of email OTPs / passkey challenges). Mongo's TTL monitor ran every 60 seconds.

1.5 Backup with mongodump

[!note] Historical — MongoDB has been removed.

# Connect into the container, dump locally, copy out
docker exec nickapp-mongodb sh -c \
  "mongodump --db=marketplace --archive=/tmp/marketplace-$(date +%F).archive --gzip"
docker cp nickapp-mongodb:/tmp/marketplace-$(date +%F).archive ./backups/

# Or stream directly to host
docker exec nickapp-mongodb \
  mongodump --db=marketplace --archive --gzip \
  > ./backups/marketplace-$(date +%F).gz

For full details (retention, RTO/RPO, offsite copies) see Backup & Recovery.

1.6 Restore

[!note] Historical — MongoDB has been removed.

# Restore an archive to an empty database
docker exec -i nickapp-mongodb \
  mongorestore --archive --gzip --drop \
  < ./backups/marketplace-2026-05-20.gz

--drop drops each collection before restoring. Omit it to merge.

[!warning] Restoring is destructive to current data. Always practise on a staging clone first.

1.7 Migrations

[!note] Historical — MongoDB has been removed. Drizzle migrations are now used exclusively (npx drizzle-kit migrate).

There was no formal migration framework. Two patterns were used:

• Mongoose schema changes were forward-compatible (new optional fields default to undefined). Older documents would still load.
• Data backfills were one-shot scripts in backend/src/scripts/ (e.g. migrateUserPoints.ts, fix-transaction-hashes.js, fix-dispute-sellers.js).

1.8 Common admin queries

[!note] Historical — MongoDB has been removed.

// Count by collection
db.users.countDocuments({ role: 'buyer' })

// Disk usage per collection
db.runCommand({ collStats: 'payments', scale: 1024*1024 }).size

// Slow queries
db.setProfilingLevel(1, { slowms: 200 })   // log queries > 200ms
db.system.profile.find().sort({ ts: -1 }).limit(10)

// Lock contention
db.serverStatus().locks

1.9 Seeding production safely

[!note] Historical — MongoDB has been removed. Seeds are now Postgres-only and idempotent; see the PostgreSQL Operations section above.

Seed scripts were designed to be idempotent for categories but destructive for users/addresses.

[!warning] Never run seed:all or seed:users against production. They drop the existing users and addresses collections.

---

2. PostgreSQL 18 (legacy section — superseded by PostgreSQL Operations above)

[!note] Historical — This section documented the partial migration era. PostgreSQL is now the sole database; see the PostgreSQL Operations section at the top of this document.

2.1 Runtime role

Postgres is present in the current dev/integration stack, but MongoDB remains the primary runtime store.

As of v2.9.12, PostgreSQL is the only runtime store. All domain repositories use Drizzle. There is no dual-write mode.

2.2 Docker volume layout for Postgres 18

See the Docker volume layout subsection in PostgreSQL Operations above.

2.3 Apply migrations

cd backend && npx drizzle-kit migrate

19 migrations (0000–0019) covering 32 tables. See PostgreSQL Operations above.

2.4 Backfill and verification

[!note] Historical — Mongo→Postgres backfill tooling is no longer needed. The migration is complete.

Backfills used MIGRATION_PG_URL (not PG_URL) and enforced a host allowlist:

MIGRATION_MONGO_URL=mongodb://mongodb:27017/marketplace \
MIGRATION_PG_URL=postgres://amanat:...@postgres:5432/amanat_dev \
node dist/db/backfill/run-backfill.js --dry-run

2.5 Backup

See the Backup subsection in PostgreSQL Operations above.

---

3. Redis

3.1 Connection

Dev: redis://redis:6379 (no password). Prod: redis://:<REDIS_PASSWORD>@redis:6379. The compose command line is redis-server --requirepass "$REDIS_PASSWORD".

Inspect:

docker exec -it nickapp-redis redis-cli -a "$REDIS_PASSWORD"
> INFO server
> DBSIZE
> KEYS *           # prod-unsafe on large datasets, use SCAN

3.2 What we store

• Rate-limit counters for express-rate-limit
• Session data for refresh-token tracking and revocation lists
• Socket.IO adapter state (when scaled horizontally — currently single-node)
• Application caches (TTL'd keys for expensive aggregates)
• Idempotency keys for webhook deduplication

Key prefixes follow <service>:<entity>:<id>. E.g. payment:idem:<requestId>, auth:refresh:<userId>.

3.3 Persistence

Redis 8 defaults to RDB snapshots + optional AOF. Our compose uses the default config:

• RDB snapshot triggers: save 3600 1, save 300 100, save 60 10000.
• AOF is disabled by default.
• RDB file lives at /data/dump.rdb inside the redis_data volume.

To enable AOF for stronger durability, override the command in docker-compose.production.yml:

redis:
  command: ["sh","-lc","redis-server --requirepass \"$${REDIS_PASSWORD}\" --appendonly yes --appendfsync everysec"]

appendfsync everysec is the common compromise: at most 1 second of writes lost on crash, with negligible perf impact.

3.4 Eviction policy

Default is noeviction — Redis refuses writes when memory is full. For our use (caches that can be regenerated), set:

docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" \
  CONFIG SET maxmemory 256mb
docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" \
  CONFIG SET maxmemory-policy allkeys-lru

Persist by adding to a custom redis.conf mounted at /usr/local/etc/redis/redis.conf (then change the compose command: to ["redis-server","/usr/local/etc/redis/redis.conf","--requirepass",...]).

3.5 Backup

Redis backups are usually unnecessary (the data is regeneratable) but still cheap:

# Snapshot now
docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" BGSAVE
docker cp nickapp-redis:/data/dump.rdb ./backups/redis-$(date +%F).rdb

BGSAVE is non-blocking (forks). For AOF, copy /data/appendonly.aof too.

3.6 Cache flush

When deploying breaking changes to cached schemas:

# Flush everything (DEV ONLY)
docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" FLUSHALL

# Targeted (safer)
docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" \
  --scan --pattern 'payment:idem:*' | \
  xargs -L 1 docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" DEL

[!warning] FLUSHALL will sign out every user with an active refresh token and reset every rate-limit counter. Avoid in production unless that is what you want.

3.7 Monitoring

docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" INFO stats
docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" INFO memory
docker exec nickapp-redis redis-cli -a "$REDIS_PASSWORD" SLOWLOG GET 10

Watch evicted_keys, keyspace_misses, rejected_connections — see Monitoring for thresholds.

---

4. Maintenance windows

Schedule a window when:

• Bumping major version (PostgreSQL, Redis 8 → 9)
• Restoring from backup
• Running a destructive migration

Suggested checklist:

1. Announce in #ops Slack / status page.
2. Trigger pg_dump backup (see Backup & Recovery).
3. Stop the backend container so writes stop: docker compose stop nickapp-backend.
4. Perform the operation.
5. Restart backend: docker compose start nickapp-backend.
6. Verify health: curl https://amn.gg/api/health.
7. Close window.

---

5. Cross-links

• Backup & Recovery — formal backup/restore procedures, RTO/RPO targets, offsite storage.
• Monitoring — what metrics to watch (slow queries, evictions, replication lag).
• Incident Response — runbooks for database unreachable scenarios.
• Data Models — schema details for every table.
• Postgres Runtime Cutover Status — migration history and current state.