1239 lines
54 KiB
PL/PgSQL
1239 lines
54 KiB
PL/PgSQL
-- ================================================
|
||
-- 养老管理系统数据库结构 (PostgreSQL版本)
|
||
-- ElderCare Management System Database Schema
|
||
-- PostgreSQL 13+ 兼容版本
|
||
-- ================================================
|
||
|
||
-- 确保启用必要的PostgreSQL扩展
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
||
|
||
-- ------------------------------------------------
|
||
-- 1. 权限角色系统 (扩展现有)
|
||
-- ------------------------------------------------
|
||
|
||
-- 基于现有角色系统，增加养老系统特有角色
|
||
INSERT INTO public.ak_roles (name, description, level, is_system) VALUES
|
||
('elder', '入住老人', 5, true),
|
||
('family', '家属用户', 15, true),
|
||
('caregiver', '护理员', 25, true),
|
||
('nurse', '护士', 35, true),
|
||
('doctor', '医生', 45, true),
|
||
('facility_admin', '机构管理员', 55, true),
|
||
('system_admin', '系统管理员', 100, true)
|
||
ON CONFLICT (name) DO NOTHING;
|
||
|
||
-- 添加养老系统权限
|
||
INSERT INTO public.ak_permissions (code, name, resource_type, action, description) VALUES
|
||
-- 老人权限
|
||
('elder.profile.read', '查看个人档案', 'profile', 'read', '查看自己的档案信息'),
|
||
('elder.health.read', '查看健康记录', 'health', 'read', '查看自己的健康数据'),
|
||
|
||
-- 家属权限
|
||
('family.elder.read', '查看老人信息', 'elder', 'read', '查看关联老人的信息'),
|
||
('family.visit.create', '预约探访', 'visit', 'create', '预约探访老人'),
|
||
|
||
-- 护理员权限
|
||
('caregiver.care.manage', '护理管理', 'care', 'manage', '执行日常护理任务'),
|
||
('caregiver.elder.read', '查看老人信息', 'elder', 'read', '查看所有老人信息'),
|
||
|
||
-- 护士权限
|
||
('nurse.health.manage', '健康管理', 'health', 'manage', '管理老人健康记录'),
|
||
('nurse.medication.manage', '用药管理', 'medication', 'manage', '管理用药计划'),
|
||
|
||
-- 医生权限
|
||
('doctor.diagnosis.manage', '诊疗管理', 'diagnosis', 'manage', '管理医疗诊断'),
|
||
('doctor.prescription.manage', '处方管理', 'prescription', 'manage', '开具处方')
|
||
ON CONFLICT (code) DO NOTHING;
|
||
|
||
-- 创建获取当前用户ID的函数（替代auth.uid()）
|
||
CREATE OR REPLACE FUNCTION get_current_user_id()
|
||
RETURNS uuid AS $$
|
||
BEGIN
|
||
-- 这里可以根据实际的认证系统进行调整
|
||
-- 示例：从session变量获取用户ID
|
||
RETURN COALESCE(
|
||
(current_setting('app.current_user_id', true))::uuid,
|
||
'00000000-0000-0000-0000-000000000000'::uuid
|
||
);
|
||
EXCEPTION
|
||
WHEN OTHERS THEN
|
||
RETURN '00000000-0000-0000-0000-000000000000'::uuid;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
-- ------------------------------------------------
|
||
-- 2. 多租户架构 (改造复用)
|
||
-- ------------------------------------------------
|
||
|
||
-- 地区管理 → 机构区域管理 (直接使用 ak_regions 表)
|
||
|
||
-- 学校管理 → 养老机构管理
|
||
-- 基于 ak_schools 表结构
|
||
CREATE TABLE IF NOT EXISTS public.ec_facilities (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
name VARCHAR(128) NOT NULL, -- 机构名称
|
||
region_id uuid REFERENCES public.ak_regions(id), -- 所属地区
|
||
type VARCHAR(32) CHECK (type IN ('nursing_home', 'assisted_living', 'day_care')), -- 机构类型
|
||
license_number VARCHAR(64), -- 许可证号
|
||
contact_phone VARCHAR(32), -- 联系电话
|
||
address TEXT, -- 详细地址
|
||
capacity INT CHECK (capacity > 0), -- 床位总数
|
||
current_occupancy INT DEFAULT 0 CHECK (current_occupancy >= 0), -- 当前入住人数
|
||
admin_id uuid REFERENCES public.ak_users(id), -- 管理员
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 班级管理 → 护理单元管理
|
||
-- 基于 ak_classes 表结构
|
||
CREATE TABLE IF NOT EXISTS public.ec_care_units (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
facility_id uuid NOT NULL REFERENCES public.ec_facilities(id) ON DELETE CASCADE,
|
||
name VARCHAR(32) NOT NULL, -- 护理单元名称(如：A栋1楼、康复区)
|
||
unit_type VARCHAR(32) CHECK (unit_type IN ('general', 'dementia', 'medical', 'rehab')), -- 单元类型
|
||
bed_capacity INT CHECK (bed_capacity > 0), -- 床位数
|
||
current_occupancy INT DEFAULT 0 CHECK (current_occupancy >= 0), -- 当前入住数
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 3. 设备管理系统 (改造复用)
|
||
-- ------------------------------------------------
|
||
|
||
-- 基于 ak_devices 表，扩展为健康监测设备
|
||
CREATE TABLE IF NOT EXISTS public.ec_devices (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
facility_id uuid REFERENCES public.ec_facilities(id),
|
||
elder_id uuid REFERENCES public.ak_users(id), -- 关联老人
|
||
device_type VARCHAR(32) NOT NULL CHECK (device_type IN ('blood_pressure', 'glucose', 'heart_rate', 'fall_detector')), -- 设备类型
|
||
device_name VARCHAR(64), -- 设备名称
|
||
device_mac VARCHAR(64), -- 设备MAC
|
||
location VARCHAR(128), -- 设备位置
|
||
bind_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'maintenance')), -- 状态
|
||
last_sync TIMESTAMP WITH TIME ZONE, -- 最后同步时间
|
||
extra JSONB, -- 设备特有配置
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 4. 老人档案管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 老人基本档案
|
||
CREATE TABLE IF NOT EXISTS public.ec_elders (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid UNIQUE REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 关联用户表
|
||
facility_id uuid REFERENCES public.ec_facilities(id), -- 所属机构
|
||
care_unit_id uuid REFERENCES public.ec_care_units(id), -- 护理单元
|
||
elder_code VARCHAR(32) UNIQUE, -- 老人编号
|
||
name VARCHAR(64) NOT NULL, -- 姓名
|
||
id_card VARCHAR(32), -- 身份证号
|
||
gender VARCHAR(16) CHECK (gender IN ('male', 'female', 'other')), -- 性别
|
||
birthday DATE CHECK (birthday <= CURRENT_DATE), -- 出生日期
|
||
nationality VARCHAR(32), -- 民族
|
||
religion VARCHAR(32), -- 宗教信仰
|
||
marital_status VARCHAR(16) CHECK (marital_status IN ('single', 'married', 'divorced', 'widowed')), -- 婚姻状况
|
||
education VARCHAR(32), -- 文化程度
|
||
occupation VARCHAR(64), -- 职业
|
||
admission_date DATE DEFAULT CURRENT_DATE, -- 入住日期
|
||
care_level VARCHAR(16) CHECK (care_level IN ('self_care', 'assisted', 'full_care', 'dementia')), -- 护理等级
|
||
room_number VARCHAR(16), -- 房间号
|
||
bed_number VARCHAR(16), -- 床位号
|
||
payment_method VARCHAR(16) CHECK (payment_method IN ('self_pay', 'insurance', 'government')), -- 付费方式
|
||
monthly_fee DECIMAL(10,2) CHECK (monthly_fee >= 0), -- 月费用
|
||
deposit DECIMAL(10,2) CHECK (deposit >= 0), -- 押金
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'discharged', 'deceased')), -- 状态
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 家属联系人
|
||
CREATE TABLE IF NOT EXISTS public.ec_family_contacts (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
user_id uuid REFERENCES public.ak_users(id), -- 关联家属用户账号
|
||
name VARCHAR(64) NOT NULL, -- 联系人姓名
|
||
relationship VARCHAR(32) CHECK (relationship IN ('son', 'daughter', 'spouse', 'sibling', 'other')), -- 关系
|
||
phone VARCHAR(32), -- 电话
|
||
email VARCHAR(128), -- 邮箱
|
||
address TEXT, -- 地址
|
||
is_emergency_contact BOOLEAN DEFAULT false, -- 是否紧急联系人
|
||
is_primary BOOLEAN DEFAULT false, -- 是否主要联系人
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 5. 健康管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 健康档案
|
||
CREATE TABLE IF NOT EXISTS public.ec_health_records (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
record_type VARCHAR(32) CHECK (record_type IN ('admission', 'routine', 'emergency', 'discharge')), -- 记录类型
|
||
record_date DATE DEFAULT CURRENT_DATE,
|
||
height_cm INT CHECK (height_cm > 0 AND height_cm < 300), -- 身高
|
||
weight_kg DECIMAL(5,2) CHECK (weight_kg > 0 AND weight_kg < 500), -- 体重
|
||
blood_pressure_systolic INT CHECK (blood_pressure_systolic > 0 AND blood_pressure_systolic < 300), -- 收缩压
|
||
blood_pressure_diastolic INT CHECK (blood_pressure_diastolic > 0 AND blood_pressure_diastolic < 200), -- 舒张压
|
||
heart_rate INT CHECK (heart_rate > 0 AND heart_rate < 300), -- 心率
|
||
temperature DECIMAL(4,1) CHECK (temperature > 30 AND temperature < 45), -- 体温
|
||
blood_sugar DECIMAL(5,2) CHECK (blood_sugar > 0 AND blood_sugar < 50), -- 血糖
|
||
medical_history TEXT, -- 病史
|
||
current_medications TEXT, -- 当前用药
|
||
allergies TEXT, -- 过敏史
|
||
mental_status TEXT, -- 心理状态
|
||
mobility_level VARCHAR(16) CHECK (mobility_level IN ('independent', 'assisted', 'wheelchair', 'bedridden')), -- 行动能力
|
||
cognitive_level VARCHAR(16) CHECK (cognitive_level IN ('normal', 'mild', 'moderate', 'severe')), -- 认知能力
|
||
recorded_by uuid REFERENCES public.ak_users(id), -- 记录人
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 基于现有 ak_biometric_data 表结构扩展
|
||
-- 生命体征监测数据
|
||
CREATE TABLE IF NOT EXISTS public.ec_vital_signs (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
device_id uuid REFERENCES public.ec_devices(id), -- 监测设备
|
||
vital_type VARCHAR(32) CHECK (vital_type IN ('blood_pressure', 'heart_rate', 'temperature', 'oxygen', 'glucose')), -- 体征类型
|
||
systolic_pressure INT CHECK (systolic_pressure > 0 AND systolic_pressure < 300), -- 收缩压
|
||
diastolic_pressure INT CHECK (diastolic_pressure > 0 AND diastolic_pressure < 200), -- 舒张压
|
||
heart_rate INT CHECK (heart_rate > 0 AND heart_rate < 300), -- 心率
|
||
temperature DECIMAL(4,1) CHECK (temperature > 30 AND temperature < 45), -- 体温
|
||
oxygen_saturation INT CHECK (oxygen_saturation > 0 AND oxygen_saturation <= 100), -- 血氧饱和度
|
||
glucose_level DECIMAL(5,2) CHECK (glucose_level > 0 AND glucose_level < 50), -- 血糖
|
||
measured_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- 测量时间
|
||
measured_by uuid REFERENCES public.ak_users(id), -- 测量人
|
||
notes TEXT, -- 备注
|
||
is_abnormal BOOLEAN DEFAULT false, -- 是否异常
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 6. 护理管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 护理计划 (基于 ak_training_plans 改造)
|
||
CREATE TABLE IF NOT EXISTS public.ec_care_plans (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
plan_name VARCHAR(128) NOT NULL, -- 计划名称
|
||
plan_type VARCHAR(32) CHECK (plan_type IN ('daily', 'medical', 'rehabilitation', 'nutrition')), -- 计划类型
|
||
description TEXT, -- 描述
|
||
start_date DATE DEFAULT CURRENT_DATE, -- 开始日期
|
||
end_date DATE, -- 结束日期
|
||
frequency VARCHAR(32) CHECK (frequency IN ('daily', 'weekly', 'monthly')), -- 频率
|
||
assigned_caregiver uuid REFERENCES public.ak_users(id), -- 负责护理员
|
||
created_by uuid REFERENCES public.ak_users(id), -- 创建人
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'completed')), -- 状态
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_end_date CHECK (end_date IS NULL OR end_date >= start_date)
|
||
);
|
||
|
||
-- 护理任务 (基于 ak_assignments 改造)
|
||
CREATE TABLE IF NOT EXISTS public.ec_care_tasks (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
care_plan_id uuid REFERENCES public.ec_care_plans(id),
|
||
task_name VARCHAR(128) NOT NULL, -- 任务名称
|
||
task_type VARCHAR(32) CHECK (task_type IN ('medication', 'hygiene', 'mobility', 'nutrition', 'social')), -- 任务类型
|
||
description TEXT, -- 任务描述
|
||
scheduled_time TIMESTAMP WITH TIME ZONE, -- 计划执行时间
|
||
assigned_to uuid REFERENCES public.ak_users(id), -- 分配给
|
||
priority VARCHAR(16) DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')), -- 优先级
|
||
estimated_duration INT CHECK (estimated_duration > 0), -- 预计耗时(分钟)
|
||
status VARCHAR(16) DEFAULT 'pending' CHECK (status IN ('pending', 'in_progress', 'completed', 'cancelled')), -- 状态
|
||
due_date TIMESTAMP WITH TIME ZONE, -- 截止时间
|
||
created_by uuid REFERENCES public.ak_users(id), -- 创建人
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 护理记录 (基于 ak_assignment_submissions 改造)
|
||
CREATE TABLE IF NOT EXISTS public.ec_care_records (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
task_id uuid REFERENCES public.ec_care_tasks(id) ON DELETE CASCADE,
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
caregiver_id uuid NOT NULL REFERENCES public.ak_users(id) ON DELETE CASCADE, -- 护理员
|
||
start_time TIMESTAMP WITH TIME ZONE, -- 开始时间
|
||
end_time TIMESTAMP WITH TIME ZONE, -- 结束时间
|
||
actual_duration INT CHECK (actual_duration > 0), -- 实际耗时(分钟)
|
||
care_content TEXT, -- 护理内容
|
||
elder_condition TEXT, -- 老人状况描述
|
||
issues_notes TEXT, -- 问题或注意事项
|
||
photo_urls TEXT[], -- 相关照片
|
||
status VARCHAR(16) DEFAULT 'completed' CHECK (status IN ('completed', 'incomplete', 'cancelled')), -- 状态
|
||
rating INT CHECK (rating >= 1 AND rating <= 5), -- 护理质量评分(1-5)
|
||
supervisor_notes TEXT, -- 主管备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_time_order CHECK (end_time IS NULL OR end_time >= start_time)
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 7. 医疗管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 医疗记录
|
||
CREATE TABLE IF NOT EXISTS public.ec_medical_records (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
doctor_id uuid REFERENCES public.ak_users(id), -- 医生
|
||
visit_type VARCHAR(32) CHECK (visit_type IN ('routine', 'emergency', 'consultation', 'follow_up')), -- 就诊类型
|
||
visit_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- 就诊时间
|
||
chief_complaint TEXT, -- 主诉
|
||
symptoms TEXT, -- 症状
|
||
diagnosis TEXT, -- 诊断
|
||
treatment_plan TEXT, -- 治疗方案
|
||
prescription TEXT, -- 处方
|
||
follow_up_date DATE, -- 复诊日期
|
||
notes TEXT, -- 医生备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 用药管理
|
||
CREATE TABLE IF NOT EXISTS public.ec_medications (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
medical_record_id uuid REFERENCES public.ec_medical_records(id), -- 关联医疗记录
|
||
medication_name VARCHAR(128) NOT NULL, -- 药品名称
|
||
dosage VARCHAR(64), -- 剂量
|
||
frequency VARCHAR(64), -- 用药频率
|
||
route VARCHAR(32) CHECK (route IN ('oral', 'injection', 'topical')), -- 给药途径
|
||
start_date DATE DEFAULT CURRENT_DATE, -- 开始日期
|
||
end_date DATE, -- 结束日期
|
||
prescribed_by uuid REFERENCES public.ak_users(id), -- 开药医生
|
||
instructions TEXT, -- 用药说明
|
||
side_effects TEXT, -- 副作用注意
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'discontinued')), -- 状态
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_medication_dates CHECK (end_date IS NULL OR end_date >= start_date)
|
||
);
|
||
|
||
-- 用药记录
|
||
CREATE TABLE IF NOT EXISTS public.ec_medication_logs (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
medication_id uuid NOT NULL REFERENCES public.ec_medications(id) ON DELETE CASCADE,
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
administered_by uuid REFERENCES public.ak_users(id), -- 给药人
|
||
scheduled_time TIMESTAMP WITH TIME ZONE, -- 计划给药时间
|
||
actual_time TIMESTAMP WITH TIME ZONE, -- 实际给药时间
|
||
dosage_given VARCHAR(64), -- 实际给药剂量
|
||
status VARCHAR(16) CHECK (status IN ('given', 'refused', 'missed', 'delayed')), -- 状态
|
||
notes TEXT, -- 备注
|
||
side_effects_observed TEXT, -- 观察到的副作用
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 8. 服务管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 餐饮服务
|
||
CREATE TABLE IF NOT EXISTS public.ec_meal_services (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
facility_id uuid NOT NULL REFERENCES public.ec_facilities(id) ON DELETE CASCADE,
|
||
meal_date DATE NOT NULL DEFAULT CURRENT_DATE, -- 用餐日期
|
||
meal_type VARCHAR(16) CHECK (meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')), -- 餐次类型
|
||
menu_items JSONB, -- 菜单项目
|
||
nutritional_info JSONB, -- 营养信息
|
||
special_diet_options JSONB, -- 特殊饮食选项
|
||
created_by uuid REFERENCES public.ak_users(id), -- 创建人
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 用餐记录
|
||
CREATE TABLE IF NOT EXISTS public.ec_meal_records (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
meal_service_id uuid REFERENCES public.ec_meal_services(id),
|
||
meal_type VARCHAR(16) CHECK (meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')), -- 餐次
|
||
meal_date DATE DEFAULT CURRENT_DATE, -- 用餐日期
|
||
food_items JSONB, -- 实际用餐项目
|
||
appetite_level VARCHAR(16) CHECK (appetite_level IN ('good', 'fair', 'poor')), -- 食欲
|
||
amount_consumed VARCHAR(16) CHECK (amount_consumed IN ('full', 'half', 'quarter', 'none')), -- 进食量
|
||
assistance_needed BOOLEAN DEFAULT false, -- 是否需要协助
|
||
assisted_by uuid REFERENCES public.ak_users(id), -- 协助人员
|
||
notes TEXT, -- 备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 活动安排
|
||
CREATE TABLE IF NOT EXISTS public.ec_activities (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
facility_id uuid NOT NULL REFERENCES public.ec_facilities(id) ON DELETE CASCADE,
|
||
activity_name VARCHAR(128) NOT NULL, -- 活动名称
|
||
activity_type VARCHAR(32) CHECK (activity_type IN ('recreation', 'therapy', 'education', 'social', 'exercise')), -- 活动类型
|
||
description TEXT, -- 活动描述
|
||
location VARCHAR(128), -- 活动地点
|
||
start_time TIMESTAMP WITH TIME ZONE, -- 开始时间
|
||
end_time TIMESTAMP WITH TIME ZONE, -- 结束时间
|
||
max_participants INT CHECK (max_participants > 0), -- 最大参与人数
|
||
instructor uuid REFERENCES public.ak_users(id), -- 活动指导员
|
||
requirements TEXT, -- 参与要求
|
||
materials_needed TEXT, -- 所需物品
|
||
status VARCHAR(16) DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'in_progress', 'completed', 'cancelled')), -- 状态
|
||
created_by uuid REFERENCES public.ak_users(id), -- 创建人
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_activity_time CHECK (end_time IS NULL OR end_time >= start_time)
|
||
);
|
||
|
||
-- 活动参与记录
|
||
CREATE TABLE IF NOT EXISTS public.ec_activity_participations (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
activity_id uuid NOT NULL REFERENCES public.ec_activities(id) ON DELETE CASCADE,
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
participation_status VARCHAR(16) CHECK (participation_status IN ('registered', 'attended', 'absent', 'cancelled')), -- 参与状态
|
||
enjoyment_level VARCHAR(16) CHECK (enjoyment_level IN ('excellent', 'good', 'fair', 'poor')), -- 参与满意度
|
||
behavior_notes TEXT, -- 行为观察记录
|
||
staff_notes TEXT, -- 工作人员备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 访客管理
|
||
CREATE TABLE IF NOT EXISTS public.ec_visits (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
visitor_name VARCHAR(64) NOT NULL, -- 访客姓名
|
||
visitor_relationship VARCHAR(32), -- 与老人关系
|
||
visitor_id_card VARCHAR(32), -- 访客身份证
|
||
visitor_phone VARCHAR(32), -- 访客电话
|
||
visit_date DATE DEFAULT CURRENT_DATE, -- 访问日期
|
||
start_time TIME, -- 开始时间
|
||
end_time TIME, -- 结束时间
|
||
visit_purpose TEXT, -- 访问目的
|
||
items_brought TEXT, -- 携带物品
|
||
approved_by uuid REFERENCES public.ak_users(id), -- 审批人
|
||
check_in_time TIMESTAMP WITH TIME ZONE, -- 实际签到时间
|
||
check_out_time TIMESTAMP WITH TIME ZONE, -- 实际签出时间
|
||
status VARCHAR(16) DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'in_progress', 'completed', 'cancelled')), -- 状态
|
||
notes TEXT, -- 备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_visit_time CHECK (end_time IS NULL OR end_time >= start_time)
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 9. AI分析与报告系统 (基于现有系统改造)
|
||
-- ------------------------------------------------
|
||
|
||
-- 健康预警系统 (基于 ak_ai_reports 改造)
|
||
CREATE TABLE IF NOT EXISTS public.ec_health_alerts (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
alert_type VARCHAR(32) CHECK (alert_type IN ('vital_signs', 'medication', 'fall_risk', 'behavior')), -- 预警类型
|
||
severity VARCHAR(16) CHECK (severity IN ('low', 'medium', 'high', 'critical')), -- 严重程度
|
||
title VARCHAR(128), -- 预警标题
|
||
description TEXT, -- 预警描述
|
||
data_source JSONB, -- 触发预警的数据
|
||
ai_analysis JSONB, -- AI分析结果
|
||
recommendations TEXT, -- 建议措施
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'acknowledged', 'resolved')), -- 状态
|
||
acknowledged_by uuid REFERENCES public.ak_users(id), -- 确认人
|
||
acknowledged_at TIMESTAMP WITH TIME ZONE, -- 确认时间
|
||
resolved_by uuid REFERENCES public.ak_users(id), -- 处理人
|
||
resolved_at TIMESTAMP WITH TIME ZONE, -- 处理时间
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 行为分析 (基于 ak_video_analysis 改造)
|
||
CREATE TABLE IF NOT EXISTS public.ec_behavior_analysis (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE,
|
||
device_id uuid REFERENCES public.ec_devices(id), -- 监控设备
|
||
analysis_type VARCHAR(32) CHECK (analysis_type IN ('fall_detection', 'wandering', 'sleep_pattern', 'activity_level')), -- 分析类型
|
||
video_url TEXT, -- 视频链接
|
||
timestamp_start TIMESTAMP WITH TIME ZONE, -- 分析开始时间
|
||
timestamp_end TIMESTAMP WITH TIME ZONE, -- 分析结束时间
|
||
ai_results JSONB, -- AI分析结果
|
||
confidence_score DECIMAL(3,2) CHECK (confidence_score >= 0 AND confidence_score <= 1), -- 置信度
|
||
is_anomaly BOOLEAN DEFAULT false, -- 是否异常
|
||
alert_generated BOOLEAN DEFAULT false, -- 是否生成预警
|
||
reviewed_by uuid REFERENCES public.ak_users(id), -- 审核人
|
||
review_notes TEXT, -- 审核备注
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT check_analysis_time CHECK (timestamp_end IS NULL OR timestamp_end >= timestamp_start)
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 10. 系统配置与管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 通知模板系统 (基于现有通知系统扩展)
|
||
CREATE TABLE IF NOT EXISTS public.ec_notification_templates (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
template_code VARCHAR(64) UNIQUE NOT NULL, -- 模板代码
|
||
template_name VARCHAR(128) NOT NULL, -- 模板名称
|
||
category VARCHAR(32) CHECK (category IN ('health', 'care', 'visit', 'emergency', 'system')), -- 分类
|
||
subject_template TEXT, -- 标题模板
|
||
content_template TEXT, -- 内容模板
|
||
variables JSONB, -- 模板变量定义
|
||
delivery_methods VARCHAR(16)[] CHECK (delivery_methods <@ ARRAY['app', 'sms', 'email']), -- 发送方式
|
||
is_active BOOLEAN DEFAULT true, -- 是否启用
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 通知发送记录
|
||
CREATE TABLE IF NOT EXISTS public.ec_notification_logs (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
template_id uuid REFERENCES public.ec_notification_templates(id),
|
||
recipient_id uuid REFERENCES public.ak_users(id), -- 接收人
|
||
elder_id uuid REFERENCES public.ec_elders(id), -- 关联老人
|
||
subject VARCHAR(256), -- 实际标题
|
||
content TEXT, -- 实际内容
|
||
delivery_method VARCHAR(16) CHECK (delivery_method IN ('app', 'sms', 'email')), -- 发送方式
|
||
delivery_status VARCHAR(16) DEFAULT 'pending' CHECK (delivery_status IN ('pending', 'sent', 'failed', 'delivered')), -- 发送状态
|
||
sent_at TIMESTAMP WITH TIME ZONE, -- 发送时间
|
||
delivered_at TIMESTAMP WITH TIME ZONE, -- 送达时间
|
||
error_message TEXT, -- 错误信息
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 11. 索引优化
|
||
-- ------------------------------------------------
|
||
|
||
-- 老人档案相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_elders_facility_id ON public.ec_elders(facility_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_elders_care_unit_id ON public.ec_elders(care_unit_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_elders_status ON public.ec_elders(status);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_elders_elder_code ON public.ec_elders(elder_code);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_elders_user_id ON public.ec_elders(user_id);
|
||
|
||
-- 护理任务相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_tasks_elder_id ON public.ec_care_tasks(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_tasks_assigned_to ON public.ec_care_tasks(assigned_to);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_tasks_status ON public.ec_care_tasks(status);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_tasks_scheduled_time ON public.ec_care_tasks(scheduled_time);
|
||
|
||
-- 生命体征相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_vital_signs_elder_id ON public.ec_vital_signs(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_vital_signs_measured_at ON public.ec_vital_signs(measured_at);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_vital_signs_vital_type ON public.ec_vital_signs(vital_type);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_vital_signs_is_abnormal ON public.ec_vital_signs(is_abnormal);
|
||
|
||
-- 护理记录相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_records_elder_id ON public.ec_care_records(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_records_caregiver_id ON public.ec_care_records(caregiver_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_care_records_created_at ON public.ec_care_records(created_at);
|
||
|
||
-- 用药相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_medications_elder_id ON public.ec_medications(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_medication_logs_elder_id ON public.ec_medication_logs(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_medication_logs_scheduled_time ON public.ec_medication_logs(scheduled_time);
|
||
|
||
-- 健康预警相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_health_alerts_elder_id ON public.ec_health_alerts(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_health_alerts_status ON public.ec_health_alerts(status);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_health_alerts_severity ON public.ec_health_alerts(severity);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_health_alerts_created_at ON public.ec_health_alerts(created_at);
|
||
|
||
-- 家属联系人索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_family_contacts_elder_id ON public.ec_family_contacts(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_family_contacts_user_id ON public.ec_family_contacts(user_id);
|
||
|
||
-- 活动相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_activities_facility_id ON public.ec_activities(facility_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_activities_start_time ON public.ec_activities(start_time);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_activity_participations_elder_id ON public.ec_activity_participations(elder_id);
|
||
|
||
-- ------------------------------------------------
|
||
-- 12. 触发器和函数
|
||
-- ------------------------------------------------
|
||
|
||
-- 自动更新时间戳函数
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = CURRENT_TIMESTAMP;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 为需要自动更新时间戳的表创建触发器
|
||
DROP TRIGGER IF EXISTS trigger_ec_facilities_updated_at ON public.ec_facilities;
|
||
CREATE TRIGGER trigger_ec_facilities_updated_at
|
||
BEFORE UPDATE ON public.ec_facilities
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_care_units_updated_at ON public.ec_care_units;
|
||
CREATE TRIGGER trigger_ec_care_units_updated_at
|
||
BEFORE UPDATE ON public.ec_care_units
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_devices_updated_at ON public.ec_devices;
|
||
CREATE TRIGGER trigger_ec_devices_updated_at
|
||
BEFORE UPDATE ON public.ec_devices
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_elders_updated_at ON public.ec_elders;
|
||
CREATE TRIGGER trigger_ec_elders_updated_at
|
||
BEFORE UPDATE ON public.ec_elders
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_family_contacts_updated_at ON public.ec_family_contacts;
|
||
CREATE TRIGGER trigger_ec_family_contacts_updated_at
|
||
BEFORE UPDATE ON public.ec_family_contacts
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_health_records_updated_at ON public.ec_health_records;
|
||
CREATE TRIGGER trigger_ec_health_records_updated_at
|
||
BEFORE UPDATE ON public.ec_health_records
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_care_plans_updated_at ON public.ec_care_plans;
|
||
CREATE TRIGGER trigger_ec_care_plans_updated_at
|
||
BEFORE UPDATE ON public.ec_care_plans
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_care_tasks_updated_at ON public.ec_care_tasks;
|
||
CREATE TRIGGER trigger_ec_care_tasks_updated_at
|
||
BEFORE UPDATE ON public.ec_care_tasks
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
DROP TRIGGER IF EXISTS trigger_ec_care_records_updated_at ON public.ec_care_records;
|
||
CREATE TRIGGER trigger_ec_care_records_updated_at
|
||
BEFORE UPDATE ON public.ec_care_records
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 自动生成健康预警
|
||
CREATE OR REPLACE FUNCTION generate_health_alert()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
-- 检查生命体征异常并自动生成预警
|
||
IF NEW.is_abnormal = TRUE THEN
|
||
INSERT INTO public.ec_health_alerts (
|
||
elder_id,
|
||
alert_type,
|
||
severity,
|
||
title,
|
||
description,
|
||
data_source
|
||
) VALUES (
|
||
NEW.elder_id,
|
||
'vital_signs',
|
||
'medium',
|
||
'生命体征异常',
|
||
'检测到异常的生命体征数据',
|
||
jsonb_build_object(
|
||
'vital_signs_id', NEW.id,
|
||
'vital_type', NEW.vital_type,
|
||
'measured_at', NEW.measured_at
|
||
)
|
||
);
|
||
END IF;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 先安全删除再创建触发器，避免重复报错
|
||
DROP TRIGGER IF EXISTS trigger_generate_health_alert ON public.ec_vital_signs;
|
||
CREATE TRIGGER trigger_generate_health_alert
|
||
AFTER INSERT ON public.ec_vital_signs
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION generate_health_alert();
|
||
|
||
-- 更新机构入住率
|
||
CREATE OR REPLACE FUNCTION update_facility_occupancy()
|
||
RETURNS TRIGGER AS $$
|
||
DECLARE
|
||
facility_id_val uuid;
|
||
BEGIN
|
||
-- 获取机构ID
|
||
IF TG_OP = 'INSERT' THEN
|
||
facility_id_val := NEW.facility_id;
|
||
ELSIF TG_OP = 'DELETE' THEN
|
||
facility_id_val := OLD.facility_id;
|
||
ELSE
|
||
facility_id_val := COALESCE(NEW.facility_id, OLD.facility_id);
|
||
END IF;
|
||
|
||
-- 更新机构当前入住人数
|
||
UPDATE public.ec_facilities
|
||
SET current_occupancy = (
|
||
SELECT COUNT(*)
|
||
FROM public.ec_elders
|
||
WHERE facility_id = facility_id_val
|
||
AND status = 'active'
|
||
),
|
||
updated_at = CURRENT_TIMESTAMP
|
||
WHERE id = facility_id_val;
|
||
|
||
RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 更新护理单元入住率
|
||
CREATE OR REPLACE FUNCTION update_care_unit_occupancy()
|
||
RETURNS TRIGGER AS $$
|
||
DECLARE
|
||
care_unit_id_val uuid;
|
||
BEGIN
|
||
-- 获取护理单元ID
|
||
IF TG_OP = 'INSERT' THEN
|
||
care_unit_id_val := NEW.care_unit_id;
|
||
ELSIF TG_OP = 'DELETE' THEN
|
||
care_unit_id_val := OLD.care_unit_id;
|
||
ELSE
|
||
care_unit_id_val := COALESCE(NEW.care_unit_id, OLD.care_unit_id);
|
||
END IF;
|
||
-- 如果有护理单元，更新入住人数
|
||
IF care_unit_id_val IS NOT NULL THEN
|
||
UPDATE public.ec_care_units
|
||
SET current_occupancy = (
|
||
SELECT COUNT(*)
|
||
FROM public.ec_elders
|
||
WHERE care_unit_id = care_unit_id_val
|
||
AND status = 'active'
|
||
),
|
||
updated_at = CURRENT_TIMESTAMP
|
||
WHERE id = care_unit_id_val;
|
||
END IF;
|
||
RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- ------------------------------------------------
|
||
-- 7. 医疗预约管理
|
||
-- ------------------------------------------------
|
||
|
||
-- 医疗预约表（老人、家属、医生均可用）
|
||
CREATE TABLE IF NOT EXISTS public.ec_appointments (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE, -- 关联老人
|
||
doctor_id uuid REFERENCES public.ak_users(id), -- 关联医生
|
||
scheduled_time TIMESTAMP WITH TIME ZONE NOT NULL, -- 预约时间
|
||
appointment_type VARCHAR(32) NOT NULL CHECK (appointment_type IN ('consultation', 'followup', 'treatment', 'checkup', 'other')), -- 预约类型
|
||
status VARCHAR(16) NOT NULL DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'completed', 'cancelled', 'no_show')), -- 状态
|
||
notes TEXT, -- 备注
|
||
created_by uuid REFERENCES public.ak_users(id), -- 创建人（预约发起人）
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 启用 RLS
|
||
ALTER TABLE public.ec_appointments ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 预约插入策略：authenticated 只能为自己/关联老人预约
|
||
DROP POLICY IF EXISTS appointments_insert_authenticated ON public.ec_appointments;
|
||
CREATE POLICY appointments_insert_authenticated ON public.ec_appointments
|
||
FOR INSERT TO authenticated
|
||
WITH CHECK (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (SELECT elder_id FROM public.ec_family_contacts WHERE user_id = get_current_user_id())
|
||
OR id IN (SELECT DISTINCT elder_id FROM public.ec_care_tasks WHERE assigned_to = get_current_user_id())
|
||
)
|
||
OR doctor_id = get_current_user_id()
|
||
OR created_by = get_current_user_id()
|
||
);
|
||
|
||
-- 预约查询策略：authenticated 只能查自己/关联老人/自己为医生的预约
|
||
DROP POLICY IF EXISTS appointments_select_authenticated ON public.ec_appointments;
|
||
CREATE POLICY appointments_select_authenticated ON public.ec_appointments
|
||
FOR SELECT TO authenticated
|
||
USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (SELECT elder_id FROM public.ec_family_contacts WHERE user_id = get_current_user_id())
|
||
OR id IN (SELECT DISTINCT elder_id FROM public.ec_care_tasks WHERE assigned_to = get_current_user_id())
|
||
)
|
||
OR doctor_id = get_current_user_id()
|
||
OR created_by = get_current_user_id()
|
||
);
|
||
|
||
-- 预约更新策略：authenticated 只能改自己/关联老人/自己为医生的预约
|
||
DROP POLICY IF EXISTS appointments_update_authenticated ON public.ec_appointments;
|
||
CREATE POLICY appointments_update_authenticated ON public.ec_appointments
|
||
FOR UPDATE TO authenticated
|
||
USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (SELECT elder_id FROM public.ec_family_contacts WHERE user_id = get_current_user_id())
|
||
OR id IN (SELECT DISTINCT elder_id FROM public.ec_care_tasks WHERE assigned_to = get_current_user_id())
|
||
)
|
||
OR doctor_id = get_current_user_id()
|
||
OR created_by = get_current_user_id()
|
||
);
|
||
|
||
-- 自动更新时间戳触发器（如有需要可复用 update_updated_at_column）
|
||
DROP TRIGGER IF EXISTS trigger_ec_appointments_updated_at ON public.ec_appointments;
|
||
CREATE TRIGGER trigger_ec_appointments_updated_at
|
||
BEFORE UPDATE ON public.ec_appointments
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- ------------------------------------------------
|
||
-- 13. RLS (行级安全策略) - PostgreSQL兼容版本
|
||
-- ------------------------------------------------
|
||
|
||
|
||
|
||
-- 启用 RLS
|
||
ALTER TABLE public.ec_elders ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_care_records ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_vital_signs ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_health_alerts ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_family_contacts ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_care_tasks ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_service_requests ENABLE ROW LEVEL SECURITY;
|
||
ALTER TABLE public.ec_appointments ENABLE ROW LEVEL SECURITY;
|
||
|
||
|
||
-- 老人只能查看自己的数据
|
||
DROP POLICY IF EXISTS elder_own_data ON public.ec_elders;
|
||
CREATE POLICY elder_own_data ON public.ec_elders
|
||
FOR ALL USING (user_id = get_current_user_id());
|
||
|
||
-- 家属只能查看关联老人的数据
|
||
DROP POLICY IF EXISTS family_elder_data ON public.ec_elders;
|
||
CREATE POLICY family_elder_data ON public.ec_elders
|
||
FOR SELECT USING (
|
||
id IN (
|
||
SELECT elder_id
|
||
FROM public.ec_family_contacts
|
||
WHERE user_id = get_current_user_id()
|
||
)
|
||
);
|
||
|
||
-- 护理员可以查看所有老人的数据
|
||
DROP POLICY IF EXISTS caregiver_assigned_elders ON public.ec_elders;
|
||
CREATE POLICY caregiver_assigned_elders ON public.ec_elders
|
||
FOR SELECT USING (
|
||
EXISTS (
|
||
SELECT 1 FROM public.ak_user_roles ur
|
||
JOIN public.ak_roles r ON ur.role_id = r.id
|
||
WHERE ur.user_id = get_current_user_id()
|
||
AND r.name = 'caregiver'
|
||
)
|
||
OR
|
||
id IN (
|
||
SELECT DISTINCT elder_id
|
||
FROM public.ec_care_tasks
|
||
WHERE assigned_to = get_current_user_id()
|
||
)
|
||
);
|
||
|
||
-- 机构管理员可以查看本机构所有数据
|
||
DROP POLICY IF EXISTS facility_admin_all_data ON public.ec_elders;
|
||
CREATE POLICY facility_admin_all_data ON public.ec_elders
|
||
FOR ALL USING (
|
||
facility_id IN (
|
||
SELECT id FROM public.ec_facilities WHERE admin_id = get_current_user_id()
|
||
)
|
||
);
|
||
|
||
-- 护理记录访问策略
|
||
DROP POLICY IF EXISTS care_records_access ON public.ec_care_records;
|
||
CREATE POLICY care_records_access ON public.ec_care_records
|
||
FOR ALL USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (
|
||
SELECT elder_id
|
||
FROM public.ec_family_contacts
|
||
WHERE user_id = get_current_user_id()
|
||
)
|
||
OR id IN (
|
||
SELECT DISTINCT elder_id
|
||
FROM public.ec_care_tasks
|
||
WHERE assigned_to = get_current_user_id()
|
||
)
|
||
)
|
||
OR caregiver_id = get_current_user_id()
|
||
);
|
||
|
||
-- 生命体征访问策略
|
||
DROP POLICY IF EXISTS vital_signs_access ON public.ec_vital_signs;
|
||
CREATE POLICY vital_signs_access ON public.ec_vital_signs
|
||
FOR ALL USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (
|
||
SELECT elder_id
|
||
FROM public.ec_family_contacts
|
||
WHERE user_id = get_current_user_id()
|
||
)
|
||
OR id IN (
|
||
SELECT DISTINCT elder_id
|
||
FROM public.ec_care_tasks
|
||
WHERE assigned_to = get_current_user_id()
|
||
)
|
||
)
|
||
OR measured_by = get_current_user_id()
|
||
);
|
||
|
||
-- 健康预警访问策略
|
||
DROP POLICY IF EXISTS health_alerts_access ON public.ec_health_alerts;
|
||
CREATE POLICY health_alerts_access ON public.ec_health_alerts
|
||
FOR ALL USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (
|
||
SELECT elder_id
|
||
FROM public.ec_family_contacts
|
||
WHERE user_id = get_current_user_id()
|
||
)
|
||
OR id IN (
|
||
SELECT DISTINCT elder_id
|
||
FROM public.ec_care_tasks
|
||
WHERE assigned_to = get_current_user_id()
|
||
)
|
||
)
|
||
);
|
||
|
||
-- ec_service_requests RLS 策略：允许 authenticated 用户插入、查询、更新自己的请求
|
||
DROP POLICY IF EXISTS service_requests_insert_authenticated ON public.ec_service_requests;
|
||
CREATE POLICY service_requests_insert_authenticated ON public.ec_service_requests
|
||
FOR INSERT TO authenticated
|
||
WITH CHECK (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (SELECT elder_id FROM public.ec_family_contacts WHERE user_id = get_current_user_id())
|
||
)
|
||
);
|
||
|
||
DROP POLICY IF EXISTS service_requests_select_authenticated ON public.ec_service_requests;
|
||
CREATE POLICY service_requests_select_authenticated ON public.ec_service_requests
|
||
FOR SELECT TO authenticated
|
||
USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (SELECT elder_id FROM public.ec_family_contacts WHERE user_id = get_current_user_id())
|
||
)
|
||
OR created_by = get_current_user_id()
|
||
OR handled_by = get_current_user_id()
|
||
);
|
||
|
||
DROP POLICY IF EXISTS service_requests_update_authenticated ON public.ec_service_requests;
|
||
CREATE POLICY service_requests_update_authenticated ON public.ec_service_requests
|
||
FOR UPDATE TO authenticated
|
||
USING (
|
||
elder_id IN (
|
||
SELECT id FROM public.ec_elders
|
||
WHERE user_id = get_current_user_id()
|
||
OR id IN (SELECT elder_id FROM public.ec_family_contacts WHERE user_id = get_current_user_id())
|
||
)
|
||
OR created_by = get_current_user_id()
|
||
OR handled_by = get_current_user_id()
|
||
);
|
||
|
||
-- ------------------------------------------------
|
||
-- 14. 视图和查询优化
|
||
-- ------------------------------------------------
|
||
|
||
-- 老人详细信息视图
|
||
CREATE OR REPLACE VIEW public.vw_elder_details AS
|
||
SELECT
|
||
e.id,
|
||
e.elder_code,
|
||
e.name,
|
||
e.gender,
|
||
e.birthday,
|
||
EXTRACT(YEAR FROM AGE(e.birthday)) as age,
|
||
e.care_level,
|
||
e.room_number,
|
||
e.bed_number,
|
||
e.status,
|
||
f.name as facility_name,
|
||
cu.name as care_unit_name,
|
||
fc.name as primary_contact_name,
|
||
fc.phone as primary_contact_phone,
|
||
e.created_at,
|
||
e.updated_at
|
||
FROM public.ec_elders e
|
||
LEFT JOIN public.ec_facilities f ON e.facility_id = f.id
|
||
LEFT JOIN public.ec_care_units cu ON e.care_unit_id = cu.id
|
||
LEFT JOIN public.ec_family_contacts fc ON e.id = fc.elder_id AND fc.is_primary = true;
|
||
|
||
-- 护理任务统计视图
|
||
CREATE OR REPLACE VIEW public.vw_care_task_stats AS
|
||
SELECT
|
||
e.id as elder_id,
|
||
e.name as elder_name,
|
||
COUNT(ct.id) as total_tasks,
|
||
COUNT(CASE WHEN ct.status = 'completed' THEN 1 END) as completed_tasks,
|
||
COUNT(CASE WHEN ct.status = 'pending' THEN 1 END) as pending_tasks,
|
||
COUNT(CASE WHEN ct.status = 'overdue' AND ct.due_date < CURRENT_TIMESTAMP THEN 1 END) as overdue_tasks
|
||
FROM public.ec_elders e
|
||
LEFT JOIN public.ec_care_tasks ct ON e.id = ct.elder_id
|
||
GROUP BY e.id, e.name;
|
||
|
||
-- 健康监测摘要视图
|
||
CREATE OR REPLACE VIEW public.vw_health_summary AS
|
||
SELECT
|
||
e.id as elder_id,
|
||
e.name as elder_name,
|
||
vs.vital_type,
|
||
vs.measured_at as last_measurement,
|
||
vs.is_abnormal,
|
||
COUNT(ha.id) as active_alerts
|
||
FROM public.ec_elders e
|
||
LEFT JOIN public.ec_vital_signs vs ON e.id = vs.elder_id
|
||
LEFT JOIN public.ec_health_alerts ha ON e.id = ha.elder_id AND ha.status = 'active'
|
||
WHERE vs.measured_at = (
|
||
SELECT MAX(measured_at)
|
||
FROM public.ec_vital_signs vs2
|
||
WHERE vs2.elder_id = e.id AND vs2.vital_type = vs.vital_type
|
||
)
|
||
GROUP BY e.id, e.name, vs.vital_type, vs.measured_at, vs.is_abnormal;
|
||
|
||
-- ================================================
|
||
-- 数据库初始化完成
|
||
-- ================================================
|
||
|
||
-- 创建必要的序列和默认值
|
||
CREATE SEQUENCE IF NOT EXISTS public.elder_code_seq START 1000;
|
||
|
||
-- 创建生成老人编号的函数
|
||
CREATE OR REPLACE FUNCTION generate_elder_code()
|
||
RETURNS VARCHAR(32) AS $$
|
||
BEGIN
|
||
RETURN 'E' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || LPAD(nextval('public.elder_code_seq')::text, 4, '0');
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 为老人表添加自动生成编号的触发器
|
||
CREATE OR REPLACE FUNCTION set_elder_code()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF NEW.elder_code IS NULL THEN
|
||
NEW.elder_code := generate_elder_code();
|
||
END IF;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
DROP TRIGGER IF EXISTS trigger_set_elder_code ON public.ec_elders;
|
||
CREATE TRIGGER trigger_set_elder_code
|
||
BEFORE INSERT ON public.ec_elders
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION set_elder_code();
|
||
|
||
-- 创建数据完整性检查函数
|
||
CREATE OR REPLACE FUNCTION check_elder_data_integrity()
|
||
RETURNS BOOLEAN AS $$
|
||
DECLARE
|
||
integrity_issues INTEGER := 0;
|
||
BEGIN
|
||
-- 检查孤立的护理记录
|
||
SELECT COUNT(*) INTO integrity_issues
|
||
FROM public.ec_care_records cr
|
||
LEFT JOIN public.ec_elders e ON cr.elder_id = e.id
|
||
WHERE e.id IS NULL;
|
||
|
||
IF integrity_issues > 0 THEN
|
||
RAISE WARNING '发现 % 条孤立的护理记录', integrity_issues;
|
||
RETURN FALSE;
|
||
END IF;
|
||
|
||
-- 检查异常的生命体征数据
|
||
SELECT COUNT(*) INTO integrity_issues
|
||
FROM public.ec_vital_signs
|
||
WHERE (heart_rate < 30 OR heart_rate > 200)
|
||
OR (temperature < 35 OR temperature > 42)
|
||
OR (systolic_pressure < 60 OR systolic_pressure > 250);
|
||
|
||
IF integrity_issues > 0 THEN
|
||
RAISE WARNING '发现 % 条异常的生命体征数据', integrity_issues;
|
||
RETURN FALSE;
|
||
END IF;
|
||
|
||
RETURN TRUE;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建示例数据插入函数（用于测试）
|
||
CREATE OR REPLACE FUNCTION insert_sample_eldercare_data()
|
||
RETURNS VOID AS $$
|
||
BEGIN
|
||
-- 插入示例机构数据
|
||
INSERT INTO public.ec_facilities (name, type, contact_phone, address, capacity) VALUES
|
||
('阳光养老院', 'nursing_home', '010-12345678', '北京市朝阳区阳光街123号', 100),
|
||
('康乐护理中心', 'assisted_living', '021-87654321', '上海市浦东新区康乐路456号', 80)
|
||
ON CONFLICT DO NOTHING;
|
||
|
||
RAISE NOTICE '示例数据插入完成';
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建数据库状态检查函数
|
||
CREATE OR REPLACE FUNCTION eldercare_system_status()
|
||
RETURNS TABLE(
|
||
table_name TEXT,
|
||
record_count BIGINT,
|
||
last_updated TIMESTAMP WITH TIME ZONE
|
||
) AS $$
|
||
BEGIN
|
||
RETURN QUERY
|
||
SELECT
|
||
'ec_facilities'::TEXT,
|
||
COUNT(*),
|
||
MAX(created_at)
|
||
FROM public.ec_facilities
|
||
UNION ALL
|
||
SELECT
|
||
'ec_elders'::TEXT,
|
||
COUNT(*),
|
||
MAX(created_at)
|
||
FROM public.ec_elders
|
||
UNION ALL
|
||
SELECT
|
||
'ec_care_tasks'::TEXT,
|
||
COUNT(*),
|
||
MAX(created_at)
|
||
FROM public.ec_care_tasks
|
||
UNION ALL
|
||
SELECT
|
||
'ec_vital_signs'::TEXT,
|
||
COUNT(*),
|
||
MAX(created_at)
|
||
FROM public.ec_vital_signs
|
||
UNION ALL
|
||
SELECT
|
||
'ec_health_alerts'::TEXT,
|
||
COUNT(*),
|
||
MAX(created_at)
|
||
FROM public.ec_health_alerts;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建清理过期数据的函数
|
||
CREATE OR REPLACE FUNCTION cleanup_expired_data()
|
||
RETURNS INTEGER AS $$
|
||
DECLARE
|
||
deleted_count INTEGER := 0;
|
||
temp_count INTEGER := 0;
|
||
BEGIN
|
||
-- 删除已解决的超过30天的健康预警
|
||
DELETE FROM public.ec_health_alerts
|
||
WHERE status = 'resolved'
|
||
AND resolved_at < CURRENT_DATE - INTERVAL '30 days';
|
||
GET DIAGNOSTICS deleted_count = ROW_COUNT;
|
||
|
||
-- 删除超过1年的通知日志
|
||
DELETE FROM public.ec_notification_logs
|
||
WHERE created_at < CURRENT_DATE - INTERVAL '1 year';
|
||
GET DIAGNOSTICS temp_count = ROW_COUNT;
|
||
deleted_count := deleted_count + temp_count;
|
||
|
||
RETURN deleted_count;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- ================================================
|
||
-- 15. 急诊管理
|
||
-- ================================================
|
||
|
||
-- 急诊事件表
|
||
CREATE TABLE IF NOT EXISTS public.ec_emergencies (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
appointment_id uuid REFERENCES public.ec_appointments(id), -- 关联预约
|
||
elder_id uuid NOT NULL REFERENCES public.ec_elders(id) ON DELETE CASCADE, -- 关联老人
|
||
doctor_id uuid REFERENCES public.ak_users(id), -- 处理医生
|
||
emergency_type VARCHAR(32) CHECK (emergency_type IN ('fall', 'stroke', 'cardiac', 'other')), -- 急诊类型
|
||
severity VARCHAR(16) CHECK (severity IN ('low', 'medium', 'high', 'critical')), -- 紧急程度
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'processing', 'resolved', 'cancelled')), -- 状态
|
||
description TEXT, -- 急诊描述
|
||
occurred_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- 发生时间
|
||
handled_at TIMESTAMP WITH TIME ZONE, -- 处理时间
|
||
handler_notes TEXT, -- 处理说明
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 启用 RLS
|
||
ALTER TABLE public.ec_emergencies ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 插入策略：authenticated 只能登记自己/关联老人的急诊
|
||
DROP POLICY IF EXISTS emergencies_insert_authenticated ON public.ec_emergencies;
|
||
CREATE POLICY emergencies_insert_authenticated ON public.ec_emergencies
|
||
FOR INSERT TO authenticated
|
||
WITH CHECK (elder_id = get_current_user_id() OR doctor_id = get_current_user_id());
|
||
|
||
-- 查询策略：authenticated 只能查自己/关联老人/自己为医生的急诊
|
||
DROP POLICY IF EXISTS emergencies_select_authenticated ON public.ec_emergencies;
|
||
CREATE POLICY emergencies_select_authenticated ON public.ec_emergencies
|
||
FOR SELECT TO authenticated
|
||
USING (elder_id = get_current_user_id() OR doctor_id = get_current_user_id());
|
||
|
||
-- 更新策略：authenticated 只能改自己/关联老人/自己为医生的急诊
|
||
DROP POLICY IF EXISTS emergencies_update_authenticated ON public.ec_emergencies;
|
||
CREATE POLICY emergencies_update_authenticated ON public.ec_emergencies
|
||
FOR UPDATE TO authenticated
|
||
USING (elder_id = get_current_user_id() OR doctor_id = get_current_user_id());
|
||
|
||
-- 自动更新时间戳触发器
|
||
DROP TRIGGER IF EXISTS trigger_ec_emergencies_updated_at ON public.ec_emergencies;
|
||
CREATE TRIGGER trigger_ec_emergencies_updated_at
|
||
BEFORE UPDATE ON public.ec_emergencies
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 急诊相关索引
|
||
CREATE INDEX IF NOT EXISTS idx_ec_emergencies_elder_id ON public.ec_emergencies(elder_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_emergencies_doctor_id ON public.ec_emergencies(doctor_id);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_emergencies_status ON public.ec_emergencies(status);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_emergencies_severity ON public.ec_emergencies(severity);
|
||
CREATE INDEX IF NOT EXISTS idx_ec_emergencies_occurred_at ON public.ec_emergencies(occurred_at);
|
||
-- 急诊处理记录表
|
||
CREATE TABLE IF NOT EXISTS public.ec_emergency_handlings (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
emergency_id uuid NOT NULL REFERENCES public.ec_emergencies(id) ON DELETE CASCADE,
|
||
handler_id uuid NOT NULL REFERENCES public.ak_users(id),
|
||
handle_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
handling_notes TEXT,
|
||
status VARCHAR(16) CHECK (status IN ('pending', 'in_progress', 'resolved', 'closed')),
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 启用 RLS
|
||
ALTER TABLE public.ec_emergency_handlings ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 插入策略
|
||
DROP POLICY IF EXISTS emergency_handlings_insert_authenticated ON public.ec_emergency_handlings;
|
||
CREATE POLICY emergency_handlings_insert_authenticated ON public.ec_emergency_handlings
|
||
FOR INSERT TO authenticated
|
||
WITH CHECK (true);
|
||
|
||
-- 查询策略
|
||
DROP POLICY IF EXISTS emergency_handlings_select_authenticated ON public.ec_emergency_handlings;
|
||
CREATE POLICY emergency_handlings_select_authenticated ON public.ec_emergency_handlings
|
||
FOR SELECT TO authenticated
|
||
USING (handler_id = get_current_user_id());
|
||
|
||
-- 更新策略
|
||
DROP POLICY IF EXISTS emergency_handlings_update_authenticated ON public.ec_emergency_handlings;
|
||
CREATE POLICY emergency_handlings_update_authenticated ON public.ec_emergency_handlings
|
||
FOR UPDATE TO authenticated
|
||
USING (handler_id = get_current_user_id());
|