`ledger.sql`

34 lines
-- Operational ledger for the image-shrink campaign. Lives in the LOCAL Mac2
-- dw_unified mirror (host=/tmp) — NOT the canonical Kamatera DB (avoids the
-- repl_user/pg_dump GRANT-gap issue). One row per source image; resumable.
CREATE TABLE IF NOT EXISTS img_shrink_ledger (
  id                BIGSERIAL PRIMARY KEY,
  product_id        BIGINT       NOT NULL,
  shard             INT          NOT NULL,              -- product_id % N, for fan-out
  orig_image_id     BIGINT       NOT NULL,
  orig_src          TEXT         NOT NULL,
  orig_position     INT,
  orig_alt          TEXT,
  orig_width        INT,
  orig_height       INT,
  orig_bytes        BIGINT,                             -- actual (buf.length at process time)
  content_type      TEXT,
  product_snapshot  JSONB,                              -- images[] snapshot for rollback
  new_image_id      BIGINT,
  new_src           TEXT,
  new_bytes         BIGINT,
  new_width         INT,
  new_height        INT,
  state             TEXT NOT NULL DEFAULT 'pending',    -- pending|claimed|done|failed|skipped|imageless_alert
  skip_reason       TEXT,
  fail_reason       TEXT,
  attempts          INT NOT NULL DEFAULT 0,
  worker_id         TEXT,
  claimed_at        TIMESTAMPTZ,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_orig_image UNIQUE (orig_image_id)
);
CREATE INDEX IF NOT EXISTS ix_shrink_shard_state ON img_shrink_ledger (shard, state);
CREATE INDEX IF NOT EXISTS ix_shrink_product    ON img_shrink_ledger (product_id);
CREATE INDEX IF NOT EXISTS ix_shrink_state      ON img_shrink_ledger (state);