27 lines
983 B
SQL
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 '';
|