app

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

0010_sync_contract_alignment.sql (2329B)

      1 ALTER TABLE local_outbox RENAME TO local_outbox_legacy;
      2 ALTER TABLE local_conflicts RENAME TO local_conflicts_legacy;
      3 DROP TABLE sync_checkpoints;
      4 
      5 CREATE TABLE local_outbox (
      6     id TEXT PRIMARY KEY NOT NULL,
      7     account_id TEXT NOT NULL,
      8     aggregate_kind TEXT NOT NULL CHECK (
      9         aggregate_kind IN ('farm', 'fulfillment_window', 'product', 'order')
     10     ),
     11     aggregate_id TEXT NOT NULL,
     12     operation_kind TEXT NOT NULL CHECK (operation_kind IN ('upsert', 'delete')),
     13     payload_json TEXT NOT NULL,
     14     created_at TEXT NOT NULL,
     15     available_at TEXT NOT NULL,
     16     attempt_count INTEGER NOT NULL DEFAULT 0
     17 );
     18 
     19 CREATE TABLE local_conflicts (
     20     id TEXT PRIMARY KEY NOT NULL,
     21     account_id TEXT NOT NULL,
     22     aggregate_kind TEXT NOT NULL CHECK (
     23         aggregate_kind IN ('farm', 'fulfillment_window', 'product', 'order')
     24     ),
     25     aggregate_id TEXT NOT NULL,
     26     conflict_kind TEXT NOT NULL CHECK (
     27         conflict_kind IN (
     28             'revision_mismatch',
     29             'remote_delete',
     30             'remote_validation_reject'
     31         )
     32     ),
     33     severity TEXT NOT NULL CHECK (severity IN ('review_required', 'blocking')),
     34     resolution_status TEXT NOT NULL CHECK (
     35         resolution_status IN (
     36             'unresolved',
     37             'accepted_local',
     38             'accepted_remote',
     39             'dismissed'
     40         )
     41     ),
     42     local_payload_json TEXT NOT NULL,
     43     remote_payload_json TEXT,
     44     detected_at TEXT NOT NULL,
     45     resolved_at TEXT
     46 );
     47 
     48 CREATE TABLE sync_checkpoints (
     49     account_id TEXT PRIMARY KEY NOT NULL,
     50     state TEXT NOT NULL CHECK (
     51         state IN ('never_synced', 'syncing', 'current', 'failed')
     52     ),
     53     last_sync_started_at TEXT,
     54     last_sync_completed_at TEXT,
     55     last_remote_cursor TEXT,
     56     last_error_message TEXT
     57 );
     58 
     59 DROP TABLE local_outbox_legacy;
     60 DROP TABLE local_conflicts_legacy;
     61 
     62 CREATE INDEX idx_local_outbox_account_available_at ON local_outbox(
     63     account_id,
     64     available_at,
     65     created_at,
     66     id
     67 );
     68 CREATE INDEX idx_local_outbox_account_aggregate ON local_outbox(
     69     account_id,
     70     aggregate_kind,
     71     aggregate_id
     72 );
     73 CREATE INDEX idx_local_conflicts_account_detected_at ON local_conflicts(
     74     account_id,
     75     detected_at,
     76     id
     77 );
     78 CREATE INDEX idx_local_conflicts_account_aggregate ON local_conflicts(
     79     account_id,
     80     aggregate_kind,
     81     aggregate_id
     82 );