009 - Audit columns on taxonomy/benefit tables

• Tanggal: 2026-08-10
• DB: mpcdb (PostgreSQL)
• Ringkas: Tambah created_at/updated_at/deleted_at (+ by) pada tabel yang belum punya audit fields.

ALTER (jalankan di DB server)

-- item_groups
ALTER TABLE item_groups
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS created_by UUID,
  ADD COLUMN IF NOT EXISTS updated_by UUID,
  ADD COLUMN IF NOT EXISTS deleted_by UUID;

-- item_categories
ALTER TABLE item_categories
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS created_by UUID,
  ADD COLUMN IF NOT EXISTS updated_by UUID,
  ADD COLUMN IF NOT EXISTS deleted_by UUID;

-- tier_benefits
ALTER TABLE tier_benefits
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS created_by UUID,
  ADD COLUMN IF NOT EXISTS updated_by UUID,
  ADD COLUMN IF NOT EXISTS deleted_by UUID,
  ADD COLUMN IF NOT EXISTS code TEXT;

CREATE UNIQUE INDEX IF NOT EXISTS tier_benefits_code_key ON tier_benefits (code) WHERE code IS NOT NULL;

-- benefit_usages
ALTER TABLE benefit_usages
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS created_by UUID,
  ADD COLUMN IF NOT EXISTS updated_by UUID,
  ADD COLUMN IF NOT EXISTS deleted_by UUID,
  ADD COLUMN IF NOT EXISTS code TEXT;

CREATE UNIQUE INDEX IF NOT EXISTS benefit_usages_code_key ON benefit_usages (code) WHERE code IS NOT NULL;

-- pos_devices: optional human code (identity remains api_key_hash)
ALTER TABLE pos_devices
  ADD COLUMN IF NOT EXISTS code TEXT;

CREATE UNIQUE INDEX IF NOT EXISTS pos_devices_code_key ON pos_devices (code) WHERE code IS NOT NULL;

Rollback

ALTER TABLE item_groups DROP COLUMN IF EXISTS created_at, DROP COLUMN IF EXISTS updated_at, DROP COLUMN IF EXISTS deleted_at,
  DROP COLUMN IF EXISTS created_by, DROP COLUMN IF EXISTS updated_by, DROP COLUMN IF EXISTS deleted_by;
ALTER TABLE item_categories DROP COLUMN IF EXISTS created_at, DROP COLUMN IF EXISTS updated_at, DROP COLUMN IF EXISTS deleted_at,
  DROP COLUMN IF EXISTS created_by, DROP COLUMN IF EXISTS updated_by, DROP COLUMN IF EXISTS deleted_by;
ALTER TABLE tier_benefits DROP COLUMN IF EXISTS created_at, DROP COLUMN IF EXISTS updated_at, DROP COLUMN IF EXISTS deleted_at,
  DROP COLUMN IF EXISTS created_by, DROP COLUMN IF EXISTS updated_by, DROP COLUMN IF EXISTS deleted_by, DROP COLUMN IF EXISTS code;
ALTER TABLE benefit_usages DROP COLUMN IF EXISTS created_at, DROP COLUMN IF EXISTS updated_at, DROP COLUMN IF EXISTS deleted_at,
  DROP COLUMN IF EXISTS created_by, DROP COLUMN IF EXISTS updated_by, DROP COLUMN IF EXISTS deleted_by, DROP COLUMN IF EXISTS code;
ALTER TABLE pos_devices DROP COLUMN IF EXISTS code;
DROP INDEX IF EXISTS tier_benefits_code_key;
DROP INDEX IF EXISTS benefit_usages_code_key;
DROP INDEX IF EXISTS pos_devices_code_key;

Catatan

• pos_devices.code opsional; device identity tetap api_key_hash.
• Lihat juga mpc-dev/docs/schema/conventions.md.