Neo-ZQYY/docs/database/BD_Manual_app_schema_rls_views.md

BD_Manual：app Schema 与 RLS 视图层

目标库：test_etl_feiqiu（通过 PG_DSN 连接） 迁移脚本：db/etl_feiqiu/migrations/2026-02-24__p1_create_app_schema_rls_views.sql DDL 位置：docs/database/ddl/etl_feiqiu__app.sql（执行后需重新生成） 关联 SPEC：miniapp-db-foundation（P1 基础设施层）

---

1. 变更说明

新增 Schema

• app：RLS 视图层，供业务库通过 postgres_fdw 只读访问 ETL 数据

新增角色

• app_reader：只读角色（LOGIN），拥有 app Schema 的 USAGE + SELECT 权限

新增视图（35 张）

DWD 层（11 张，全部含 site_id 过滤）：

视图	源表	过滤条件
app.v_dim_member	dwd.dim_member	site_id = current_setting('app.current_site_id')::bigint
app.v_dim_assistant	dwd.dim_assistant	同上
app.v_dim_member_card_account	dwd.dim_member_card_account	同上
app.v_dim_table	dwd.dim_table	同上
app.v_dwd_settlement_head	dwd.dwd_settlement_head	同上
app.v_dwd_table_fee_log	dwd.dwd_table_fee_log	同上
app.v_dwd_assistant_service_log	dwd.dwd_assistant_service_log	同上
app.v_dwd_recharge_order	dwd.dwd_recharge_order	同上
app.v_dwd_store_goods_sale	dwd.dwd_store_goods_sale	同上
app.v_dim_staff	dwd.dim_staff	同上
app.v_dim_staff_ex	dwd.dim_staff_ex	同上

DWS 层 — 含 site_id 过滤（20 张）：

视图	源表
app.v_dws_member_consumption_summary	dws.dws_member_consumption_summary
app.v_dws_member_visit_detail	dws.dws_member_visit_detail
app.v_dws_member_winback_index	dws.dws_member_winback_index
app.v_dws_member_newconv_index	dws.dws_member_newconv_index
app.v_dws_member_recall_index	dws.dws_member_recall_index
app.v_dws_member_assistant_relation_index	dws.dws_member_assistant_relation_index
app.v_dws_member_assistant_intimacy	dws.dws_member_assistant_intimacy
app.v_dws_assistant_daily_detail	dws.dws_assistant_daily_detail
app.v_dws_assistant_monthly_summary	dws.dws_assistant_monthly_summary
app.v_dws_assistant_salary_calc	dws.dws_assistant_salary_calc
app.v_dws_assistant_customer_stats	dws.dws_assistant_customer_stats
app.v_dws_assistant_finance_analysis	dws.dws_assistant_finance_analysis
app.v_dws_finance_daily_summary	dws.dws_finance_daily_summary
app.v_dws_finance_income_structure	dws.dws_finance_income_structure
app.v_dws_finance_recharge_summary	dws.dws_finance_recharge_summary
app.v_dws_finance_discount_detail	dws.dws_finance_discount_detail
app.v_dws_finance_expense_summary	dws.dws_finance_expense_summary
app.v_dws_platform_settlement	dws.dws_platform_settlement
app.v_dws_assistant_recharge_commission	dws.dws_assistant_recharge_commission
app.v_dws_order_summary	dws.dws_order_summary

DWS 层 — cfg_ 配置表（4 张，无 site_id，直接 SELECT *）：*

视图	源表	说明
app.v_cfg_performance_tier	dws.cfg_performance_tier	无 site_id 列，不加过滤
app.v_cfg_assistant_level_price	dws.cfg_assistant_level_price	同上
app.v_cfg_bonus_rules	dws.cfg_bonus_rules	同上
app.v_cfg_index_parameters	dws.cfg_index_parameters	同上

权限配置

角色	Schema	权限
app_reader	app	USAGE + SELECT ON ALL TABLES + ALTER DEFAULT PRIVILEGES

P2 预留（注释形式，暂不创建）

• dws.dws_member_spending_power_index → app.v_dws_member_spending_power_index
• dws.dws_assistant_order_contribution → app.v_dws_assistant_order_contribution

---

2. 兼容性影响

组件	影响
ETL 任务	无影响。视图仅读取 DWD/DWS 表，不影响 ETL 写入流程
后端 API	前置依赖。后端通过 FDW 读取 app Schema 视图，本脚本是 FDW 配置的前提
小程序	无直接影响。小程序通过后端 API 间接访问
管理后台	无直接影响
现有 app Schema	已有 7 个视图将被 CREATE OR REPLACE 覆盖更新，新增 28 个视图

---

3. 回滚策略

迁移脚本末尾已包含注释形式的回滚语句，按逆序执行：

ALTER DEFAULT PRIVILEGES IN SCHEMA app REVOKE SELECT ON TABLES FROM app_reader;
REVOKE SELECT ON ALL TABLES IN SCHEMA app FROM app_reader;
REVOKE USAGE ON SCHEMA app FROM app_reader;
DROP SCHEMA IF EXISTS app CASCADE;  -- 会删除所有视图
DROP ROLE IF EXISTS app_reader;

注意：DROP SCHEMA app CASCADE 会级联删除所有视图和依赖的 FDW 外部表，需先回滚 FDW 配置。

---

4. 验证 SQL

-- 1. 验证 app Schema 存在
SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'app';

-- 2. 验证视图数量（应为 35 张）
SELECT count(*) FROM information_schema.views WHERE table_schema = 'app';

-- 3. 验证 app_reader 角色存在且有 app Schema 权限
SELECT has_schema_privilege('app_reader', 'app', 'USAGE') AS has_usage;

-- 4. 验证含 site_id 的视图定义包含 current_setting 过滤
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'app'
  AND view_definition LIKE '%current_setting%'
ORDER BY table_name;

-- 5. 验证 cfg_* 视图不含 current_setting 过滤
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'app'
  AND table_name LIKE 'v_cfg_%'
  AND view_definition NOT LIKE '%current_setting%';