姚章浩
20 lines
2.2 KiB
SQL
20 lines
2.2 KiB
SQL
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());
|