Data retention: purge expired auth rows, audit retention and client deletion #8

Closed
opened 2026-10-15 02:42:16 +00:00 by ben · 8 comments
Owner

Define and implement data retention once the practice confirms its retention policy.

Today nothing is purged automatically:

  • expired auth_sessions and login_tokens rows;
  • the audit log, including staff access history;
  • client accounts and their card-sort sessions, which have no deletion path.

The mail outbox is the exception: it already purges after 30 days.

Scope (to be confirmed with the practice)

  • A scheduled purge of expired auth sessions and login tokens. This has no policy dependency.
  • A retention period for access-audit rows, if the practice's policy sets one.
  • Client deletion or anonymization on request, with a backup and an audit record, and a documented owner workflow.
  • Docs: a retention section in docs/self-hosting.md and the privacy checklist.

Constraints

  • Content-mutation audit rows must never be purged: dashboard progress (#4) is derived from them. Any audit purge must exclude them, or progress must first move to its own store.
  • Privacy obligations (e.g. Alberta PIPA) set the minimum and maximum periods. Confirm with the practice before choosing defaults.

Acceptance

  • Expired auth rows are purged on a schedule, with tests.
  • Client deletion removes or anonymizes personal data without breaking other clients' progress or the audit trail it must keep.

Depends on: the practice's retention policy, for everything except the auth-row purge.

Define and implement data retention once the practice confirms its retention policy. **Today nothing is purged automatically:** - expired `auth_sessions` and `login_tokens` rows; - the audit log, including staff access history; - client accounts and their card-sort sessions, which have no deletion path. The mail outbox is the exception: it already purges after 30 days. **Scope (to be confirmed with the practice)** - A scheduled purge of expired auth sessions and login tokens. This has no policy dependency. - A retention period for access-audit rows, if the practice's policy sets one. - Client deletion or anonymization on request, with a backup and an audit record, and a documented owner workflow. - Docs: a retention section in `docs/self-hosting.md` and the privacy checklist. **Constraints** - Content-mutation audit rows must **never** be purged: dashboard progress (#4) is derived from them. Any audit purge must exclude them, or progress must first move to its own store. - Privacy obligations (e.g. Alberta PIPA) set the minimum and maximum periods. Confirm with the practice before choosing defaults. **Acceptance** - Expired auth rows are purged on a schedule, with tests. - Client deletion removes or anonymizes personal data without breaking other clients' progress or the audit trail it must keep. Depends on: the practice's retention policy, for everything except the auth-row purge.
Author
Owner

The auth-row purge landed in 16705eb on main. Not deployed yet; deploying is a normal rebuild with no migration.

  • What it deletes:
    • Sign-in sessions that authentication rejects: past 4 hours idle or 7 days old, or belonging to a disabled user.
    • Sign-in links that can no longer be used and that no outbox row references, so within about 30 days.
  • What it keeps: each client's unused invite, because the dashboard's "expired" status reads it.
  • When it runs: at startup and then hourly (server/retention.js). Session and link validity come from shared predicates in server/auth-validity.js, which authentication uses too.
  • Behaviour change: consume and the mail worker now reject a link with a malformed expiry.
  • Verification:
    • Tests in server/test/retention.test.js cover time boundaries, equivalence with real requests, refresh/consume/issuance ordering, SSE closure after commit, the token × outbox matrix, dashboard invite status, the scheduler lifecycle and shutdown.
    • The full server suite passes (1190).
    • High review: codex, qwen and deepseek (3/3).
  • Plan: docs/plans/2026-10-14-21-34_purge-expired-auth-rows.md (done).
  • Docs: the retention section of docs/self-hosting.md.

Left on this issue:

  • Access-audit retention and client deletion. Both wait on the practice's retention policy.
  • When client deletion is built, derive invite status from the audit log so expired invites can go too.
  • An index on outbox(token_hash), deferred because it needs a migration.
The auth-row purge landed in 16705eb on `main`. Not deployed yet; deploying is a normal rebuild with no migration. - **What it deletes:** - Sign-in sessions that authentication rejects: past 4 hours idle or 7 days old, or belonging to a disabled user. - Sign-in links that can no longer be used and that no outbox row references, so within about 30 days. - **What it keeps:** each client's unused invite, because the dashboard's "expired" status reads it. - **When it runs:** at startup and then hourly (`server/retention.js`). Session and link validity come from shared predicates in `server/auth-validity.js`, which authentication uses too. - **Behaviour change:** consume and the mail worker now reject a link with a malformed expiry. - **Verification:** - Tests in `server/test/retention.test.js` cover time boundaries, equivalence with real requests, refresh/consume/issuance ordering, SSE closure after commit, the token × outbox matrix, dashboard invite status, the scheduler lifecycle and shutdown. - The full server suite passes (1190). - High review: codex, qwen and deepseek (3/3). - **Plan:** `docs/plans/2026-10-14-21-34_purge-expired-auth-rows.md` (done). - **Docs:** the retention section of `docs/self-hosting.md`. **Left on this issue:** - Access-audit retention and client deletion. Both wait on the practice's retention policy. - When client deletion is built, derive invite status from the audit log so expired invites can go too. - An index on `outbox(token_hash)`, deferred because it needs a migration.
Author
Owner

The auth-row purge (16705eb) is deployed, live at 13541d6, schema v5, no migration. On startup the live server purged 1 dead sign-in session and 2 spent sign-in links. Smoke checks passed: healthz ok, signed-out requests get 401, the container is healthy, and the mail tunnel is active.

Still open on this issue:

  • access-audit retention;
  • client deletion;
  • an index on outbox(token_hash).

The first two wait on the practice's retention policy.

The auth-row purge (16705eb) is deployed, live at 13541d6, schema v5, no migration. On startup the live server purged 1 dead sign-in session and 2 spent sign-in links. Smoke checks passed: healthz ok, signed-out requests get 401, the container is healthy, and the mail tunnel is active. Still open on this issue: - access-audit retention; - client deletion; - an index on `outbox(token_hash)`. The first two wait on the practice's retention policy.
Author
Owner

Decisions (maintainer, 2026-10-15):

  • Client deletion: hard delete. On request, remove the client's account, sessions, notes and flags. Any audit rows kept refer to the client only by an opaque id.
  • Access-audit retention: a fixed period that follows Canadian privacy law. Before choosing the period, confirm what Alberta PIPA and PIPEDA require. The practice's professional-records obligations may also matter.

Constraint still in force: content-mutation audit rows drive dashboard progress, so a purge must keep them, or progress must move to its own store first. A hard delete removes those rows only together with the client's sessions.

Index: the outbox(token_hash) index is filed separately.

**Decisions (maintainer, 2026-10-15):** - **Client deletion:** hard delete. On request, remove the client's account, sessions, notes and flags. Any audit rows kept refer to the client only by an opaque id. - **Access-audit retention:** a fixed period that follows Canadian privacy law. Before choosing the period, confirm what Alberta PIPA and PIPEDA require. The practice's professional-records obligations may also matter. **Constraint still in force:** content-mutation audit rows drive dashboard progress, so a purge must keep them, or progress must move to its own store first. A hard delete removes those rows only together with the client's sessions. **Index:** the `outbox(token_hash)` index is filed separately.
Author
Owner

Access-audit retention research. This is not legal advice; counsel should confirm.

Finding: no Alberta or federal statute, regulation or regulator guidance sets a retention period for staff access logs. The period rests on PIPA s.35(1): keep information "only for as long as the organization reasonably requires" it for legal or business purposes, then destroy it or make it non-identifying within a reasonable time (s.35(2)).

What applies:

  • Alberta PIPA (SA 2003 c P-6.5) is the governing law for an Alberta private practice.
    • It has no minimum retention period. The often-quoted "at least one year after use" rule is in the public-sector FOIP Act s.35, not PIPA.
    • s.59(1)(c) makes it an offence to destroy a record about the use or disclosure of personal information after a s.24 access request is received, or when one is reasonably likely. Any purge therefore needs a legal hold.
    • s.49.2 requires records relating to a Commissioner investigation to be kept for one year after it ends.
  • PIPEDA: the Alberta exemption order (SOR/2004-219) exempts PIPA-covered activity within Alberta. It may still reach data flows that cross provincial borders, such as hosting or processors outside Alberta. Its 24-month breach-record rule (Breach of Security Safeguards Regulations s.6(1)) is a useful benchmark, not a binding rule here. Alberta's PIPA Regulation has no breach record-keeping duty.
  • Health Information Act: almost certainly doesn't apply. Custodians are the designated colleges in AR 106/2026, which don't include counsellors, psychologists or social workers.
  • Professional standards set periods for clinical client records, not access logs, and only for members:
    • CCPA: 7 years after last service.
    • College of Alberta Psychologists: 10 years after last service; s.7.6.5 requires audit logging but sets no period.
    • ACSW: 10 years after the last entry.
    • Career counselling and counselling therapy are unregulated in Alberta.
  • IP addresses are likely personal information (PIPA s.1(1)(k)). The logged IPs are staff IPs. Minimization applies (s.11(2) and s.16(2)).

Recommended defaults (configurable):

  • Access-audit rows: 24 months from the access event, rolling. This matches the Limitations Act s.3(1)(a) two-year period and the PIPEDA 24-month benchmark, and covers the one-year Commissioner windows. Ten years would be hard to justify under s.35 for staff metadata.
  • IP addresses: blank after 90 days. This is engineering judgement, not a statutory figure.
  • Legal hold: no purging for a client with an open access request, complaint, Commissioner investigation (plus one year) or breach investigation.
  • Hard deletion: access rows run to expiry, keyed to an opaque id that no longer resolves to anyone (supported by s.35(3)), and the privacy notice says so. Content-mutation rows go with the deleted sessions, per the decision above.

For the practice to confirm with counsel:

  1. That 24 months is reasonable under s.35.
  2. Whether any staff member belongs to CCPA, CAP or ACSW. If so, ask whether the log should follow the 7- or 10-year client-record periods.
  3. Whether PIPEDA reaches hosting outside Alberta.
  4. The approach of keeping access rows after deletion.

Sources (primary text unless noted):

Next: the plan for audit retention plus hard client deletion, using these defaults. Nothing has been committed for it yet.

**Access-audit retention research.** This is not legal advice; counsel should confirm. **Finding:** no Alberta or federal statute, regulation or regulator guidance sets a retention period for staff access logs. The period rests on PIPA s.35(1): keep information "only for as long as the organization reasonably requires" it for legal or business purposes, then destroy it or make it non-identifying within a reasonable time (s.35(2)). **What applies:** - **Alberta PIPA** (SA 2003 c P-6.5) is the governing law for an Alberta private practice. - It has no minimum retention period. The often-quoted "at least one year after use" rule is in the public-sector FOIP Act s.35, not PIPA. - s.59(1)(c) makes it an offence to destroy a record about the use or disclosure of personal information after a s.24 access request is received, or when one is reasonably likely. Any purge therefore needs a **legal hold**. - s.49.2 requires records relating to a Commissioner investigation to be kept for one year after it ends. - **PIPEDA:** the Alberta exemption order (SOR/2004-219) exempts PIPA-covered activity within Alberta. It may still reach data flows that cross provincial borders, such as hosting or processors outside Alberta. Its 24-month breach-record rule (Breach of Security Safeguards Regulations s.6(1)) is a useful benchmark, not a binding rule here. Alberta's PIPA Regulation has no breach record-keeping duty. - **Health Information Act:** almost certainly doesn't apply. Custodians are the designated colleges in AR 106/2026, which don't include counsellors, psychologists or social workers. - **Professional standards** set periods for clinical client records, not access logs, and only for members: - CCPA: 7 years after last service. - College of Alberta Psychologists: 10 years after last service; s.7.6.5 requires audit logging but sets no period. - ACSW: 10 years after the last entry. - Career counselling and counselling therapy are unregulated in Alberta. - **IP addresses** are likely personal information (PIPA s.1(1)(k)). The logged IPs are staff IPs. Minimization applies (s.11(2) and s.16(2)). **Recommended defaults** (configurable): - **Access-audit rows: 24 months from the access event, rolling.** This matches the Limitations Act s.3(1)(a) two-year period and the PIPEDA 24-month benchmark, and covers the one-year Commissioner windows. Ten years would be hard to justify under s.35 for staff metadata. - **IP addresses: blank after 90 days.** This is engineering judgement, not a statutory figure. - **Legal hold:** no purging for a client with an open access request, complaint, Commissioner investigation (plus one year) or breach investigation. - **Hard deletion:** access rows run to expiry, keyed to an opaque id that no longer resolves to anyone (supported by s.35(3)), and the privacy notice says so. Content-mutation rows go with the deleted sessions, per the decision above. **For the practice to confirm with counsel:** 1. That 24 months is reasonable under s.35. 2. Whether any staff member belongs to CCPA, CAP or ACSW. If so, ask whether the log should follow the 7- or 10-year client-record periods. 3. Whether PIPEDA reaches hosting outside Alberta. 4. The approach of keeping access rows after deletion. **Sources** (primary text unless noted): - Statutes and regulations: [PIPA](https://kings-printer.alberta.ca/documents/Acts/P06P5.pdf), [PIPA Regulation](https://kings-printer.alberta.ca/documents/Regs/2003_366.pdf), [HIA](https://kings-printer.alberta.ca/documents/Acts/H05.pdf), [AR 106/2026](https://kings-printer.alberta.ca/documents/Regs/2026_106.pdf), [Limitations Act](https://kings-printer.alberta.ca/documents/Acts/L12.pdf), [PIPEDA Sch. 1](https://laws-lois.justice.gc.ca/eng/acts/P-8.6/page-7.html), [SOR/2018-64](https://laws-lois.justice.gc.ca/eng/regulations/SOR-2018-64/FullText.html), [SOR/2004-219](https://laws-lois.justice.gc.ca/eng/regulations/SOR-2004-219/FullText.html) - Guidance: [OPC retention guidance](https://www.priv.gc.ca/en/privacy-topics/business-privacy/breaches-and-safeguards/safeguarding-personal-information/gd_rd_201406/), [OIPC PIPA guide](https://www.oipc.ab.ca/wp-content/uploads/2022/02/PIPA-Guide-2008.pdf) - Professional standards: [CCPA](https://www.ccpa-accp.ca/wp-content/uploads/2021/10/CCPA-Standards-of-Practice-ENG-Sept-29-Web-file.pdf), [CAP](https://www.cap.ab.ca/Portals/0/adam/Content/PCibGBBnCE6ZY6pd7EKcqQ/Link/Standards%20of%20Practice%20(May%2031,%202023).pdf), [ACSW](https://acsw.ab.ca/wp-content/uploads/2025/05/ACSW-Standards-of-Practice-2023.pdf) **Next:** the plan for audit retention plus hard client deletion, using these defaults. Nothing has been committed for it yet.
Author
Owner

Decisions (maintainer, 2026-10-15):

  • Legal hold: a per-client hold flag that the owner sets on People, with a reason, and audited. A held client's audit rows are not purged and the client cannot be deleted. Separately, an environment setting pauses all audit purging practice-wide, for example during a breach investigation.
  • Deleting a client: the owner confirms by typing the client's email address. Deletion is immediate and cannot be undone; there is no grace period. Backups are the only recovery, so the docs will say that deletion is complete only once older backups have been rotated out.
  • Who can delete: the owner only.
**Decisions (maintainer, 2026-10-15):** - **Legal hold:** a per-client hold flag that the owner sets on People, with a reason, and audited. A held client's audit rows are not purged and the client cannot be deleted. Separately, an environment setting pauses all audit purging practice-wide, for example during a breach investigation. - **Deleting a client:** the owner confirms by typing the client's email address. Deletion is immediate and cannot be undone; there is no grace period. Backups are the only recovery, so the docs will say that deletion is complete only once older backups have been rotated out. - **Who can delete:** the owner only.
Author
Owner

The plan for the rest of this issue landed in a0f5627: docs/plans/2026-10-15-10-58_audit-retention-hold-client-deletion.md (status: draft). It was reviewed in a High consult with codex and glm (2/2); its Review notes record every point and how it was resolved. No implementation code has been committed yet.

Approach:

  • Audit retention: an explicit allowlist of actions is purged 730 days after the event, and IPs on every row are blanked after 90 days. Rows with an unknown action or a malformed timestamp are kept and logged. The dashboard's progress actions are unchanged, and activity.finished is also retained. The settings are AUDIT_RETENTION_DAYS, AUDIT_IP_RETENTION_DAYS and RETENTION_PAUSED.
  • Legal hold: a legal_holds table, with owner-only PUT and DELETE /api/users/:id/hold. For a held client, the hold suspends the audit purge, IP blanking, and the auth-row and outbox purges. While any hold exists, unattributable audit rows are kept. A held client can't be deleted or have their role changed. The docs will ask for an evidence snapshot when a hold is set.
  • Hard delete: POST /api/users/:id/delete {email} (owner only), plus cli.js delete-client for re-applying a deletion after a restore. It runs in one immediate transaction, with an FK check before commit. Staff access rows are kept, with session_id set to NULL so they refer only to UUIDs that no longer resolve. The deletion writes a client.delete audit row and closes the affected streams. Accounts with staff history are refused. secure_delete is turned on, and the migration runs a one-time VACUUM. There is no automatic backup before a deletion; the docs cover backup age and the restore procedure.
  • Migration v6: legal_holds, plus the indexes outbox_token_hash (#13), audit_at and audit_actor.

Left: implementing the seven slices, then a High code review, a migration dry run on a copy of the live database, and deploying when the maintainer asks. The counsel questions are listed in the plan and don't block the work.

The plan for the rest of this issue landed in a0f5627: `docs/plans/2026-10-15-10-58_audit-retention-hold-client-deletion.md` (status: draft). It was reviewed in a High consult with codex and glm (2/2); its Review notes record every point and how it was resolved. No implementation code has been committed yet. **Approach:** - **Audit retention:** an explicit allowlist of actions is purged 730 days after the event, and IPs on every row are blanked after 90 days. Rows with an unknown action or a malformed timestamp are kept and logged. The dashboard's progress actions are unchanged, and `activity.finished` is also retained. The settings are `AUDIT_RETENTION_DAYS`, `AUDIT_IP_RETENTION_DAYS` and `RETENTION_PAUSED`. - **Legal hold:** a `legal_holds` table, with owner-only `PUT` and `DELETE /api/users/:id/hold`. For a held client, the hold suspends the audit purge, IP blanking, and the auth-row and outbox purges. While any hold exists, unattributable audit rows are kept. A held client can't be deleted or have their role changed. The docs will ask for an evidence snapshot when a hold is set. - **Hard delete:** `POST /api/users/:id/delete {email}` (owner only), plus `cli.js delete-client` for re-applying a deletion after a restore. It runs in one immediate transaction, with an FK check before commit. Staff access rows are kept, with `session_id` set to NULL so they refer only to UUIDs that no longer resolve. The deletion writes a `client.delete` audit row and closes the affected streams. Accounts with staff history are refused. `secure_delete` is turned on, and the migration runs a one-time `VACUUM`. There is no automatic backup before a deletion; the docs cover backup age and the restore procedure. - **Migration v6:** `legal_holds`, plus the indexes `outbox_token_hash` (#13), `audit_at` and `audit_actor`. **Left:** implementing the seven slices, then a High code review, a migration dry run on a copy of the live database, and deploying when the maintainer asks. The counsel questions are listed in the plan and don't block the work.
Author
Owner

Implemented in 8754704 on main. Not deployed yet. Deploying needs migration v6, which takes an automatic backup and runs a one-time VACUUM.

Audit retention:

  • Rows whose action is on the PURGEABLE_ACTIONS allowlist (server/audit-classes.js) are deleted 730 days after the event (AUDIT_RETENTION_DAYS).
  • Every row's IP is blanked after 90 days (AUDIT_IP_RETENTION_DAYS).
  • Progress and activity.finished rows stay with their session.
  • Unlisted actions and malformed timestamps are kept and logged.
  • RETENTION_PAUSED=true suspends every retention purge.
  • The purger runs at startup and then hourly (server/retention.js createRetentionPurger), and ends each run with a WAL checkpoint.

Legal hold:

  • The owner sets or releases a hold on People: PUT and DELETE /api/users/:id/hold. Each change is audited; the reason is stored only in legal_holds.
  • A held client's audit, sign-in and mail rows are exempt from every purge. While any hold exists, rows that can't be tied to a client are kept.
  • A held client can't be deleted or have their role changed.

Hard client deletion:

  • Owner-only: POST /api/users/:id/delete with the typed email, or cli.js delete-client for re-applying deletions after a restore. cli.js release-hold supports the same procedure.
  • One immediate transaction removes the account, sessions, content, sign-in rows, mail, counsellor links and the client's own audit trail.
  • Staff access and lifecycle rows are kept; they refer only to UUIDs that no longer resolve.
  • A client.delete row is written, and the affected streams close.
  • The deletion is refused (409) for:
    • a held client;
    • an account with staff history;
    • rows linked to another client's hold.
  • secure_delete and journal_size_limit = 0 on every connection, plus checkpoints, keep deleted data out of the file and the WAL.

Docs:

  • docs/self-hosting.md §2, §5, §9, §10 and §11: the privacy checklist, settings, deleting and holds, and backups with the restore procedure;
  • docs/api.md;
  • the v6 release note in docs/deployment.md.

Verification:

  • Server tests pass (1351), including new suites for audit classes, audit retention, legal holds and client deletion. The deletion suite checks:
    • a schema-driven FK sweep;
    • a text sweep and a raw byte scan of the database and WAL;
    • that refusals leave every table byte-identical;
    • stream closure for every role;
    • the in-flight mail lease;
    • lock exclusion and rollback.
  • Web: tsc is clean, 643 unit tests pass, coverage is at least 85% per file, and Playwright passes 10 with 1 skipped, including a new People journey (hold, release, delete).
  • High review: codex and deepseek (2/2), then a full-change re-review by both, then a clean codex delta round. Every finding was fixed.
  • Plan: docs/plans/2026-10-15-10-58_audit-retention-hold-client-deletion.md. 7/7 slices and 21/22 acceptance items have tests.

Left:

  • The migration dry run on a copy of the live database, after which the plan closes. It's the remaining acceptance item.
  • Deploying when the maintainer asks.
  • The counsel questions listed in the plan and docs/self-hosting.md §2. They don't block the work.

This issue closes once v6 is deployed and verified.

Implemented in 8754704 on `main`. Not deployed yet. Deploying needs migration v6, which takes an automatic backup and runs a one-time VACUUM. **Audit retention:** - Rows whose action is on the `PURGEABLE_ACTIONS` allowlist (`server/audit-classes.js`) are deleted 730 days after the event (`AUDIT_RETENTION_DAYS`). - Every row's IP is blanked after 90 days (`AUDIT_IP_RETENTION_DAYS`). - Progress and `activity.finished` rows stay with their session. - Unlisted actions and malformed timestamps are kept and logged. - `RETENTION_PAUSED=true` suspends every retention purge. - The purger runs at startup and then hourly (`server/retention.js` `createRetentionPurger`), and ends each run with a WAL checkpoint. **Legal hold:** - The owner sets or releases a hold on People: `PUT` and `DELETE /api/users/:id/hold`. Each change is audited; the reason is stored only in `legal_holds`. - A held client's audit, sign-in and mail rows are exempt from every purge. While any hold exists, rows that can't be tied to a client are kept. - A held client can't be deleted or have their role changed. **Hard client deletion:** - Owner-only: `POST /api/users/:id/delete` with the typed email, or `cli.js delete-client` for re-applying deletions after a restore. `cli.js release-hold` supports the same procedure. - One immediate transaction removes the account, sessions, content, sign-in rows, mail, counsellor links and the client's own audit trail. - Staff access and lifecycle rows are kept; they refer only to UUIDs that no longer resolve. - A `client.delete` row is written, and the affected streams close. - The deletion is refused (409) for: - a held client; - an account with staff history; - rows linked to another client's hold. - `secure_delete` and `journal_size_limit = 0` on every connection, plus checkpoints, keep deleted data out of the file and the WAL. **Docs:** - `docs/self-hosting.md` §2, §5, §9, §10 and §11: the privacy checklist, settings, deleting and holds, and backups with the restore procedure; - `docs/api.md`; - the v6 release note in `docs/deployment.md`. **Verification:** - Server tests pass (1351), including new suites for audit classes, audit retention, legal holds and client deletion. The deletion suite checks: - a schema-driven FK sweep; - a text sweep and a raw byte scan of the database and WAL; - that refusals leave every table byte-identical; - stream closure for every role; - the in-flight mail lease; - lock exclusion and rollback. - Web: `tsc` is clean, 643 unit tests pass, coverage is at least 85% per file, and Playwright passes 10 with 1 skipped, including a new People journey (hold, release, delete). - High review: codex and deepseek (2/2), then a full-change re-review by both, then a clean codex delta round. Every finding was fixed. - Plan: `docs/plans/2026-10-15-10-58_audit-retention-hold-client-deletion.md`. 7/7 slices and 21/22 acceptance items have tests. **Left:** - The migration dry run on a copy of the live database, after which the plan closes. It's the remaining acceptance item. - Deploying when the maintainer asks. - The counsel questions listed in the plan and `docs/self-hosting.md` §2. They don't block the work. This issue closes once v6 is deployed and verified.
Author
Owner

Live: deployed at ec6f0cb, schema v6. A pre-deploy backup was taken first. After the migration:

  • integrity_check returned ok, foreign_key_check was clean, and the free list was 0;
  • legal_holds and the indexes outbox_token_hash, audit_at and audit_actor exist;
  • row counts are unchanged;
  • RETENTION_PAUSED=false, with the defaults of 730 and 90 days;
  • healthz is ok, signed-out API requests get 401, the container is healthy, and the boot log has no errors.

Dry run (before the deploy): run on a copy of the live database on a dev host. It passed 47 of 47 checks.

  • Migration only, under the live container limits:
    • v5 → v6 in about 44 ms, with a 12 MiB memory peak;
    • the pre-v5 backup was written with mode 600;
    • integrity_check returned ok and foreign_key_check was clean;
    • every existing table kept the same row count and content hash.
  • A production boot through Compose: healthy, with no error-level lines and no change to any row count.
  • The docs/self-hosting.md §10 restore procedure: a synthetic client was deleted with a hold set (refused, nothing changed), then after release-hold (deleted). Afterwards:
    • the FK check was clean;
    • no email bytes were left in the database or the WAL;
    • the paused and unpaused restarts behaved as documented.

The details are in the plan's completion notes (ec6f0cb).

Acceptance:

  • Expired auth rows are purged on a schedule, with tests (16705eb, live since 13541d6).
  • Client deletion removes the client's personal data, and leaves other clients' progress and the kept audit trail intact (8754704; tests in server/test/client-deletion.test.js, plus the dry run above).

Still open, outside this issue: the counsel questions in docs/self-hosting.md §2 (the retention period, the backup age limit, when to release a hold). Neither the periods nor RETENTION_PAUSED need a code change.

**Live:** deployed at ec6f0cb, schema v6. A pre-deploy backup was taken first. After the migration: - `integrity_check` returned ok, `foreign_key_check` was clean, and the free list was 0; - `legal_holds` and the indexes `outbox_token_hash`, `audit_at` and `audit_actor` exist; - row counts are unchanged; - `RETENTION_PAUSED=false`, with the defaults of 730 and 90 days; - healthz is ok, signed-out API requests get 401, the container is healthy, and the boot log has no errors. **Dry run (before the deploy):** run on a copy of the live database on a dev host. It passed 47 of 47 checks. - **Migration only, under the live container limits:** - v5 → v6 in about 44 ms, with a 12 MiB memory peak; - the `pre-v5` backup was written with mode 600; - `integrity_check` returned ok and `foreign_key_check` was clean; - every existing table kept the same row count and content hash. - **A production boot through Compose:** healthy, with no error-level lines and no change to any row count. - **The `docs/self-hosting.md` §10 restore procedure:** a synthetic client was deleted with a hold set (refused, nothing changed), then after `release-hold` (deleted). Afterwards: - the FK check was clean; - no email bytes were left in the database or the WAL; - the paused and unpaused restarts behaved as documented. The details are in the plan's completion notes (ec6f0cb). **Acceptance:** - Expired auth rows are purged on a schedule, with tests (16705eb, live since 13541d6). - Client deletion removes the client's personal data, and leaves other clients' progress and the kept audit trail intact (8754704; tests in `server/test/client-deletion.test.js`, plus the dry run above). **Still open, outside this issue:** the counsel questions in `docs/self-hosting.md` §2 (the retention period, the backup age limit, when to release a hold). Neither the periods nor `RETENTION_PAUSED` need a code change.
ben closed this issue 2026-10-16 04:20:31 +00:00
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
ben/career-values-cards#8
No description provided.