012 - booking transaction link + payment/stock ledgers

• Tanggal: 2026-08-10
• DB: mpcdb (PostgreSQL)
• Ringkas: bookings.transaction_id; payment_events; stock_ledger.

ALTER (jalankan di DB server)

ALTER TABLE bookings ADD COLUMN IF NOT EXISTS transaction_id uuid REFERENCES transactions(id);
CREATE INDEX IF NOT EXISTS idx_bookings_transaction_id ON bookings(transaction_id);

-- Backfill from transaction_items (ItemBooking)
UPDATE bookings b
SET transaction_id = ti.transaction_id
FROM transaction_items ti
WHERE ti.item_type = 'Booking'
  AND ti.reference_id = b.id::text
  AND b.transaction_id IS NULL
  AND ti.deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS payment_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_id uuid NOT NULL REFERENCES transactions(id),
  provider text NOT NULL DEFAULT 'mock',
  event_type text NOT NULL,
  external_ref text,
  payload jsonb,
  code text UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid,
  updated_at timestamptz DEFAULT now(),
  updated_by uuid,
  deleted_at timestamptz,
  deleted_by uuid
);
CREATE INDEX IF NOT EXISTS idx_payment_events_tx ON payment_events(transaction_id);

CREATE TABLE IF NOT EXISTS stock_ledger (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id uuid NOT NULL REFERENCES products(id),
  delta int NOT NULL,
  reason text NOT NULL,
  reference_type text,
  reference_id text,
  code text UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid,
  updated_at timestamptz DEFAULT now(),
  updated_by uuid,
  deleted_at timestamptz,
  deleted_by uuid
);
CREATE INDEX IF NOT EXISTS idx_stock_ledger_product ON stock_ledger(product_id);

Rollback

ALTER TABLE bookings DROP COLUMN IF EXISTS transaction_id;
DROP TABLE IF EXISTS payment_events;
DROP TABLE IF EXISTS stock_ledger;