MiragenFlow/server/migrations/0004_v1_extended.sql

-- Extended V1 domain tables. Secrets are referenced by encrypted_ref only;
-- user files live in staging/object adapters and never in static directories.
CREATE TABLE IF NOT EXISTS balance_units (
  id TEXT PRIMARY KEY,
  version INTEGER NOT NULL,
  name TEXT NOT NULL,
  short_code TEXT NOT NULL,
  icon TEXT NOT NULL,
  precision INTEGER NOT NULL DEFAULT 0,
  currency_code TEXT NOT NULL,
  conversion_rate NUMERIC(24, 8) NOT NULL,
  rounding TEXT NOT NULL,
  published_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS balance_buckets (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  source TEXT NOT NULL,
  remaining NUMERIC(24, 6) NOT NULL CHECK (remaining >= 0),
  priority INTEGER NOT NULL DEFAULT 0,
  expires_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS membership_plans (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  version INTEGER NOT NULL DEFAULT 1,
  snapshot JSONB NOT NULL,
  published BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS user_plan_entitlements (
  user_id UUID NOT NULL REFERENCES users(id),
  plan_id TEXT NOT NULL REFERENCES membership_plans(id),
  version INTEGER NOT NULL,
  starts_at TIMESTAMPTZ NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (user_id, plan_id, version)
);
CREATE TABLE IF NOT EXISTS recharge_orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  provider TEXT NOT NULL,
  status TEXT NOT NULL,
  fiat_amount NUMERIC(24, 6) NOT NULL,
  balance_amount NUMERIC(24, 6) NOT NULL,
  unit_version INTEGER NOT NULL,
  idempotency_key TEXT NOT NULL UNIQUE,
  provider_order_id TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  paid_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS payment_events (
  id UUID PRIMARY KEY,
  provider TEXT NOT NULL,
  provider_event_id TEXT NOT NULL,
  order_id UUID REFERENCES recharge_orders(id),
  payload_digest TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(provider, provider_event_id)
);
CREATE TABLE IF NOT EXISTS generation_outputs (
  id UUID PRIMARY KEY,
  task_id UUID NOT NULL REFERENCES generation_tasks(id),
  owner_id UUID NOT NULL REFERENCES users(id),
  object_key TEXT NOT NULL,
  mime_type TEXT NOT NULL,
  status TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS upload_objects (
  id UUID PRIMARY KEY,
  owner_id UUID NOT NULL REFERENCES users(id),
  object_key TEXT NOT NULL,
  mime_type TEXT NOT NULL,
  byte_size BIGINT NOT NULL,
  checksum TEXT NOT NULL,
  scan_status TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  deleted_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS assets (
  id UUID PRIMARY KEY,
  owner_id UUID NOT NULL REFERENCES users(id),
  task_id UUID REFERENCES generation_tasks(id),
  object_id UUID REFERENCES upload_objects(id),
  asset_type TEXT NOT NULL,
  snapshot JSONB NOT NULL,
  deleted_at TIMESTAMPTZ,
  retention_expires_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS email_sms_outbox (
  id UUID PRIMARY KEY,
  target_hash TEXT NOT NULL,
  channel TEXT NOT NULL,
  purpose TEXT NOT NULL,
  payload JSONB NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  attempts INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS webdav_configs (
  user_id UUID PRIMARY KEY REFERENCES users(id),
  encrypted_url_ref TEXT NOT NULL,
  encrypted_username_ref TEXT,
  encrypted_password_ref TEXT,
  directory TEXT NOT NULL,
  state TEXT NOT NULL,
  last_synced_at TIMESTAMPTZ,
  retention_days INTEGER NOT NULL DEFAULT 30
);
CREATE TABLE IF NOT EXISTS webdav_manifests (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  version BIGINT NOT NULL,
  checksum TEXT NOT NULL,
  manifest JSONB NOT NULL,
  state TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, version)
);
CREATE TABLE IF NOT EXISTS storage_policies (
  id TEXT PRIMARY KEY,
  version INTEGER NOT NULL,
  snapshot JSONB NOT NULL,
  published BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS admin_roles (
  id TEXT PRIMARY KEY,
  permissions JSONB NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE IF NOT EXISTS admin_user_roles (
  admin_id UUID NOT NULL,
  role_id TEXT NOT NULL REFERENCES admin_roles(id),
  PRIMARY KEY (admin_id, role_id)
);
CREATE TABLE IF NOT EXISTS admin_approvals (
  id UUID PRIMARY KEY,
  requester_id UUID NOT NULL,
  approver_id UUID,
  action TEXT NOT NULL,
  object_type TEXT NOT NULL,
  object_id TEXT,
  status TEXT NOT NULL DEFAULT 'pending',
  before_value JSONB,
  after_value JSONB,
  expires_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  approved_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS task_event_log (
  task_id UUID NOT NULL REFERENCES generation_tasks(id),
  sequence BIGINT NOT NULL,
  event_id UUID NOT NULL UNIQUE,
  event_type TEXT NOT NULL,
  payload JSONB NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (task_id, sequence)
);
CREATE INDEX IF NOT EXISTS upload_objects_owner_idx ON upload_objects(owner_id, expires_at);
CREATE INDEX IF NOT EXISTS generation_outputs_owner_idx ON generation_outputs(owner_id, expires_at);
CREATE INDEX IF NOT EXISTS recharge_orders_user_created_idx ON recharge_orders(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS task_event_log_task_sequence_idx ON task_event_log(task_id, sequence);