CREATE TABLE IF NOT EXISTS system_config_revisions ( id TEXT PRIMARY KEY, tenant_key TEXT NOT NULL, kind TEXT NOT NULL, revision INTEGER NOT NULL CHECK(revision>0), state TEXT NOT NULL CHECK(state IN ('draft','validated','active','retired','failed')), public_config JSONB NOT NULL DEFAULT '{}'::JSONB, secret_ref TEXT NOT NULL DEFAULT '', validation_status TEXT NOT NULL DEFAULT 'pending', validation_code TEXT NOT NULL DEFAULT '', validated_at TIMESTAMPTZ, activated_at TIMESTAMPTZ, created_by_staff_id TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE(tenant_key,kind,revision)); CREATE UNIQUE INDEX IF NOT EXISTS uq_system_config_active ON system_config_revisions(tenant_key,kind) WHERE state='active'; CREATE TABLE IF NOT EXISTS administrators ( id TEXT PRIMARY KEY, tenant_key TEXT NOT NULL, staff_id TEXT NOT NULL, union_id TEXT NOT NULL DEFAULT '', display_name TEXT NOT NULL DEFAULT '', department TEXT NOT NULL DEFAULT '', role TEXT NOT NULL CHECK(role IN ('super_admin','admin')), status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','disabled')), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE(tenant_key,staff_id)); CREATE TABLE IF NOT EXISTS system_resource_bindings ( id TEXT PRIMARY KEY, tenant_key TEXT NOT NULL, kind TEXT NOT NULL, revision INTEGER NOT NULL CHECK(revision>0), state TEXT NOT NULL CHECK(state IN ('draft','validated','active','retired','failed')), public_config JSONB NOT NULL DEFAULT '{}'::JSONB, validation_code TEXT NOT NULL DEFAULT '', validated_at TIMESTAMPTZ, activated_at TIMESTAMPTZ, created_by_staff_id TEXT NOT NULL DEFAULT '', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE(tenant_key,kind,revision)); CREATE UNIQUE INDEX IF NOT EXISTS uq_resource_binding_active ON system_resource_bindings(tenant_key,kind) WHERE state='active'; CREATE TABLE IF NOT EXISTS config_audit_events ( id TEXT PRIMARY KEY, tenant_key TEXT NOT NULL, actor_staff_id TEXT NOT NULL DEFAULT '', action TEXT NOT NULL, target_kind TEXT NOT NULL, target_revision INTEGER NOT NULL DEFAULT 0, result_code TEXT NOT NULL, metadata JSONB NOT NULL DEFAULT '{}'::JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW());