← back to AbramsOS

db/migrations/0012_plaid_sync_cursor.sql

18 lines

-- 0012_plaid_sync_cursor.sql
-- Make bank (Plaid) sync incremental + idempotent:
--   • connector_account.sync_cursor — persist Plaid's transactions/sync cursor so each
--     sync only pulls what's NEW (without it, every sync re-pulled everything → dup rows).
--   • unique (user_id, order_number) on purchase — Plaid stores its stable transaction_id
--     in order_number; this lets the sync ON CONFLICT DO NOTHING so a transaction never
--     lands twice even across cursor resets.
-- Idempotent. Safe to re-run.

BEGIN;

ALTER TABLE connector_account ADD COLUMN IF NOT EXISTS sync_cursor text;

CREATE UNIQUE INDEX IF NOT EXISTS purchase_user_order_uidx
  ON purchase (user_id, order_number) WHERE order_number IS NOT NULL;

COMMIT;