mailadmin/backend/migrations/001_init.sql

CREATE TABLE IF NOT EXISTS nodes (
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  hostname TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS domains (
  id SERIAL PRIMARY KEY,
  domain TEXT UNIQUE NOT NULL,
  node_name TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  first_seen_at TIMESTAMPTZ DEFAULT now(),
  last_seen_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS mailboxes (
  id SERIAL PRIMARY KEY,
  email_address TEXT UNIQUE NOT NULL,
  domain TEXT NOT NULL,
  node_name TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  used_bytes BIGINT DEFAULT 0,
  last_usage_scan_at TIMESTAMPTZ,
  first_seen_at TIMESTAMPTZ DEFAULT now(),
  last_seen_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS admin_users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'super_admin',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_log (
  id SERIAL PRIMARY KEY,
  actor_email TEXT,
  action TEXT NOT NULL,
  target TEXT,
  details JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ============================================================
-- Upgrade existing MVP database
-- ============================================================

ALTER TABLE nodes
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();

ALTER TABLE nodes
  ADD COLUMN IF NOT EXISTS is_current BOOLEAN DEFAULT false;

ALTER TABLE domains
  ADD COLUMN IF NOT EXISTS node_name TEXT;

ALTER TABLE domains
  ADD COLUMN IF NOT EXISTS current_node TEXT;

ALTER TABLE domains
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();

ALTER TABLE domains
  ADD COLUMN IF NOT EXISTS last_synced_at TIMESTAMPTZ;

ALTER TABLE domains
  ADD COLUMN IF NOT EXISTS notes TEXT DEFAULT '';

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS node_name TEXT;

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS local_part TEXT;

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS quota_bytes BIGINT;

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS quota_percent NUMERIC(8,3);

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS message_count BIGINT;

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS message_limit BIGINT;

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS usage_scanned_at TIMESTAMPTZ;

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();

ALTER TABLE mailboxes
  ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

ALTER TABLE admin_users
  ADD COLUMN IF NOT EXISTS allowed_domains TEXT[] NOT NULL DEFAULT '{}';

ALTER TABLE admin_users
  ADD COLUMN IF NOT EXISTS active BOOLEAN NOT NULL DEFAULT true;

ALTER TABLE admin_users
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ DEFAULT now();

ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS target_type TEXT;

ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS target_id TEXT;

ALTER TABLE audit_log
  ADD COLUMN IF NOT EXISTS ip_address TEXT;

ALTER TABLE audit_log
  ALTER COLUMN details SET DEFAULT '{}';

-- ============================================================
-- Compatibility backfills
-- ============================================================

-- If an earlier version used current_node instead of node_name, copy it.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'domains'
      AND column_name = 'current_node'
  ) THEN
    EXECUTE '
      UPDATE domains
      SET node_name = current_node
      WHERE node_name IS NULL
        AND current_node IS NOT NULL
    ';
  END IF;
END $$;

-- If node_name is still empty, set it to current app node from env when available.
DO $$
DECLARE
  app_node_name TEXT;
BEGIN
  app_node_name := current_setting('app.node_name', true);

  IF app_node_name IS NOT NULL AND app_node_name <> '' THEN
    UPDATE domains
    SET node_name = app_node_name
    WHERE node_name IS NULL;

    UPDATE mailboxes
    SET node_name = app_node_name
    WHERE node_name IS NULL;
  END IF;
END $$;

-- Fill current_node from node_name for compatibility with newer code variants.
UPDATE domains
SET current_node = node_name
WHERE current_node IS NULL
  AND node_name IS NOT NULL;

-- Fill local_part for existing rows.
UPDATE mailboxes
SET local_part = split_part(email_address, '@', 1)
WHERE local_part IS NULL
  AND email_address LIKE '%@%';

-- Fill mailbox node_name from domain node_name where possible.
UPDATE mailboxes m
SET node_name = d.node_name
FROM domains d
WHERE m.domain = d.domain
  AND m.node_name IS NULL
  AND d.node_name IS NOT NULL;

-- Keep old and new usage timestamp columns in sync initially.
-- Dynamic EXECUTE is important here, otherwise PostgreSQL may still parse a missing column.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'mailboxes'
      AND column_name = 'last_usage_scan_at'
  ) THEN
    EXECUTE '
      UPDATE mailboxes
      SET usage_scanned_at = last_usage_scan_at
      WHERE usage_scanned_at IS NULL
        AND last_usage_scan_at IS NOT NULL
    ';
  END IF;
END $$;

-- Backfill new audit target columns from old target column.
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'audit_log'
      AND column_name = 'target'
  ) THEN
    EXECUTE '
      UPDATE audit_log
      SET target_id = target
      WHERE target_id IS NULL
        AND target IS NOT NULL
    ';
  END IF;
END $$;

UPDATE audit_log
SET target_type = 'unknown'
WHERE target_type IS NULL;

-- ============================================================
-- Final constraints/defaults after backfill
-- ============================================================

ALTER TABLE domains
  ALTER COLUMN node_name SET DEFAULT 'unknown';

ALTER TABLE mailboxes
  ALTER COLUMN node_name SET DEFAULT 'unknown';

UPDATE domains
SET node_name = 'unknown'
WHERE node_name IS NULL;

UPDATE mailboxes
SET node_name = 'unknown'
WHERE node_name IS NULL;

ALTER TABLE domains
  ALTER COLUMN node_name SET NOT NULL;

ALTER TABLE mailboxes
  ALTER COLUMN node_name SET NOT NULL;

-- ============================================================
-- Useful indexes
-- ============================================================

CREATE INDEX IF NOT EXISTS idx_domains_node_name
  ON domains(node_name);

CREATE INDEX IF NOT EXISTS idx_domains_current_node
  ON domains(current_node);

CREATE INDEX IF NOT EXISTS idx_mailboxes_domain
  ON mailboxes(domain);

CREATE INDEX IF NOT EXISTS idx_mailboxes_node_name
  ON mailboxes(node_name);

CREATE INDEX IF NOT EXISTS idx_audit_created
  ON audit_log(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_admin_users_allowed_domains
  ON admin_users USING GIN(allowed_domains);