← back to AbramsOS

db/migrations/0003_reminders.sql

25 lines

-- 0003_reminders.sql — calendar_reminder table

BEGIN;

CREATE TABLE IF NOT EXISTS calendar_reminder (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  owner_table     text,                       -- 'purchase', 'recall_match', etc.
  owner_id        text,
  due_at          timestamptz NOT NULL,
  reason_code     text NOT NULL,              -- 'returns_window_closing', 'warranty_expiry', 'recall_action_due'
  title           text NOT NULL,
  body            text,
  calendar_event_id text,                     -- when synced to Google Calendar (later)
  state           text NOT NULL DEFAULT 'pending',  -- pending | dismissed | resolved
  created_at      timestamptz NOT NULL DEFAULT now(),
  reviewed_at     timestamptz,
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS calendar_reminder_due_idx ON calendar_reminder (user_id, state, due_at);
CREATE INDEX IF NOT EXISTS calendar_reminder_owner_idx ON calendar_reminder (owner_table, owner_id);
CREATE UNIQUE INDEX IF NOT EXISTS calendar_reminder_dedupe ON calendar_reminder (user_id, owner_table, owner_id, reason_code);

COMMIT;