Files
dobotai-platform/db/migrations/000009_directory_profiles.up.sql
2026-07-23 10:55:25 +08:00

27 lines
983 B
SQL

CREATE TABLE IF NOT EXISTS directory_profiles (
tenant_key TEXT NOT NULL,
staff_id TEXT NOT NULL,
union_id TEXT NOT NULL DEFAULT '',
name TEXT NOT NULL,
nickname TEXT NOT NULL DEFAULT '',
job_title TEXT NOT NULL DEFAULT '',
department_ids JSONB NOT NULL DEFAULT '[]'::JSONB,
department_name TEXT NOT NULL DEFAULT '',
main_department_name TEXT NOT NULL DEFAULT '',
region TEXT NOT NULL DEFAULT '',
employment_status TEXT NOT NULL DEFAULT '',
source TEXT NOT NULL CHECK (source IN ('full_sync','on_demand')),
synced_at TIMESTAMPTZ NOT NULL,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (tenant_key, staff_id)
);
CREATE INDEX IF NOT EXISTS idx_directory_profiles_tenant_name
ON directory_profiles (tenant_key, name);
ALTER TABLE messages
ADD COLUMN IF NOT EXISTS department_snapshot TEXT NOT NULL DEFAULT '';
ALTER TABLE messages
ADD COLUMN IF NOT EXISTS region_snapshot TEXT NOT NULL DEFAULT '';