# Spec 051 — Privacy & Retention Decisions (T6.5)

Covers the `onboardingEventLog` 24-month retention job, the `userInvites`/
`inviteMessages` user-deletion hook, and the retention treatment of the
051-adjacent archive tables. Written as a durable decision record, not just
inline code comments, because these are exactly the kind of cross-cutting
compliance decisions a future engineer needs to find without re-deriving
them from source.

---

## 1. `onboardingEventLog` 24-month retention job

**Job:** `BuyerKiosk\TaskEngine\Jobs\OnboardingEventLogRetentionJob`
(registered in `TaskCommandFactory::registerJobs()`).

**Behavior:** archive-then-delete, batched (500 rows/batch, capped at 50,000
rows/execution). Deletes `onboardingEventLog` rows with `occurredAt` older
than 24 months (the SDD's stated retention window). Never deletes a row that
failed to archive first.

### Deviation: archive target is a file, not a new DB table

CLAUDE.md's migration-system mandate ("DO NOT MODIFY TABLES DIRECTLY. USE
OUR MIGRATION SYSTEM") plus this phase's explicit scope exclusion of new
migrations together rule out creating a new `onboardingEventLogArchive` DB
table here. `onboarding-reset-store.php`'s runtime `CREATE TABLE ... LIKE`
pattern is an explicitly narrow, documented, DEV-ONLY exception (guarded by
DEV=1 + localhost-only checks) — this retention job runs in **production**,
so that exception does not extend to it.

**Resolution:** the job archives each doomed row as one JSON line, appended
to `{LOG_DIR}/onboarding-eventlog-retention/onboardingEventLog-{YYYY-MM}.jsonl`,
before deleting it. This preserves the archive-before-delete safety property
(CON-9) without any schema change. A future phase that wants a
SQL-queryable archive can add the DB table via a proper migration and swap
the job's `appendToArchive()` internals — the batched fetch/delete loop
itself is unaffected either way.

### Deviation: cadence not yet seeded into `task_job_definitions`

Every OTHER scheduled job in this codebase (e.g. `staff-chat-retention`,
`0 3 * * *`) has its cron schedule seeded via a migration inserting into
`task_job_definitions` (see `20260102_001_staff_chat_retention_job.json` for
the exact precedent). This phase's no-new-migrations scope means that row
does not yet exist for `onboarding-eventlog-retention`. The class is fully
built, registered in the code-level `JobRegistry`
(`TaskCommandFactory::registerJobs()`), and its recommended cadence is a
public constant (`OnboardingEventLogRetentionJob::RECOMMENDED_SCHEDULE =
'0 4 * * *'`, daily at 4 AM — matching the StaffChatRetentionJob precedent's
cadence) verified via a real `CronExpression::parse()` test
(`OnboardingEventLogRetentionJobTest::testRecommendedScheduleFiresDailyAtFourAm`).
It will **not actually run on a schedule** until a migration seeds it. Ready
to apply as its own follow-up migration:

```json
[
  {
    "type": "insert",
    "description": "Add Onboarding Event Log Retention job definition (Spec 051 T6.5)",
    "database": "kiosk_buykiosk",
    "check_query": "SELECT id FROM task_job_definitions WHERE name = 'onboarding-eventlog-retention' LIMIT 1",
    "sql": "INSERT INTO `task_job_definitions` (`name`, `displayName`, `className`, `schedule`, `queue`, `scope`, `timeout`, `maxRetries`, `retryBackoff`, `config`, `isEnabled`, `notifyOnFailure`, `notifyEmails`) VALUES ('onboarding-eventlog-retention', 'Onboarding Event Log Retention (24-month)', 'BuyerKiosk\\\\TaskEngine\\\\Jobs\\\\OnboardingEventLogRetentionJob', '0 4 * * *', 'low', 'global', 600, 2, 60, '{}', 1, 1, NULL)"
  }
]
```

Apply via `php userfrosting/conductor run` once this migration lands in
`migrations/input/`. Until then, the job can still be run manually
(`php userfrosting/bin/task job:dispatch onboarding-eventlog-retention`) for
ad-hoc cleanup.

---

## 2. `userInvites`/`inviteMessages` hooked into the user-deletion path

**Service:** `BuyerKiosk\Privacy\Services\InviteUserDeletionPrivacyService`,
called from `UserFrosting\MySqlUser::delete()` (`models/mysql/MySqlUser.php`)
**before** `parent::delete()` issues the actual `DELETE FROM users`.

### The problem

`userInvites` has two enforced foreign keys to `users.id`, both `NOT NULL`
with no `ON DELETE` clause (implicit RESTRICT): `userId` (invitee) and
`invitedByUserId` (inviter). A plain `DELETE FROM users` fails with a
foreign-key violation the moment ANY invite row references that user in
EITHER direction (T1/T2 review finding — the reason invite writes were
pilot/dev-only until this phase).

### Decided semantics

| Direction | Column | Action | Why |
|---|---|---|---|
| Invitee | `userInvites.userId = $userId` | **DELETE** (cascades to `inviteMessages` automatically via `ON DELETE CASCADE`) | The deleted user's own invite history is personal data about THEM |
| Inviter | `userInvites.invitedByUserId = $userId` | **ANONYMIZE** (reassign to the master/system account) | The row describes SOMEONE ELSE's invite — deleting it would destroy that OTHER person's invite history as a side effect |

### Why anonymize to the master account (not NULL, not a new sentinel row)

Checked this codebase's existing precedent for "who did this" actor
columns referencing `users.id`: `onboardingProgress.completedByUserId` and
`userPayRates.createdByUserId` are BOTH plain, **unenforced** integer
columns (no FK constraint at all) — the established convention here is to
let actor references go stale after a user is deleted, never to block
deletion or scrub them. `userInvites.invitedByUserId` breaks that
convention by carrying a real FK (a genuine schema inconsistency, not
something to silently paper over by widening scope to drop the constraint —
that needs a migration, out of this phase). Given the column is `NOT NULL`
(so `NULL` isn't an option) and adding a brand-new "deleted user" placeholder
account would need its own migration/seed, reassigning to the existing
`user_id_master` config value (already guaranteed un-deletable —
`UserController::deleteUser()`'s own guard: "Check that we are not disabling
the master account") satisfies both constraints with zero schema changes.

### Finding: local dev DB is missing the `user_id_master` row

`config-userfrosting.php` sets `user_id_master => 1`, but this specific local
dev database's `kiosk_users.users` table has **no row with id=1** (lowest id
present is 28) — a pre-existing environment data gap, unrelated to Spec 051.
This matters operationally: if the configured master id ever doesn't
correspond to a real row (in ANY environment), the anonymize step ITSELF
throws a foreign-key violation (can't set `invitedByUserId` to a
non-existent user), which would then block the inviter's deletion the same
way the original bug did. **Action for launch:** verify `user_id_master`'s
configured id (1) is a real, permanent row in the production `kiosk_users.users`
table before this phase's invite-writing paths (T3) go live for real
traffic. The integration test suite (`InviteUserDeletionPrivacyServiceTest`)
works around this local gap by creating its own dedicated fixture "master"
user per test run rather than depending on the real id=1 row — this proves
the mechanism, but does not substitute for the production verification above.

### Proof (real DB, real FK constraints)

`tests/Integration/Privacy/InviteUserDeletionPrivacyServiceTest.php` — 7
tests, all green:

- Negative baseline: WITHOUT the cleanup, deleting either an invitee or an
  inviter genuinely throws `PDOException` (SQLSTATE 23000) — proves the
  failure mode is real, not hypothetical.
- Invitee deletion: invite + message rows deleted; the subsequent real
  `DELETE FROM users` succeeds.
- Inviter deletion: `invitedByUserId` reassigned to the master account; the
  OTHER user's invite/message rows survive fully intact; the subsequent real
  `DELETE FROM users` succeeds.
- A user who is simultaneously an invitee (row 1) and an inviter (row 2):
  both halves apply correctly in one call.
- No invite history at all: safe no-op.
- Wiring proof: `MySqlUser::delete()` calls `cleanupBeforeUserDeletion()`
  strictly BEFORE `parent::delete()` (source-order assertion, since fully
  bootstrapping the legacy Slim/UserFrosting app object graph just to
  construct a real `MySqlUser` instance is disproportionate to what this
  proof needs — the real DB/FK behavior above is the substantive proof; this
  is the integration-ordering proof).

---

## 3. Archive tables in the retention/privacy sweep decision

Per T6.5's scope: decide what happens to the archive tables this spec has
touched, beyond the primary `onboardingEventLog` retention job above.

| Table | Location | Nature | Retention decision |
|---|---|---|---|
| `userDeviceTokensAppIdConflictArchive` | `kiosk_users` (central) | Created ONCE by migration `20260721_051_004_device_token_uk_appid.json`, holding real FCM device tokens + `userId` for rows the uk-widening migration displaced. Not written to on an ongoing basis — a one-time migration artifact. | **Genuine PII (device tokens + userId) that currently has NO retention rule at all.** Recommendation (not implemented this phase — no evidence of urgency, and it lives in a table this spec's own migration created, arguably making it this spec's responsibility to eventually clean up): extend a FUTURE retention job (or widen `OnboardingEventLogRetentionJob`'s scope with a second method) to archive-and-purge rows from this table once they exceed a similar age window. Flagged here so it isn't forgotten, not silently left to accumulate PII indefinitely. |
| `<table>Archive051Reset` (e.g. `schedulePositionsArchive051Reset`, `onboardingProgressArchive051Reset`, etc.) | `kiosk_buykiosk`, `kiosk_users`, and the fixture stores' own DBs (`kiosk_ob01`/`ob02`/`ob03`) | Created at RUNTIME by `onboarding-reset-store.php`, itself an explicitly DEV-ONLY, fixture-scoped tool (hard guards: DEV=1, localhost DB_HOST, fixture-typeNum allowlist). | **Exempt from production retention policy — these tables cannot exist outside a local dev environment** (the reset script refuses to run anywhere else, per its own guards). No production retention job needs to consider them; they are testing scaffolding, not product data. Documented here explicitly so no future retention sweep mistakes them for a real production PII surface. |

---

## Summary of what's implemented vs. documented-only in this phase

| Item | Status |
|---|---|
| `OnboardingEventLogRetentionJob` (archive-then-delete, batched) | **Implemented + tested** (9 tests, real DB) |
| Job registered in `JobRegistry`/`TaskCommandFactory` | **Implemented + tested** |
| Job cadence (`0 4 * * *`) validated as a real cron expression | **Implemented + tested** |
| Job's `task_job_definitions` row (actual scheduling) | **Documented only** — ready-to-apply migration above, blocked on this phase's no-new-migrations scope |
| `userInvites`/`inviteMessages` user-deletion hook (both FK directions) | **Implemented + tested** (7 integration tests, real FK constraints) |
| `userDeviceTokensAppIdConflictArchive` retention | **Documented only** — flagged as a gap, not implemented this phase (see table above) |
| `*Archive051Reset` tables | **Documented decision: exempt** (dev-only, cannot exist in production) |