migrations/027_maintenance_buckets.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Recurring maintenance buckets: a finite pool of native_tasks that get
-- repeatedly activated/deactivated (state flip, not a new row per cycle,
-- unlike due-date recurrence) every cycle_days, picking pick_n dormant
-- items per cycle.
CREATE TABLE maintenance_buckets (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    cycle_days INTEGER NOT NULL,
    pick_n INTEGER NOT NULL,
    last_cycle_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE native_tasks ADD COLUMN bucket_id TEXT DEFAULT '';
ALTER TABLE native_tasks ADD COLUMN bucket_state TEXT DEFAULT '';
ALTER TABLE native_tasks ADD COLUMN bucket_last_active_at DATETIME;

CREATE INDEX IF NOT EXISTS idx_native_tasks_bucket ON native_tasks(bucket_id);