Backup Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• Manual and scheduled PostgreSQL logical dumps (pg_dump -Fc) of the whole application database, via BackupRunCreateService / BackupRunHandler.
• An optional archive of the local upload root (tar -czf), only when the effective storage driver is local.
• A filesystem catalog (manifest.json per run) that is self-describing and independently rebuildable, plus a Postgres index of that catalog (backup_run).
• Count-based grandfather-father-son (GFS) retention and pruning (BackupRetentionService), both scheduled and admin-triggered.
• Optional off-host replication of completed artifacts over rsync-over-ssh, with local-copy reclamation once a remote copy is verified (BackupReplicationService).
• The restore traffic lock (MaintenanceService, MaintenanceGuard). A restore engages it when accepted, marked reconciliation: "pending", and lifts it when it completes cleanly and has reconciled the restored database; while it is engaged, MaintenanceGuard, registered as an APP_GUARD, refuses non-operator traffic. An operator releases a lock a failed restore left engaged; nothing engages it by hand.
• Admin-editable backup/retention policy stored in settings.json under the backup root (BackupSettingsService), with optimistic concurrency.
• The request/accept surface for restoring the live database from a backup: preflight validation (BackupRestorePreflightService), a backup_restore audit row, engaging maintenance, writing a live-state file, and enqueueing a restore job (BackupRestoreAdminService).
• Executing the restore, with a safety net on both sides. BackupRestoreProcessor takes an inline pre-restore safety dump and links it to the restore (backup_restore.safety_backup_run_id) before spawning workers/restore-runner.ts as a detached process — deliberately not an in-process handler, because pg_restore --clean takes ACCESS EXCLUSIVE on every table, including admin_sessions. The runner drains existing connections before terminating them, restores the database, then verifies restored row counts against the source manifest. BackupRestoreProcessor supervises the whole run by polling restore-state.json until a terminal stage or an abandoned heartbeat. See 16.8 Backend Risk Register for the narrower gaps that remain.
• Cataloguing an operator-supplied archive (BackupUploadAdminController/BackupUploadAdminService, SE-4): verifies a keyed HMAC signature over the dump and manifest, screens the archive's schema SQL for constructs a restore must not execute, and inserts a kind:'uploaded' backup_run row the existing restore path can act on unchanged. Does not restore anything itself.
• Streaming a completed dump to an authenticated, permissioned admin, with integrity checks that run before the first byte and an audit write that fails closed (BackupDownloadAdminService).

Does Not Own

• General MongoDB backup/restore. A run copies exactly one collection — audit_logs, via BackupAuditLogService (§6.8c) — as an opt-in, insert-only complement to the PostgreSQL dump that remains the module's actual subject; no other MongoDB collection is ever touched. RestoreBackupDto.acknowledgeMongoNotRestored is deprecated and ignored, to be removed after 2026-12-31 — the audit-log merge it used to require acknowledging now runs automatically and needs no confirmation, since it only ever inserts entries missing from the live log and never removes or overwrites one. It is still accepted on the request so an older client is not rejected with a 400 while it carries the field.
• Storage driver implementation (local vs. bucket) — that is @skoolsewa/storage's StorageManager, which this module only reads hasRemoteDriver() from.
• BullMQ queue registration and defaultJobOptions — QueueName.BACKUP, QueueName.BACKUP_RESTORE and QueueName.BACKUP_REPLICATE are registered in apps/api/src/services/bullmq/bull.module.ts's REGISTERED_QUEUES, not in this module.
• Outbox delivery — OutboxService/OutboxModule (apps/api/src/modules/outbox/) own atomic enqueue and the dispatcher that relays outbox rows to BullMQ.
• Activity/audit log storage — ActivityRecordService (Postgres-backed activity log) and AuditLog (MongoDB) are separate modules this one calls into.
• Permission grants — Backup_*, BackupDownload_*, BackupConfigure_* are declared in packages/db/src/authorization/permission-catalog.ts and synced by pnpm --filter @skoolsewa/api permissions:sync; this module only checks them via @Permissions(...).

Source of Truth

3. Module Composition

4. File and Directory Map

apps/api/src/modules/backup/
  backup.module.ts
  backup.constants.ts
  admin/
    backup-admin-aggregate.module.ts
    catalog/
      backup-admin.controller.ts
      backup-admin.service.ts
      backup-admin.module.ts
      dto/ (create, update, update-settings, fetch, params, response, settings-response, index)
    download/
      backup-download-admin.controller.ts
      backup-download-admin.service.ts
      backup-download-admin.module.ts
      dto/backup-download-params.dto.ts
    restore/
      backup-restore-admin.controller.ts
      backup-restore-admin.service.ts
      backup-restore-lock.service.ts     (traffic lock read + release)
      backup-restore-admin.module.ts
      dto/restore.dto.ts
      dto/restore-lock.dto.ts          (RestoreLockDto, RestoreLockReleaseDto)
      restore-poll-token.guard.ts
      backup-restore-admin-lock.integration.spec.ts
    upload/
      backup-upload-admin.controller.ts
      backup-upload-admin.service.ts
      backup-upload-admin.module.ts
      backup-upload-storage.engine.ts
      dto/backup-upload.dto.ts
  shared/
    backup-artifact.service.ts       (pg_dump / tar spawn, hashing)
    backup-archive-content.util.ts   (schema-SQL construct scan — pure)
    backup-manifest.service.ts       (snapshot capture, manifest.json, reconcileFromDisk)
    backup-path.service.ts           (root resolution, containment)
    backup-replication.service.ts    (rsync-over-ssh push + read-back verify)
    backup-restore-preflight.service.ts
    backup-retention.service.ts      (GFS sweep, prune, floors)
    backup-run-create.service.ts     (shared insert+enqueue for manual/scheduled)
    backup-run-response.util.ts      (raw row -> BackupResponseDto shape — pure)
    backup-settings.service.ts       (settings.json, Zod schema)
    backup-signature.service.ts      (keyed HMAC sign/verify for uploaded artifacts)
    backup-state-file.service.ts     (atomic read/write for all 3 state files)
    backup-subprocess-excerpt.util.ts (stderr redaction — pure)
    backup-tier.util.ts              (GFS tier calendar math — pure)
    maintenance.guard.ts             (APP_GUARD-registered)
    maintenance.service.ts           (file + Redis + in-process cache)
  workers/
    backup-queue.processor.ts        (@Processor QueueName.BACKUP)
    backup-run.processor.ts          (BackupRunHandler — dispatched, not @Processor)
    backup-prune.processor.ts        (BackupPruneHandler — dispatched, not @Processor)
    backup-restore.processor.ts      (@Processor QueueName.BACKUP_RESTORE — spawns + supervises the runner)
    restore-runner.ts                (detached process — fences DB role, runs pg_restore, heartbeats)
    backup-replicate-queue.processor.ts (@Processor QueueName.BACKUP_REPLICATE)
    backup-replication.processor.ts  (BackupReplicationHandler — dispatched)
    backup-retention.scheduler.ts    (cron: enqueue daily sweep)
    backup-schedule.scheduler.ts     (cron: nightly dump, tier assignment)
    backup-sweep.scheduler.ts        (cron: stall recovery for runs + replication)
    backup-worker.module.ts

5. Data Model

5.1 Schema Source

packages/db/src/schema/backup/
  index.ts
  backup-run.ts
  backup-restore.ts
  enums.ts

5.2 Tables

backup_run

Notable CHECK constraints (full list in the schema file):

• uq_backup_run_single_running — partial unique index on (true) WHERE status = 'running'. Backstop behind worker concurrency: 1.
• chk_backup_run_completed_has_artifact — gated on completed only; disjunction between (localArtifactPresent AND artifactDir non-null) and (NOT localArtifactPresent AND replicationStatus = 'replicated') is what makes remote_only representable.
• idx_backup_run_stalled — partial index on started_at WHERE status = 'running', read by BackupSweepScheduler.
• idx_backup_run_retention — (kind, tier, created_at) WHERE status = 'completed', read by the GFS sweep.
• chk_backup_run_signed_has_fingerprint — signature_state IS DISTINCT FROM 'signed' OR signing_key_fingerprint IS NOT NULL. Written as an implication, not as a closed disjunction over the enum: a CHECK passes on NULL, and IS DISTINCT FROM is what makes the NULL case explicit rather than accidental. A future enum value therefore does not silently fall into the "must have a fingerprint" branch.
• chk_backup_run_unsigned_has_no_fingerprint — the converse. An unsigned row carrying a fingerprint is a contradiction the resolver could otherwise write during a partial update.
• chk_backup_run_signature_state_requires_completed — the state is only meaningful once an artifact exists. A queued row with a signature state is a row somebody wrote out of order.
• chk_backup_run_signing_key_fingerprint_shape — 8 lowercase hex characters, matching what BackupKeyStoreService computes. Same reasoning as chk_backup_run_sha256_format: a column that only a program writes still gets a shape constraint, because the program is what gets edited.
• idx_backup_run_signing_key_fingerprint — partial index on signing_key_fingerprint WHERE status = 'completed' AND signing_key_fingerprint IS NOT NULL. This is the index the retired-key prune refusal scans, and the partial predicate matches its query exactly; a full index would be larger and would not be used.

All six were probed against a real database in both directions — the row each must reject and a legitimate row each must accept — by packages/db/src/scripts/probe-0053-backup-signature-state.sql, which asserts the constraint name rather than only the SQLSTATE. A probe that checks only 23514 passes when a completely different constraint fires.

backup_restore

Notable CHECK constraints:

• uq_backup_restore_single_active — partial unique index on (true) WHERE status NOT IN ('completed','failed'). Two concurrent restores would race pg_restore against itself; POST .../force-clear is the documented recovery when a row is stuck here.
• chk_backup_restore_completed_has_safety — a completed restore always records its own rollback.

Retention: backup_run rows are pruned (never deleted) by count-based GFS policy. backup_restore rows are never pruned — a restore is a permanent audit record of a destructive act, and the table is small by construction.

5.3 Relationship Diagram

6. Services and Responsibilities

6.1 BackupPathService

6.2 BackupArtifactService

Input normalization: none needed — every argv value is either a config path or a value this module computed itself (never user input). Transaction boundary: dumpDatabase runs entirely inside the REPEATABLE READ transaction opened by BackupManifestService.captureAndRun, so the exported snapshot stays valid for the dump's full duration. Fail-closed: any subprocess failure deletes the .partial file and rethrows; BackupRunHandler.markFailed stores only the sanitised excerpt.

6.3 BackupManifestService

reconcileOne re-validates every manifest's artifactDir two ways: containment (resolveWithinRoot) and equality against the directory it was actually found in — a manifest naming a different directory is refused rather than trusted, because the file is operator-editable and the value later reaches an rsync remote-target concatenation elsewhere in the module.

6.4 BackupSettingsService

The same Zod schema validates both directions — settings.json is treated as untrusted input by policy, because an operator can hand-edit it.

6.5 BackupRetentionService

kind: 'uploaded' rows are deliberately absent from RetentionBucket/allBuckets() — the sweep never scans them at all, on top of them being inserted with pinned: true. An operator who carried an archive in during an incident did not do it so the nightly sweep could delete it an hour later; the only path off an uploaded row is an explicit admin DELETE, capped on the way in by BACKUP_UPLOAD_MAX_RETAINED.

Pruning a run frees only the disk it does not share. pruneRun deletes exactly one run's own artifact directory (rm(artifactDir, {recursive:true, force:true})) and nothing else. A snapshot- format uploads file, or an audit-log segment, that this run hard-linked from an earlier base is a second directory entry pointing at the same inode — removing this run's directory removes only its own name for that inode, and the file's bytes are freed only once every run still holding a link to it is pruned too. So the bytes a du of one run's directory reports, and the bytes actually reclaimed by pruning that one run, are two different numbers whenever incremental hard-linking is in play; the gap is exactly the bytes a later run is still linking from it.

6.6 BackupRestorePreflightService

Every check in assertRestoreAllowed, in order: feature-flag + admin-DB-URL configured → application role is not a Postgres superuser → source row exists → both acknowledgements are literally true → typed database name matches current_database() → source status = 'completed' and localArtifactPresent → the backup's schema_migration_tag equals the live database's latest drizzle.__drizzle_migrations.hash, read at preflight (a null or "unknown" tag on either side refuses) → recorded size then sha256 match the file on disk → artifact signature. All of this runs before any row is inserted — a rejected restore changes nothing.

The signature step, and why it has four outcomes rather than two

Verification on restore accepts imported keys as well as generated ones (trust: "restore"), because backup_run.database_dump_sha256 is an independent server-computed anchor the backup directory cannot forge. Upload verification does not (trust: "upload") — see §6.8.

6.7 MaintenanceService

Three-layer read (in-process cache 1s TTL → Redis → file, the file being authoritative), file-first-then-Redis write. isActive() fails closed on an unreadable/corrupt file (returns true) — the asymmetry is deliberate: absent means open, unreadable means closed, because a corrupt file mid-restore must never be read as "no maintenance." getState() throws on the same file instead; the lock read catches that and reports the lock engaged with reason: "unreadable". readSnapshot() reads the file past every cache and reports { readable: false } instead of throwing.

Writes, all serialised through one promise chain in the process:

The critical section is what makes release a compare-and-clear: a restore engaging between a release's checks and its write cannot be wiped. One process is the file's only writer — the API and the backup workers share it, and the detached runner writes only restore-state.json — so an in-process lock is sufficient; a file rename has no compare-and-swap and Redis is only a mirror.

Its callers are MaintenanceGuard, BackupRestoreAdminService (requestRestore, forceClear), BackupRestoreLockService (getLockState, releaseLock, settleClosedBeforeEngage) and BackupRestoreProcessor (finish, fail, resumeAfterRestart); there is no other writer.

6.8 BackupSignatureService

isConfigured() was deleted. It answered one question where there are two, and the collapse is what made "no key held" and "the key file cannot be read" the same branch on the restore path.

recordedState and heldFingerprints exist because two callers each answered the same question independently and diverged: the scheduled run branched three ways while the upload path used a two-way ternary that would write unsigned for an unreadable store, and the upload response mapper passed retired: [] as a literal, rendering a perfectly verifiable archive as "key not held" whenever a rotation landed between the re-sign and the mapping.

A backup is signed, not encrypted. signArtifacts writes an HMAC-SHA256 over "<dumpSha256>:<manifestSha256>" to artifact.sig — an authenticity control that proves which deployment produced an artifact and that neither the dump nor the manifest has been altered since. It does nothing to the confidentiality of database.dump, uploads/, or audit-logs/*.jsonl.gz themselves: pg_dump's output is the database's own plaintext logical dump (gzip'd where the format compresses it, never encrypted), the uploads snapshot is a plain file tree, and an audit-log segment is gzip'd EJSON. Anyone who can read BACKUP_DIR, the download response, or a replicated remote copy can read the backup's contents outright; the signature only ever answers "is this genuinely ours and unaltered," never "who is allowed to read this."

What the signature defends against, stated exactly

It defends against an actor who can write files inside BACKUP_DIR and has nothing else.

It does not defend against an actor who can read files as the API user — that actor holds the key file itself, and always did. Nor against a superadmin who knows their own password: reveal-once is a product requirement, so that actor can obtain a valid signing key simply by rotating. The SQL content scan in backup-archive-content.util.ts is what holds that line, not this key.

A checksum would authenticate nothing, because the uploader supplies the file and the checksum in the same request — both operands are theirs. A keyed HMAC is what makes one operand the server's.

Known gap: the HMAC covers "<dumpSha256>:<manifestSha256>" and not uploads.tar.gz. A run taken with includesUploads = true therefore carries an unsigned uploads archive beside a signed dump. The badge is scoped accordingly — it reads "Signed (database dump)". Extending the input would change the signature for every future artifact and make every existing .sig incomparable with a newly computed one, which is its own migration with its own compatibility window.

6.8a BackupKeyStoreService

Owns the file that holds the artifact-signing key, and the lifecycle around it.

Why a file, and not the two obvious places. Not the database: a dump would then contain the key that signs dumps, so anyone holding a dump holds the key. Not settings.json: that resolves inside BACKUP_DIR, so the key would sit in the directory it protects, and whoever can write a malicious dump could write the key that signs it.

Boot refuses when the path is not absolute, resolves (via realpath, with a separator guard) inside BACKUP_DIR or the upload root — both are copied into artifacts and shipped off-host — or when the file is not mode 0600, or its parent is group/world-writable or not owned by the process uid. A corrupt file is the one condition that does not refuse the boot: it degrades to unreadable, because refusing to start is a worse outcome than starting with verification refused.

persist() fsyncs both the file descriptor and the directory. This is the only copy of a retired key; a rename that survives in the page cache but not a power cut is not good enough.

The migration copies the existing value byte for byte and never generates a fresh key. Every artifact already on disk is signed with it, and a new key would make them all unverifiable — surfacing as BACKUP_UPLOAD_SIGNATURE_INVALID, which reads like corruption rather than like a key change. key is an opaque UTF-8 string and is never hex-decoded: a generated key happens to be 64 hex characters, but a migrated one may be anything an operator typed, and hex-decoding on one path and not the other would change every HMAC.

6.8b BackupUploadsSnapshotService

Snapshots the upload root into a run's artifact directory as a file tree, <artifactDir>/uploads/, alongside a uploads.index.json that records every file's relative path, size, mtimeNs, ctimeNs and sha256. A file whose size and both timestamps still match the base run's index entry is hard-linked to the base's own copy after its bytes are re-hashed and verified against the recorded sha256 — size and timestamps only prove the source looks unchanged, not that the base copy on disk is still intact (bit rot, an operator edit, a bad restore), and a link would otherwise hand back corrupted bytes under a checksum this run never actually checked. A hash mismatch falls back to an ordinary copy and counts toward one aggregate WARN logged at the end of the run. Everything else is streamed to <dest>.partial and hashed in the same pass (HashingPassThrough), then renamed into place — never a second read of the finished file.

Because a link only ever adds a second name for the same inode and this service never writes through one, every run directory stays a complete, independent copy of the upload tree on its own — pruning an old run never corrupts a newer one's files, and restoring uploads from one run never depends on another run still existing.

snapshot(...) returns null when StorageManager.hasRemoteDriver() — snapshotting the local upload root is meaningless once uploads live on a remote object store. Any defect in the base run's own index — missing, sha256 mismatch, invalid JSON, an unexpected shape, or an unsupported BACKUP_INDEX_VERSION — makes the whole base index unusable, logged once, and every file for this run is copied rather than linked; a single bad entry does not fail the run, it just costs the disk space a full copy costs. File operations run at a concurrency of 8 (CONCURRENT_FILE_OPERATIONS) through an internal gate that stops scheduling new work and rethrows the first error once anything fails, after letting already-scheduled work settle so nothing keeps writing into a directory that is about to be deleted. A symlink, socket, FIFO, device, or a file name that fails UTF-8 validation, does not round-trip through re-encoding, or contains a control character is skipped and counted in skippedCount rather than failing the run — uploadsSkippedCount on BackupResponseDto is how an operator learns the snapshot is not the whole directory. On any failure the partially-written uploads/ directory and index are removed before the error propagates, so a failed run never leaves a half-linked tree for the next run to link from.

Which earlier run is offered as a base is decided by BackupIncrementalBaseService, described next — this service only ever sees a base it is handed, and copies everything when handed null.

Restoring uploads is a manual step, not something a restore automates. restore-runner.ts and BackupRestoreProcessor (§7.5) act on database.dump alone — nothing in the restore path reads uploads/ or uploads.index.json at all. An operator who needs the uploaded files back downloads the run's uploads snapshot via GET .../download/uploads (§7.8, Download) and copies its contents onto the upload root by hand. This mirrors why uploads restore is out of scope the same way general MongoDB restore is (see Does Not Own): a database restore is the operation that must be fast, scriptable and blocking, since it holds ACCESS EXCLUSIVE locks and engages maintenance; restoring a filesystem tree has neither property and is left to the operator's judgment about whether the current upload root should be overwritten, merged, or left alone.

6.8c BackupAuditLogService

Exports the MongoDB audit_logs collection as one gzip'd, canonical-EJSON segment per UTC day — <artifactDir>/audit-logs/<YYYY-MM-DD>.jsonl.gz — alongside an audit-logs.index.json recording each day's document count, byte size, sha256, and whether it is sealed. A day is grouped by $dateToString over the ObjectId-derived timestamp; every document is streamed through gzip and a hashing pass in one pipeline, so nothing is buffered whole in memory.

A day is sealed — eligible for the next run to hard-link instead of re-exporting — once it ended at least BACKUP_AUDIT_SEAL_LAG_MINUTES (default 15) before the export runs, which absorbs clock skew between whatever process stamped a document's _id and the exporter itself. A sealed day is only actually reused when its live document count in Mongo still matches what the base run recorded for it — a late write into an already-sealed day forces a fresh export of that one day rather than silently omitting the late document — and, as with uploads, the base segment's bytes are re-hashed and confirmed against its recorded sha256 immediately before the link() call. Every BACKUP_AUDIT_FULL_EXPORT_DAYS (default 30), the run performs a full export of every day rather than reusing any sealed segment, so documents the collection's own TTL index has since expired drop out of the backup's index instead of being carried forward as hard links to files that no longer correspond to anything live.

Extending audit retention. MongoDB's TTL index removes an audit_logs document once it expires, but a segment already exported into a backup keeps that document's bytes on disk independently of the live collection — so a backup taken before expiry is how an audit trail outlives its own TTL. Restoring a run's segments merges by _id, so a chain of periodic backups is what lets an operator recover an entry the live collection has already dropped.

export() is skipped entirely — via BackupRunHandler.exportAuditLogs — for a pre_restore_safety run (the restore's own merge is additive and needs no inverse to protect against) and whenever BACKUP_INCLUDE_AUDIT_LOGS is not true. A genuine export failure — Mongo unreachable, an authentication error, or any other exception — is absorbed into audit_logs_state = 'failed' plus audit_logs_error_code, and the run still completes: the Postgres dump is what a backup exists for, and an unreachable audit store must not cost it. BACKUP_AUDIT_LOGS_UNAVAILABLE is used for connectivity-class driver errors (MongoServerSelectionError, MongoNetworkError, MongoNetworkTimeoutError, MongoNotConnectedError, MongoTopologyClosedError, and MongoServerError codes 18/13 — authentication failures); anything else is BACKUP_AUDIT_LOGS_EXPORT_FAILED. A deadline expiry (the run's overall abort signal) is not absorbed — the run is genuinely out of time and fails as a whole, the same as a database-dump timeout.

merge(...) runs during restore, described in 7.5: it verifies every segment's sha256 against the index before inserting anything from any of them, then streams each segment through gunzip and inserts by insertMany({ ordered: false }) against the driver collection directly — never the Mongoose model, whose validate hook would restamp expiresAt. A document whose _id already exists is caught as a bulk-write error (Mongo code 11000) and counted as alreadyPresent; anything else rethrows. A document whose backed-up expiresAt has already passed by the time of the merge is skipped and counted as expiredSkipped rather than inserted, so a restore never reintroduces a document the collection's own TTL would immediately remove again. The merge is insert-missing-only — it never deletes and never overwrites an existing document — so restoring a backup can only add audit history back, never make the live trail disagree with what actually happened after the backup was taken.

6.8d BackupIncrementalBaseService

Decides which earlier backup_run a new run's uploads snapshot and audit-log export may hard-link from — uploadsBase(excludeRunId) and auditLogsBase(excludeRunId) are asked independently, because the most recent completed run is often a pre_restore_safety dump with no audit copy, or a manual run with no uploads, and forcing either to be everyone's base would force a full copy of whatever it happens to lack.

A candidate must be completed, still localArtifactPresent, not kind: 'uploaded' (an operator- supplied archive was never snapshotted the same way), and — for uploads — recorded uploadsFormat: 'snapshot', or — for audit logs — auditLogsState: 'included'. Passing that filter is not enough on its own: the candidate's artifact.sig must verify against a currently-held signing key over "<recorded dump sha256>:<sha256 of manifest.json's bytes on disk>", and the base's index digest is then read only from that verified manifest.json — never from the backup_run row, which a reconcileFromDisk pass may have rebuilt from a file nobody re-verified. This matters because linking inherits the base's bytes and the digests recorded for them, and the new run signs over those inherited digests with the current key — so a base has to be authentic, not merely present, or anyone able to write inside the backup root could plant or alter a run and have the next scheduled backup unknowingly re-sign it as legitimate. The base run's database dump is never itself linked or re-verified here — only its uploads snapshot and audit segments, each of whose own files are hashed and checked individually before any link is created.

When this deployment holds no signing key at all (BackupSignatureService.hasAnyKey() is false), nothing can ever verify as a base — logged once per process — and every run copies uploads and exports audit logs in full. That is the same condition documented in BackupSignatureService: a deployment can run without a key, it just never gets the incremental hard-linking win.

6.9 BackupUploadAdminService

Order of checks in upload(): retained-cap → manifest parse/shape → dump-vs-manifest sha256 (corruption check only) → HMAC signature verify (the actual control) → pg_restore --list format check → schema-SQL content scan (scanArchiveSql) → finalize (move .partial into place, write fresh manifest + signature, insert row). The operator's own manifest.json is never written to disk — only schemaMigrationTag and manifest.tables are read from it in memory — because reconcileFromDisk runs at every boot and would otherwise launder an operator-editable "kind":"manual" claim into the catalog on the next restart. On any exception, the finally block unlinks the .partial dump unless finalize already moved it into place.

7. Runtime Flows

7.1 Manual backup ("Run now")

7.2 Scheduled (nightly) backup

BackupScheduleScheduler registers a CronJob via SchedulerRegistry (not @Cron, because the schedule is admin-editable and must be reloadable without a restart). On each tick, resolveTier assigns the most significant period (month → ISO week → day) with no completed run yet, then calls the same BackupRunCreateService.createRun used by the manual path — kind: "scheduled". A tick that throws is logged and swallowed, never crashing the next tick.

7.3 Retention sweep

BackupRetentionScheduler.enqueueSweep() runs daily at 03:00 (@Cron(CronExpression.EVERY_DAY_AT_3AM)), enqueueing backup.prune {reason:"scheduled"} directly onto QueueName.BACKUP (the documented outbox exemption — a cron tick with no accompanying database write). BackupPruneHandler.prune dispatches to BackupRetentionService.sweep().

7.4 Stall recovery

BackupSweepScheduler runs every 5 minutes and marks any backup_run stuck running past BACKUP_TIMEOUT_MINUTES + 5 as failed (using idx_backup_run_stalled). That half is skipped while restore-state.json shows a restore in a non-terminal stage whose heartbeat is within BACKUP_RESTORE_ABANDON_SECONDS: pg_restore --clean brings back a catalog in which the source run reads running with an old started_at, and reaping it before the post-restore reconcile would fail that run permanently. A heartbeat older than the abandon window means the runner is dead — an operator force-clear settles the restore row without rewriting the file — so the sweep resumes. The other half marks any replicationStatus = 'pending' past BACKUP_REPLICATE_TIMEOUT_MINUTES + 5 as failed. Direct database writes, not BullMQ jobs — nothing to enqueue, only a row to fix.

7.5 Run a manual restore

The execution logic lives beside its supervisor: workers/restore-runner.ts (moved from an earlier apps/api/scripts/ location — the build is nest build --builder swc with sourceRoot: "src", so anything outside src/ was never emitted, and the first version of this wiring spawned a script that could not exist in dist/; see the file's own docblock). It is a detached process, deliberately not a BullMQ handler: pg_restore --clean holds ACCESS EXCLUSIVE on every table including admin_sessions, which JwtStrategy reads on every authenticated request, so a worker running inside the API would block on its own authentication and could be killed mid-restore by an orchestrator. Its own entrypoint is guarded by require.main === module, so importing the module (e.g. from a spec, to reuse verifyRowCounts) never triggers a restore. Run as node dist/modules/backup/workers/restore-runner.js <restorePublicId>, it:

1. Reads restore-state.json for state.sourceArtifactDir — written by BackupRestoreAdminService.requestRestore at submit time (source.artifactDir ?? "").
2. Fences the application's DB role with ALTER ROLE "<appRole>" CONNECTION LIMIT 0 (not ALTER DATABASE — a database-level limit is not in a single-database dump and would survive the restore, locking the site out after success). This stops new connections; it does nothing to ones already open.
3. Drains before terminating: polls pg_stat_activity for the application role's remaining connections every second, heartbeating restore-state.json ("Waiting for N connection(s) to close") so the supervisor does not abandon a restore that can legitimately take minutes here. The window is BACKUP_RESTORE_DRAIN_SECONDS — admin-editable as restoreDrainSeconds, injected into the child process's own environment by spawnRunner from the effective settings at spawn time (see below), not read from the raw environment. Whatever is still connected when the window closes is terminated with pg_terminate_backend — a courtesy with a deadline, not a negotiation, since pg_restore --clean needs ACCESS EXCLUSIVE and will not get it while a session holds a lock.
4. Runs pg_restore --clean --if-exists --single-transaction --no-owner --no-privileges against BACKUP_ADMIN_DATABASE_URL, with a timeout from BACKUP_RESTORE_TIMEOUT_MINUTES — same admin-editable/env-injected pattern as the drain window (restoreTimeoutMinutes). --single-transaction is load-bearing — probed and confirmed: restoring a truncated dump with it left the database untouched; without it the table was emptied and the original rows were gone.
5. Re-keys the restored outbox with rekeyRestoredOutbox(admin) (workers/rekey-restored-outbox.ts): UPDATE public.outbox_events SET dispatch_key = gen_random_uuid() WHERE status = 'pending' on the runner's admin connection, while the connection fence still holds. A restored pending row carries the dispatch_key it had at dump time, and the BullMQ job id is outbox-<dispatch_key>; if the live database dispatched that row after the dump, Redis may still hold the finished job, and relaying the restored row under the same id would return that job and drop the work. Only pending rows — the set the dispatcher claims — are re-keyed; a dispatched row keeps its key because report maintenance looks its live job up by it. When information_schema.columns shows no dispatch_key on public.outbox_events — a dump taken before migration 0059_outbox_dispatch_key — the function skips the UPDATE and returns 0; the restored migrations table then lacks 0059, and the next pnpm db:migrate re-applies it, whose DEFAULT gen_random_uuid() gives every row a fresh key. The count is set on the runner's state as outboxRekeyed, so every later write of restore-state.json — heartbeat, verifying, completed and failed — carries it.
6. Moves to stage:"verifying" and calls the exported verifyRowCounts(root, sourceDir, adminUrl): reads manifest.json's manifest.tables (the source backup's own recorded per-table counts) and, for every table it names except those in RESTORE_VERIFICATION_EXCLUDED_TABLES (admin_sessions, customer_sessions, backup_run, backup_restore, outbox_events, drizzle_migrations), runs SELECT count(*)::bigint FROM public."<table>" against the just-restored database and compares. The exclusion exists because the restore itself changes those tables after pg_restore returns — it truncates the session tables, re-keys the pending outbox rows, and rebuilds its own catalog rows — and backup_run in particular is guaranteed to differ, because the pre-restore safety dump inserts a row after the source dump's manifest was captured; comparing them would fail verification on every successful restore, the exact inversion of what the check is for. Table names are validated against /^[a-z_][a-z0-9_]*$/ before interpolation into the query — they come from a file on disk in the backup root, not a bound parameter, so an unexpected identifier is refused rather than queried. Any mismatch, or an unreadable/empty manifest, throws — which the outer catch reports as BACKUP_RESTORE_VERIFICATION_FAILED specifically (see below), not the generic failure code.
7. Heartbeats restore-state.json every 5 seconds (stage, runnerPid, heartbeatAt) throughout, so GET .../live can show real progress.
8. Releases the connection fence before publishing stage:"completed" — the supervisor queries the database the instant it reads that stage, and a still-fenced role would refuse that query and record a successful restore as a failure. The finally block also unconditionally releases, on every other exit path.

The catch block distinguishes three error codes. A throw from the outbox re-key gets BACKUP_RESTORE_OUTBOX_REKEY_FAILED (the restore applied, but pending outbox rows may still share a job id with a job in Redis); otherwise the stage executing at the moment of the throw decides — failedDuring === "verifying" gets BACKUP_RESTORE_VERIFICATION_FAILED (the restore applied but does not match the manifest's row counts — the more dangerous outcome, because the database is populated but wrong), anything else gets BACKUP_RESTORE_FAILED. The re-key runs while the stage is still restoring, and BACKUP_RESTORE_FAILED tells restoreMayHaveApplied the database is untouched, so a re-key failure must not share it: the supervisor would skip the reconciliation a replaced database owes. All three are read at the throw site in restore-runner.ts's main() catch block, not guessed from behavior.

A job whose row is closed never runs. The outbox job commits with the row and arrives later, whatever happened to the row in between: force-clearing a queued restore closes the row and lifts the lock, because the database is untouched. So the first thing process() does — before it reads restore-state.json or spawns anything — is read the job's backup_restore row. When the row is missing or its status is not in RESTORE_NON_TERMINAL_STATUSES, it logs restore <id> is closed (<status>); job skipped as a warning and returns. It does not call fail() and writes nothing to the row, the state file or the lock: whoever closed the row already settled all three. It returns rather than throws, because with attempts: 1 and maxStalledCount: 0 a throw only adds a failed BullMQ job. resumeAfterRestart carries no such check, deliberately: it never spawns a runner, a restore force-cleared after it may have applied keeps its pending marker precisely so the restarted API settles it, and its row may be gone, replaced by pg_restore itself.

Before any of this runs, BackupRestoreProcessor.takeSafetyDump takes an inline pre-restore safety dump and links it to the restore. Called from process() immediately before spawnRunner() (only on the branch where no runner is already alive — an attach to an existing runner skips it, since the dump for that restore was already taken on the original attempt), it calls the same BackupRunCreateService.createRun used by manual and scheduled backups, with kind: "pre_restore_safety" and the new enqueue: false (CreateBackupRunInput.enqueue?: boolean, defaults true). Before the dump runs, it writes the new row's publicId into restore-state.json as safetyBackupPublicId — deliberately before, not after, so a crash mid-dump still tells an operator which half-written artifact to look for. Then, instead of letting the outbox dispatcher pick the job up minutes later, it calls BackupRunHandler.run() directly, inline, in the same process, and waits for it. This is deliberate, not a shortcut: enqueuing would return immediately and let the runner start draining connections and running pg_restore --clean while the safety dump was still mid-snapshot — the two would race, and the "safety" dump could capture a half-destroyed database. takeSafetyDump then re-reads the backup_run row by its publicId and requires status === "completed" before proceeding; BackupRunHandler.run swallows its own failures into the row rather than throwing, so the absence of a thrown exception proves nothing — the row is the evidence. If the dump insert throws, the run throws, or the row does not reach completed, takeSafetyDump calls fail() and returns false, process() returns without ever spawning the runner, and maintenance stays engaged — proceeding without a safety dump is judged worse than the extra downtime.

Once the dump is confirmed completed, takeSafetyDump writes backupRestores.safetyBackupRunId onto the restore's own row — UPDATE backup_restore SET safety_backup_run_id = <safety dump's id> WHERE public_id = <restorePublicId> AND status IN ('queued', 'safety_dump', 'restoring', 'verifying'). It is the last row write before spawnRunner, and the dump before it can take minutes, during which force-clear is allowed (no runner pid is alive yet) and lifts the lock. The status condition makes this write and force-clear's own conditional UPDATE serialise on the row: when force-clear commits first, this update matches nothing, takeSafetyDump re-reads the row, finds it closed, logs restore <id> is closed (<status>); job skipped before spawning the runner and returns false without calling fail() — force-clear already settled the row and the lock. If the update matches nothing because the row is gone, takeSafetyDump fails the restore rather than proceeding with a link that cannot be recorded: without it, the restore would still run to completion and then fail on the chk_backup_restore_completed_has_safety CHECK at the very last write — the database already replaced, the work already done, the row stuck reporting something other than success. This same write is what makes BackupRetentionService.isMostRecentRestoreSafetyDump's retention floor actually fire — the column, the FK, the CHECK constraint and the retention rule were all already in place; only the write was missing. There is still no one-click "undo" — restoring the safety dump back requires an operator to find it (via safetyBackupPublicId on RestoreLiveStateDto, or by browsing the catalog) and submit it as the source of a second, ordinary restore request.

Step 8 — post-restore audit-log merge, from an anchor captured before the database was touched. Once maintenance is lifted, BackupRestoreProcessor.finish calls mergeAuditLogs(publicId), which reads restore-state.json back and merges state.sourceAuditLogs — the RestoreAuditAnchor ({ state, indexSha256, signatureVerified }) BackupRestorePreflightService.assertRestoreAllowedWithAnchor captured at submit time, from the source run's manifest.json in the same bytes whose HMAC signature had just verified. The merge never re-reads backup_run for this: pg_restore --clean replaces that very table with the source dump's own copy, in which the source row is still running with no audit columns at all, so anything the merge needs has to already be in hand before the database is touched.

• anchor.state !== "included" → status: "not_in_backup" — the source carried no audit-log copy; nothing to merge, not treated as a failure.
• anchor.signatureVerified === false → status: "failed", errorCode: BACKUP_AUDIT_LOGS_UNVERIFIED — the source's manifest.json did not verify against a currently-held signing key at preflight time, so its recorded auditLogsSha256 is not trusted enough to merge blind. This is the same authenticity requirement BackupIncrementalBaseService applies before offering a run as a hard-link base — see 6.8d — applied here to a restore instead of a new run.
• Otherwise, BackupAuditLogService.merge() runs against state.sourceArtifactDir and anchor.indexSha256 (§6.8c) and reports status: "merged" with the segment/insert counts, or status: "failed" with whichever BACKUP_AUDIT_LOGS_* code the merge threw.

The merge runs after maintenance is disengaged and never fails the restore — a try/catch around the whole call logs and records the outcome without rethrowing, because the database restore has already succeeded and an audit-log problem must not make that look like a failed restore. The outcome is not written to restore-state.json's live-poll shape (the merge runs after the state file has already reached stage:"completed") and is not surfaced through any response today — it is logged, and RestoreAuditLogsResultDto (see api.mdx) declares the shape for a future endpoint to return it, but no controller returns one yet.

Trust boundary. restore-state.json is written only by the API process itself, at submit time, before the row is inserted and before any worker runs — the same trust basis already documented for sourceArtifactDir (§7.5, step 4): it is server-written state a detached process later reads, never a value an operator or a request body supplies. sourceAuditLogs extends that same boundary rather than reopening it: signatureVerified is computed once, from a manifest whose HMAC was checked against a held key at the moment of computation, and is then carried as a plain boolean through a file only this module's own processes write — a value worth trusting exactly as far as the state file itself already is, no further.

7.6 Force-clear a stuck restore

POST admin/system/restores/{publicId}/force-clear marks a non-terminal row failed (with BACKUP_RESTORE_ABANDONED) — recovery from a runner that never spawned or was killed. It then lifts the lock through disengageIf: unconditionally when restore-state.json names this restore and restoreMayHaveApplied is false (the database was never touched), and otherwise only when the lock's reconciliation is neither pending nor reconcile_failed. A lock that still owes a reconciliation keeps it, for the supervisor — or a restarted API — to settle. It refuses with 409 BACKUP_RESTORE_ALREADY_ACTIVE while restore-state.json names this restore and its runnerPid is alive, and with 409 BACKUP_RESTORE_NOT_FOUND once the row is already terminal — so a restore that already failed is recovered by releasing the lock instead (7.6a). There is no age threshold, and a runner that has not yet recorded its pid (0) is not detected: force-clearing then leaves that subprocess to finish or fail with nothing tracking it. A job still on its way for a force-cleared restore finds the row closed and is skipped before the safety dump, and a force-clear that lands during the safety dump stops the job at the linking UPDATE, before the runner spawns (7.5).

7.6a Read and release the restore traffic lock

The traffic lock is maintenance.json (mirrored to Redis, cached in-process for 1s), read by MaintenanceGuard on every HTTP request. None of its writers is a general toggle:

BackupRestoreProcessor.fail never lifts the lock, so every failed or abandoned restore ends with it engaged — except one failed before its submit finished engaging the lock, which settleClosedBeforeEngage releases because no runner ever started.

reconciliation answers "has the restored database been reconciled?" — a new change-feed epoch, every integration key revoked, every webhook paused (IntegrationRestoreReconciler.reconcile). The restored rows hold keys revoked after the dump as active, so a runner having exited is not a restore having settled. pending and reconcile_failed refuse the release; reconciled and an absent marker (a lock with no restore id, a restore that failed before pg_restore committed, or a state written before the marker existed) do not.

The way out of pending and reconcile_failed is an API restart. BackupRestoreProcessor.onApplicationBootstrap starts resumeAfterRestart() in the background: reconcile_failed reconciles again and records the outcome; pending whose restore-state.json shows the runner already spawned (any stage but queued and safety_dump) is supervised again from the state file until it settles, because the job that was supervising it died with the old process and maxStalledCount: 0 does not re-run it. queued is left for its job; safety_dump never reached pg_restore and force-clear releases its lock.

GET admin/system/restores/lock (BackupRestoreLockService.getLockState, Backup_READ, ADMIN_READ) maps MaintenanceService.getState() onto RestoreLockDto. blockingRestorePublicId comes from findLockReleaseBlocker() (the open row, else the restore whose runner pid is alive) and is reported whether or not the lock is engaged. A missing state, or one with active: false, maps to active: false, releasable: false and null lock fields — the stored engagedAt/engagedBy of a disengaged state record the release, not an engagement. An unreadable file maps to active: true, reason: "unreadable", null lock fields, and releasable from the blocker alone. When the lock is engaged, releasable is: no blocker, and reconciliation neither pending nor reconcile_failed.

POST admin/system/restores/lock/release (BackupRestoreLockService.releaseLock, Backup_RESTORE, ADMIN_WRITE, @HttpCode(200)) runs, in order:

1. MaintenanceService.readSnapshot() — the lock as the file holds it now; the write is compared against this.
2. findLockReleaseBlocker() — the newest backup_restore row whose status is in RESTORE_NON_TERMINAL_STATUSES (queued, safety_dump, restoring, verifying) yields 409 BACKUP_RESTORE_ALREADY_ACTIVE; otherwise a runnerPid > 0 in restore-state.json that process.kill(pid, 0) finds alive yields the same 409 naming the pid. Both carry details.blockingRestorePublicId. Unlike forceClear, the pid is checked whichever restore the state file names.
3. MaintenanceService.disengageIf(...), which re-reads the file inside the critical section: not engaged → { released: false }; engaged by a different restorePublicId than step 1 read (or readable where step 1 found it unreadable, or the reverse) → 409 BACKUP_RESTORE_ALREADY_ACTIVE naming the new restore; reconciliation: "pending" → 409 BACKUP_RESTORE_RECONCILE_PENDING; "reconcile_failed" → 409 BACKUP_RESTORE_RECONCILE_FAILED; otherwise it writes active: false, file first, then Redis. An unreadable file in both reads is overwritten.
4. recordActivity with action backup.restore.lock_released, module system, resource backup_restore/the step-1 restorePublicId, metadata { restorePublicId, unreadableState } — written beside the ActivityAuditInterceptor entry, because the interceptor cannot see which restore's lock was lifted. Then a warn log [backup] restore lock released (<restorePublicId>) and { released: true }.

It does not touch the backup_restore row. Both routes sit under the /api/admin/system/restores/ prefix in MAINTENANCE_EXEMPT_PATH_PREFIXES, so they answer while the lock holds.

7.7 Live restore polling

GET admin/system/restores/{publicId}/live reads only restore-state.json — never a table — so it stays answerable while pg_restore --clean holds ACCESS EXCLUSIVE on every table during an active restore. Returns 410 GoneException (BACKUP_RESTORE_NOT_FOUND) if the file is absent or names a different restore.

7.8 Download

The three gates, and why each is necessary

A response can only carry an error while nothing has been written to it. Everything fallible therefore happens before the first setHeader, and for a spawned process that means waiting for its first byte. Each gate below exists because the previous one was measured to be insufficient.

Gate 1 must call read() and unshift() the chunk back, not merely listen for readable. Node emits readable at EOF as well, so a bare listener proceeds to headers and answers 200 with an empty body; a bare read() without the unshift silently drops the first 64 KB.

Gate 3 is what makes a partial artifact observable. Content-Length is deliberately absent on this route — the tar is built on the fly — so the response is chunked, and a chunked response whose terminating chunk never arrives is a truncated transfer that every HTTP client reports as an error. On a non-zero exit the response is destroyed, never ended.

Without gate 3 the fix would have been worse than the defect: a visible hang replaced by a silent, structurally valid backup with no database.dump in it, delivered over 200 with an audit row saying success.

The uploads route needs none of this. It streams a finished file with a known Content-Length, so a short transfer is already detectable, and there is no child process whose status could disagree with the bytes.

Two-phase audit

The row written before streaming is pending, not success. When the transfer settles, a companion row is appended — backup.download.completed / .failed, and likewise for backup.download_uploads.

Appended rather than updated, because auditLogSchema sets updatedAt: false: an in-place update would record no finish time at all, and "when did this artifact finish leaving this host" is the question the trail exists to answer. It also keeps the collection write-once.

The initial write stays blocking and fails closed — an audit failure still refuses the download. The companion write is best-effort: by the time it runs, bytes are on the wire and there is no status left to send. A pending row with no companion means the transfer did not complete cleanly, which is the conservative reading and the correct one.

7.9 Off-host replication

Enqueued by BackupRunHandler in the same transaction as run completion, when storageMode !== 'local' and remoteReplicationEnabled. BackupReplicationHandler.replicate → BackupReplicationService.replicate: recomputes the relative artifact directory from publicId + createdAt (never trusts artifact_dir, which originates in an operator-editable file) and validates it against BACKUP_REMOTE_RELATIVE_DIR_PATTERN; pushes the dump/archive via rsync (--secluded-args, excluding manifest.json); pulls the dump back to a temp dir and hashes it locally (never trusts a remote-reported hash); pushes manifest.json last, only once the heavy artifacts are verified. On any failure the row is marked replicationStatus='failed' — a failed replication does not fail the backup itself.

7.10 Upload and catalogue an operator-supplied archive (SE-4)

This endpoint never restores anything — it only catalogues bytes an operator already has (from a download of this deployment, or another one whose key this deployment generated) so the existing, unchanged restore path (BackupRestoreAdminController/BackupRestoreAdminService/BackupRestorePreflightService) can act on the resulting row exactly like any other completed backup. The multipart body carries three required parts (file, manifest, signature) via BackupUploadStorageEngine, a custom Multer storage engine that streams the dump straight to a .partial file on the backup volume — hashing as it writes — rather than buffering a multi-gigabyte upload in memory; the small manifest/signature parts are buffered.

The plain sha256 comparison against the manifest is a corruption check only — both operands arrive in the same request from the same actor, so it authenticates nothing about provenance. The HMAC signature over "<dumpSha256>:<manifestSha256>", keyed by an environment variable the uploader cannot read or write, is the actual control. The schema-SQL content scan is defence in depth on top of that: pg_restore --list's table of contents records an object's type, not its properties, so it cannot tell an ordinary trigger function from a SECURITY DEFINER one — rendering the schema SQL and scanning the text can.

8. Caching

This is the module's only cache key. It exists to avoid a Redis round trip on every request through MaintenanceGuard (a global guard); the file remains authoritative and Redis failures fall through to it silently.

9. BullMQ, Schedulers, and Async Work

Schedulers (@nestjs/schedule, in-process cron — not queue jobs themselves):

9.1 Dead letters and retry

backup.prune and backup.replicate jobs that exhaust their attempts land as dead letters the shared JobFailureReplayService tracks, scoped in this module by BackupFailuresAdminService to QueueName.BACKUP and QueueName.BACKUP_REPLICATE. GET admin/system/backups/failures lists them, newest first, under Backup_READ. Only two of the queue's job names are ever retryable, and each gets its own route under its own permission so the pin/note permission can never trigger a deletion:

• POST admin/system/backups/failures/{publicId}/retry-prune — Backup_DELETE — replays a failed backup.prune under a fresh job id; the worker re-checks the run against the current retention settings and floors before deleting anything, exactly as a fresh prune would.
• POST admin/system/backups/failures/{publicId}/retry-replication — Backup_UPDATE — replays a failed backup.replicate under a fresh job id.

A failed backup.run is listed for the record but is never replayable — its row is terminal and the operator starts a new backup instead — so a retry attempt against one answers 409 BACKUP_FAILURE_NOT_RETRYABLE, the same code a retry-prune against a replication failure (or vice versa) also answers, since each route replays exactly one job name. 409 JOB_FAILURE_ALREADY_REPLAYED covers the case where the failure was already retried.

10. Realtime and Events

None. The module emits no Socket.IO/WebSocket events. The nearest analogue is the outbox aggregateType/eventType pairs used purely for internal enqueue delivery (backup_run.requested, backup_run.prune_requested, backup_run.replication_requested, backup_restore.requested), which are not broadcast anywhere — they exist only to carry a job onto a BullMQ queue.

11. Security, Auth, and Abuse Controls

• Guards: every admin controller carries @UseGuards(JwtAuthGuard, RoleGuard, IpThrottlerGuard). The one handler that steps outside the session guard is GET admin/system/restores/{publicId}/live: @Public() to JwtAuthGuard, because the session lookup reads tables the restore locks, and authenticated instead by RestorePollTokenGuard against the token stored in restore-state.json. No route in this module accepts a guest.
• Releasing the traffic lock carries the restore permission: POST admin/system/restores/lock/release needs Backup_RESTORE, the same permission that starts a restore, because lifting the lock early puts users in front of a database that may be mid-replacement. It refuses while any restore row is non-terminal or any runner pid is alive, and while the restore that engaged the lock owes a reconciliation — lifting the lock over an unreconciled database lets every integration key revoked after the dump authenticate again. Nothing engages the lock by hand.
• Permissions: Backup_READ/CREATE/UPDATE/DELETE/RESTORE, BackupDownload_READ, BackupConfigure_UPDATE, BackupUpload_CREATE, BackupKey_READ/CREATE/UPDATE/DELETE. Backup, BackupDownload, BackupConfigure, BackupUpload and BackupKey are all in SUPERADMIN_ONLY_MODULES — the admin role never holds them, only superadmin. BackupKey is split from BackupConfigure because the latter changes dump frequency and retention counts, while the former replaces the credential every artifact is authenticated against and hands the caller plaintext key material.
• Two independent restore gates: Backup_RESTORE (a grantable permission, superadmin-only) and BACKUP_RESTORE_ENABLED (a deploy-time environment switch). A permission can be granted by mistake; an environment variable cannot be granted from inside the running application.
• Upload is authenticated by a keyed HMAC, not a checksum: BackupSignatureService.verifyAgainstHeldKeys compares HMAC-SHA256(key, "<dumpSha256>:<manifestSha256>") with timingSafeEqual, against every key held for that trust level. A plain sha256 comparison against an uploader-supplied manifest would authenticate nothing, since both operands arrive in the same request from the same actor — kept only as a corruption check.
• Upload content is screened before it is catalogued: scanArchiveSql renders the archive's schema SQL (pg_restore -f - --schema-only) and refuses constructs a restore must not execute, because pg_restore --list's table of contents records an object's type, not its properties, and cannot distinguish a SECURITY DEFINER function from an ordinary trigger function.
• Rate limits: ADMIN_READ (list/detail/settings-read, 30/min IP-keyed), ADMIN_WRITE (pin/prune/settings-write and the lock release, 10/min IP-keyed — a release is retried until the restore settles, so it does not spend the restore's daily budget), ADMIN_ASYNC_JOB_SUBMIT (manual run, 10/hour IP-keyed), ADMIN_BACKUP_DOWNLOAD (3/hour, user-keyed — an IP-keyed cap on the endpoint that streams every customer record and password hash would be bypassed by rotating IPs), ADMIN_BACKUP_UPLOAD (3/hour, user-keyed, same reasoning), ADMIN_RESTORE_SUBMIT (2/day, user-keyed, matching AUTH_DAILY_IRREVERSIBLE).
• Symlink containment is two controls covering two different path components, not one with a backup. The artifact open uses O_RDONLY | O_NOFOLLOW, so a symlink at the artifact path is refused atomically with ELOOP — there is no window between a check and a use, because there is no separate check. Measured: O_NOFOLLOW guards only the final component; a symlinked parent directory is followed silently and the open succeeds. So resolveExistingDirWithinRoot resolves the directory prefix through realpath and re-asserts containment on the resolved value. Neither control is redundant with the other. A path-only check would be decorative here regardless: reconcileOne inserts status, includesUploads, databaseDumpSha256 and uploadsArchiveBytes verbatim from an operator-editable manifest.json, so the same actor supplies both the symlink and the integrity values that would otherwise constrain it. The residual — a directory component swapped between the realpath and the open — is accepted with its reason and recorded, since closing it needs a component-wise openat walk Node does not expose.
• An admin route cannot ship without a permission, and this is enforced twice. RoleGuard returns true when a handler declares none, which is correct for the customer surfaces that legitimately have none — and means an admin handler shipped without @Permissions was reachable by any logged-in retail customer, since customer sessions authenticate through the same JwtAuthGuard. The guard now denies that case for admin surfaces, selected from the controller's own PATH_METADATA (array declarations, leading slashes and a controller merely named administration are all handled). A structure spec enforces the same property in CI over Nest's real reflected route table, and additionally checks that RoleGuard is present in the resolved guard chain — @Permissions is inert without it, and a decoration that reads as enforcement is worse than a missing one. A @Public() handler under admin/ must be explicitly allowlisted rather than passing automatically, because @Public() removes the global JwtAuthGuard and is strictly weaker than a missing permission. Both mechanisms are mutation-tested; the two exemption lists are deliberately duplicated rather than shared, and a test fails if they drift.
• Path containment: every filesystem path this module ever touches is either constructed by BackupPathService itself or re-validated through resolveWithinRoot before use — including values read back out of an operator-editable manifest.json or settings.json.
• Subprocess argv discipline: every spawn call uses an argv array (never a shell string); credentials go in the child's environment (PGPASSWORD), never argv, which is world-readable via ps; every binary path is absolute and env-configured, since spawn resolves a bare command through PATH at call time.
• rsync/ssh transport: --secluded-args (asserted at boot) plus a recomputed remote path (never artifact_dir) — without both, an operator-editable string reaches the remote login shell. StrictHostKeyChecking=yes against a boot-time-validated known_hosts file replaces trust-on-first-use on the channel carrying the whole database.
• Stderr redaction: sanitizeSubprocessExcerpt strips paths, user@host, IPs and tab-delimited COPY data rows before anything reaches a database column or an HTTP response; raw text still reaches the server log only.
• Filename injection: the downloaded file's Content-Disposition filename is derived from publicId alone, never from the operator-editable note field, which would otherwise be a CRLF-injection vector into the response header.
• Superuser fence check: assertApplicationRoleIsNotSuperuser (preflight) and restore-runner.ts's own ALTER ROLE ... CONNECTION LIMIT fence — PostgreSQL does not enforce rolconnlimit against a superuser role, so the connection fence would silently do nothing if the application connected as one; the preflight check is what actually stops that path before a restore is ever accepted.
• Maintenance guard fail-closed: deny-by-default — a handler is exempt only via the literal-path allowlist (MAINTENANCE_EXEMPT_PATHS), the /api/admin/system/restores/ prefix (MAINTENANCE_EXEMPT_PATH_PREFIXES), or by being non-@Public() with a non-empty @Permissions(...); every other route is blocked while the lock is engaged, closing the gap an earlier prefix-based design left open for public callbacks. The lock is disengaged only by BackupRestoreProcessor.finish on success, by force-clear, or by an operator's release — never by BackupRestoreProcessor.fail, and never by the runner itself, since the runner has no access to the app's own state once the fence is in place.
• A password step-up on every key mutation: generate, import, rotate and prune each re-check the operator's password before acting. A session token proves the session, not the person, and these four are the operations that hand out or destroy key material. The limiter for them is user-keyed (ADMIN_KEY_MATERIAL_WRITE, 5 per 15 min), never IP-keyed — an IP-keyed cap on a password check is bypassed by rotating IPs, and an IP-keyed lockout is a denial of service against the operator.
• Key material appears in exactly two responses, both at creation time (generate, rotate). There is no endpoint that returns an existing key, and neither route carries @IdempotentCreate — IdempotencyInterceptor persists responseJson to Redis for 24 hours, which would park a plaintext signing key in a store an operator may snapshot.
• The forwarded client address is not a rate-limit input. TrustedClientIpInterceptor accepts x-internal-client-ip only alongside a matching INTERNAL_CLIENT_IP_TOKEN, and the resulting address reaches the activity log and nothing else. Wiring it into IpThrottlerGuard was considered and rejected: it would let an unauthenticated attacker choose their own bucket on the admin credential path, which is a brute-force bypass, not an improvement. Trust is bound to the token rather than to Express's numeric trust proxy because a hop count does not bind to the peer at all — measured against Express 5.2.1, req.ip becomes whatever the caller sent.
• Two download routes, one permission, two rate-limit buckets. BackupDownload_READ covers both the database dump and the uploads archive: an actor holding it can already stream the entire database, so the uploads archive is a strictly smaller disclosure inside the same trust boundary. They do NOT share a budget — IpThrottlerGuard keys on `throttle:ip:${ControllerClass}:${handlerName}:${principal}`, so the constant names a policy and the handler is the bucket. Per-operator capacity is 3+3 per hour, and refusals consume it because the guard runs before the handler.
• A missing @Permissions decorator is a hole, not a default. RoleGuard returns true when no permission is declared, and customer sessions pass the same JwtAuthGuard, so an admin handler without the decorator is reachable by any logged-in customer. pnpm rules has no permission auditor and the structure snapshot is a change detector, so test/structure/backup-download-permissions.spec.ts asserts the expected value for these two routes rather than the expected diff.
• Sensitive fields never in a response: artifactDir (a filesystem path), checksums, both integer PKs, and key material never appear in BackupResponseDto, RestoreLiveStateDto or BackupKeyStateResponseDto.

13. Error Handling

Every code in this table has a throw site. BACKUP_RESTORE_EXTENSION_NOT_OWNED was previously declared with none — reserved for extension-ownership verification that was never implemented — and has been removed. pnpm rules:error-codes checks registration, not use, so a code that is validated and unreachable looks load-bearing and is not; the gate cannot see it, and a reader cannot tell the difference without grepping.

13a. Audit Retention

audit_logs carried one collection-wide 90-day TTL on createdAt, for every module. The record of which backup artifact left this host is the answer to the only question worth asking after a disclosure incident, and those investigations routinely begin later than ninety days.

Retention is now per document. expiresAt carries a TTL index with expireAfterSeconds: 0, stamped by a pre("validate") hook on the schema so a writer cannot omit it.

Scoped by action, not by module. The long window applies to actions beginning backup.download — which covers both download routes and both of their .completed / .failed companions. Scoping by module: "backup" would have been wrong: seven sites carry that tag, including the key-custody service, which passes metadata through wholesale with signing-key fingerprints in it. Two years is justified for "which artifact left this host", not for everything the module happens to log.

Both are read in apps/api and injected into @skoolsewa/mongodb; the schema package never reads process.env. That is not stylistic — rules:env-orphans scans apps/api only, so a variable consumed in the package would be declared-and-unread and fail the gate.

Why the old index had to be deleted from the schema, not just dropped

A document is removed on the earliest deadline any TTL index asserts — measured: a document with expiresAt a year in the future was deleted by a createdAt TTL. So the old index makes the new field inert.

Dropping it at runtime is not enough on its own. createMongoConnection sets autoIndex: true, so mongoose rebuilds every declared index at model init: leaving the declaration in place would bring the index straight back, on that boot or the next, and the whole change would be a no-op with every gate green. The declaration is deleted; the runtime dropIndex exists only to clean up servers that already hold it on disk.

The cost of that, stated: the old index did not care what a document contained, so a row written without expiresAt still died at 90 days. Such a row is now retained forever. Every writer today goes through AuditLog.create(), so the hook fires; a future writer using collection.insertOne or an upsert without setDefaultsOnInsert would bypass mongoose middleware entirely. The guarantee is "every writer that goes through the model", not "every writer".

AuditLogRetentionMigration

An OnModuleInit provider in MongoDBModule, ordered so a crash at any point leaves the old index in place — nothing is ever retained for less time than it is today:

1. create the expiresAt index
2. verify it exists, and abort if not
3. backfill expiresAt = createdAt + retention(action) in bounded batches
4. only then drop createdAt_1, tolerating IndexNotFound for the concurrent-boot race

Model.syncIndexes() is the obvious instrument and is wrong twice over: it calls cleanIndexes before createIndexes, so it passes through a state with no TTL index at all, and it drops every index absent from the schema rather than a named one.

The backfill pages on an _id cursor. Using modifiedCount === 0 as the terminator works only while the filter is { expiresAt: { $exists: false } }; on a re-stamp it returns the same first batch forever, and a version that did so stamped 5,000 of 12,000 rows and dropped the index anyway. An incomplete backfill now skips the drop and does not write the marker, so the next boot resumes.

A marker document in its own audit_retention_state collection holds a hash of the retention configuration. An unchanged boot is a no-op; a changed one re-stamps every row, which is what keeps a retention reduction effective rather than forward-only. The marker is deliberately not in audit_logs — it would be subject to the very TTL it governs.

14. Observability

15. Testing and Validation

Validation commands: pnpm --filter api test (unit), pnpm --filter @skoolsewa/api test:structure to regenerate structure.baseline.json after a route change, pnpm --filter @skoolsewa/api permissions:sync after adding a permission module.

16. Mandatory Backend Deep-Dive Pack

16.1 Submodule Coverage Matrix

16.2 UML and Architecture Diagram Pack

16.3 Code Flow Narrative

See Section 7 for the full per-flow narrative and branch table — flows 7.1 (manual backup), 7.2 (scheduled backup), 7.5 (restore request), 7.8 (download) and 7.9 (replication) each include a numbered step table with code location, behavior and failure case, satisfying this section's requirement without duplicating it here.

16.4 Data Layer Deep Dive

Covered in full in Section 5. Money units: not applicable — this module has no monetary fields. Timezone: BACKUP_SCHEDULE_TIMEZONE (default Asia/Kathmandu, UTC+05:45) governs GFS tier-boundary computation only; every stored timestamp is timestamptz. JSON schema examples: BackupManifest ({tables: Record<string, number>, postgresVersion, schemaMigrationTag, capturedAt}), BackupRestoreVerification ({tablesChecked, tablesMatched, mismatches[], excluded[]} — declared, never populated by any current code path).

Index rationale:

16.5 Business Logic and Invariant Catalog

16.6 Tradeoffs, Alternatives, and ADR Notes

16.7 Operational Runbook

16.8 Backend Risk Register

17. Zero-Omission Backend Checklist

• Every file in the module directory is represented or explicitly marked non-runtime.
• Every controller, service, provider, processor, scheduler, helper, mapper, DTO, enum, and schema is documented.
• Every method with business behavior has a code-flow narrative (Section 6/7).
• Every table/collection/cache object/job payload has field-level detail.
• Every index, constraint, relation, and delete behavior has rationale.
• Every lifecycle/status transition is covered (Section 5, 7; full state diagram in the features/flows doc).
• Every read/write/action/job flow has a sequence diagram and branch notes.
• Every business invariant is cataloged.
• Every cache key, invalidation path, queue job, and external call is documented.
• Every architectural tradeoff is documented with alternatives and revisit triggers.
• Every operational failure mode has a runbook entry, including the still-missing safety-dump/restore linkage and the row-count-only scope of post-restore verification.

18. Backend Completion Checklist

• Module boundaries are documented.
• Every controller, service, DTO, schema file, job, cache key, and event is covered.
• Every database table has a field table and relationship diagram.
• Every runtime flow has a diagram and branch notes.
• API, feature/flows, and TDD docs are linked.
• No claim is made without a source file or documented source reference — the safety dump and row-count verification that now exist, and the linkage/content-verification gaps that remain, are both stated explicitly rather than assumed or invented.

See Also

• API doc: /docs/developer/backup/api
• Features and flows doc: /docs/developer/backup/feature
• TDD: not present for this module at time of writing.