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).
• A site-wide maintenance kill switch (MaintenanceService, MaintenanceGuard) that blocks customer traffic while engaged, enforced globally via APP_GUARD.
• 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

• MongoDB backup/restore. The restore acknowledgement text (RestoreBackupDto.acknowledgeMongoNotRestored) states this explicitly: only PostgreSQL is in scope.
• 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-admin.module.ts
      dto/restore.dto.ts
    maintenance/
      maintenance-admin.controller.ts
      maintenance-admin.service.ts
      maintenance-admin.module.ts
      dto/maintenance.dto.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)
  customer/
    maintenance-customer.controller.ts (GET /system/maintenance — @Public())
    maintenance-customer.service.ts  (maps shared state onto the narrower public DTO)
    maintenance-customer.module.ts
    dto/maintenance-status.dto.ts
  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.

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 → schema migration tag matches the running application's → 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."

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.

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.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 dumpTimeoutMinutes + 5 as failed (using idx_backup_run_stalled), and 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. 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, neutralises the outbox, 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.
6. Heartbeats restore-state.json every 5 seconds (stage, runnerPid, heartbeatAt) throughout, so GET .../live can show real progress.
7. 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 two error codes by which stage was executing at the moment of the throw — 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. Both are read at the throw site in restore-runner.ts's main() catch block, not guessed from behavior.

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>. If that update matches no row, 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.

7.6 Force-clear a stuck restore

POST admin/system/restores/{publicId}/force-clear marks the row failed (with BACKUP_RESTORE_ABANDONED) and disengages maintenance — recovery from a runner that never spawned, was killed, or is still running but an operator wants out regardless. It does not check that the row is actually stuck (no age threshold) and does not stop a still-running pg_restore process — it only clears the row and maintenance flag, so calling it while restore-runner.ts is genuinely mid-restore leaves the subprocess to finish or fail on its own with nothing left tracking it.

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):

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 exception is MaintenanceCustomerController (GET /system/maintenance), which carries only IpThrottlerGuard and is explicitly @Public() — the single public/guest-reachable route in this module.
• The public maintenance-status route is deliberately narrow: MaintenanceCustomerService maps the shared maintenance state onto MaintenanceStatusDto, which carries only active and reason — never engagedBy (an operator's display name) or engagedAt (which would tell an unauthenticated caller exactly how long the database has been offline, the one fact that distinguishes a routine restore from an incident). It is also the one route named literally in MAINTENANCE_EXEMPT_PATHS (/api/system/maintenance) precisely because it must stay answerable through the same maintenance window it reports on — that is not a hole, since it discloses nothing beyond what every other route already shows by answering 503.
• Permissions: Backup_READ/CREATE/UPDATE/DELETE/RESTORE, BackupDownload_READ, BackupConfigure_UPDATE, BackupUpload_CREATE, BackupKey_READ/CREATE/UPDATE/DELETE, System_READ/UPDATE (maintenance). 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, 10/min IP-keyed), 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 an explicit literal-path allowlist or by being admin-authenticated; every other @Public() route is blocked while maintenance is engaged, closing the gap an earlier prefix-based design left open for payment gateway callbacks. Maintenance is disengaged only by BackupRestoreProcessor.finish/fail or force-clear — never automatically by the runner itself, since the runner has no database 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.