app

Local-first trade for farms and co-ops
git clone https://radroots.dev/git/app.git
Log | Files | Refs | README | LICENSE

0011_reminders.sql (1998B)

      1 CREATE TABLE reminder_schedules (
      2     reminder_id TEXT NOT NULL,
      3     account_id TEXT NOT NULL,
      4     farm_id TEXT NOT NULL,
      5     order_id TEXT,
      6     fulfillment_window_id TEXT,
      7     reminder_kind TEXT NOT NULL CHECK (
      8         reminder_kind IN (
      9             'fulfillment_window',
     10             'order_action',
     11             'sync_impact'
     12         )
     13     ),
     14     reminder_surface TEXT NOT NULL CHECK (
     15         reminder_surface IN ('today', 'orders', 'pack_day')
     16     ),
     17     reminder_urgency TEXT NOT NULL CHECK (
     18         reminder_urgency IN ('upcoming', 'due_soon', 'overdue', 'blocking')
     19     ),
     20     title TEXT NOT NULL,
     21     detail TEXT NOT NULL,
     22     deadline_at TEXT NOT NULL,
     23     action_label TEXT,
     24     delivery_state TEXT NOT NULL CHECK (
     25         delivery_state IN ('scheduled', 'presented', 'acknowledged', 'resolved')
     26     ),
     27     PRIMARY KEY (account_id, farm_id, reminder_id)
     28 );
     29 
     30 CREATE TABLE reminder_log_entries (
     31     log_entry_id TEXT PRIMARY KEY NOT NULL,
     32     account_id TEXT NOT NULL,
     33     farm_id TEXT NOT NULL,
     34     reminder_id TEXT NOT NULL,
     35     reminder_kind TEXT NOT NULL CHECK (
     36         reminder_kind IN (
     37             'fulfillment_window',
     38             'order_action',
     39             'sync_impact'
     40         )
     41     ),
     42     title TEXT NOT NULL,
     43     recorded_at TEXT NOT NULL,
     44     delivery_state TEXT NOT NULL CHECK (
     45         delivery_state IN ('scheduled', 'presented', 'acknowledged', 'resolved')
     46     ),
     47     detail TEXT
     48 );
     49 
     50 CREATE INDEX idx_reminder_schedules_account_farm_deadline ON reminder_schedules(
     51     account_id,
     52     farm_id,
     53     deadline_at,
     54     reminder_id
     55 );
     56 CREATE INDEX idx_reminder_schedules_account_farm_surface ON reminder_schedules(
     57     account_id,
     58     farm_id,
     59     reminder_surface,
     60     deadline_at
     61 );
     62 CREATE INDEX idx_reminder_log_entries_account_farm_recorded_at ON reminder_log_entries(
     63     account_id,
     64     farm_id,
     65     recorded_at,
     66     log_entry_id
     67 );
     68 CREATE INDEX idx_reminder_log_entries_account_farm_reminder ON reminder_log_entries(
     69     account_id,
     70     farm_id,
     71     reminder_id
     72 );