g1t/services/events/migrations/0004_audit_retention.sql
| 1 | -- Audit entries are kept by plan (src/audit.rs): 7 days for a free |
| 2 | -- workspace, 90 on the plan, or what g1t staff set for its account, and |
| 3 | -- never past AUDIT_MAX_DAYS. The daily purge deletes everything older than |
| 4 | -- the ceiling by time, finds the workspaces with entries older than the |
| 5 | -- shortest retention, and deletes each one's older than its own days; these |
| 6 | -- indexes keep each of those a seek rather than a scan. |
| 7 | CREATE INDEX IF NOT EXISTS audit_workspace_time ON audit_entries (workspace, time); |
| 8 | CREATE INDEX IF NOT EXISTS audit_time ON audit_entries (time); |
| 9 | |
| 10 | -- Where the last daily run stopped, so the next carries on from there: one |
| 11 | -- run looks at a bounded number of workspaces. Empty: from the start. |
| 12 | CREATE TABLE IF NOT EXISTS audit_purge_cursor ( |
| 13 | id INTEGER PRIMARY KEY CHECK (id = 1), |
| 14 | after TEXT NOT NULL DEFAULT '' |
| 15 | ); |