app

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

0016_deterministic_outbox.sql (1876B)

      1 ALTER TABLE local_outbox RENAME TO local_outbox_legacy;
      2 
      3 CREATE TABLE local_outbox (
      4     id TEXT PRIMARY KEY NOT NULL,
      5     account_id TEXT NOT NULL,
      6     operation_key TEXT NOT NULL,
      7     aggregate_kind TEXT NOT NULL CHECK (
      8         aggregate_kind IN ('farm', 'fulfillment_window', 'product', 'order')
      9     ),
     10     aggregate_id TEXT NOT NULL,
     11     operation_kind TEXT NOT NULL CHECK (operation_kind IN ('upsert', 'delete')),
     12     payload_json TEXT NOT NULL,
     13     created_at TEXT NOT NULL,
     14     available_at TEXT NOT NULL,
     15     attempt_count INTEGER NOT NULL DEFAULT 0,
     16     state TEXT NOT NULL DEFAULT 'pending' CHECK (
     17         state IN (
     18             'pending',
     19             'in_progress',
     20             'succeeded',
     21             'failed',
     22             'blocked',
     23             'retryable'
     24         )
     25     ),
     26     last_error_message TEXT
     27 );
     28 
     29 CREATE UNIQUE INDEX idx_local_outbox_account_operation_key_active ON local_outbox(
     30     account_id,
     31     operation_key
     32 )
     33 WHERE state IN ('pending', 'in_progress', 'failed', 'blocked', 'retryable');
     34 
     35 INSERT OR REPLACE INTO local_outbox (
     36     id,
     37     account_id,
     38     operation_key,
     39     aggregate_kind,
     40     aggregate_id,
     41     operation_kind,
     42     payload_json,
     43     created_at,
     44     available_at,
     45     attempt_count,
     46     state,
     47     last_error_message
     48 )
     49 SELECT
     50     id,
     51     account_id,
     52     aggregate_kind || ':' || aggregate_id || ':' || operation_kind,
     53     aggregate_kind,
     54     aggregate_id,
     55     operation_kind,
     56     payload_json,
     57     created_at,
     58     available_at,
     59     attempt_count,
     60     'pending',
     61     NULL
     62 FROM local_outbox_legacy
     63 ORDER BY available_at ASC, created_at ASC, id ASC;
     64 
     65 DROP TABLE local_outbox_legacy;
     66 
     67 CREATE INDEX idx_local_outbox_account_available_at ON local_outbox(
     68     account_id,
     69     available_at,
     70     created_at,
     71     id
     72 );
     73 CREATE INDEX idx_local_outbox_account_aggregate ON local_outbox(
     74     account_id,
     75     aggregate_kind,
     76     aggregate_id
     77 );