1 files changed, 18 insertions, 0 deletions
diff --git a/migrations/027_maintenance_buckets.sql b/migrations/027_maintenance_buckets.sql
new file mode 100644
index 0000000..50c6d7e
--- /dev/null
+++ b/migrations/027_maintenance_buckets.sql
@@ -0,0 +1,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);