The `licensing_risks` PostgreSQL database stores municipal licensing risk prompts parsed from Excel workbooks. Each record links regions, themes, permits, and risk narratives so downstream systems can query compliance obligations quickly.
All primary keys are integer sequences; unique indexes and `ON CONFLICT DO NOTHING` logic make repeated imports idempotent. Foreign keys should be enforced in the target schema to prevent orphan rows.
## Query Cheatsheet
### 列出所有主题事项（总表）
```sql
SELECT t.id,
t.name AS theme_name,
r.name AS region_name
FROM themes t
JOIN region_themes rt ON rt.theme_id = t.id
JOIN regions r ON r.id = rt.region_id
ORDER BY r.name, t.name;
```
### 根据主题事项获取许可事项列表
Replace `%主题关键词%` with the desired theme name or keyword.
```sql
SELECT DISTINCT p.id,
p.name AS permit_name,
r.name AS region_name
FROM permits p
JOIN region_theme_permits rtp
ON rtp.permit_id = p.id
JOIN region_themes rt
ON rt.region_id = rtp.region_id
AND rt.theme_id = rtp.theme_id
JOIN themes t ON t.id = rt.theme_id
JOIN regions r ON r.id = rt.region_id
WHERE t.name ILIKE '%主题关键词%'
ORDER BY r.name, permit_name;
```
### 根据许可事项检索风险条目
Substitute `'具体许可名称'` with the permit you care about.
```sql
SELECT r.name AS region_name,
p.name AS permit_name,
rk.risk_content,
rk.legal_basis,
rk.document_no,
rk.summary
FROM region_permit_risks rpr
JOIN regions r ON r.id = rpr.region_id
JOIN permits p ON p.id = rpr.permit_id
JOIN risks rk ON rk.id = rpr.risk_id
WHERE p.name = '具体许可名称'
ORDER BY r.name, rk.risk_content;
```
For fuzzy lookups, switch to `WHERE p.name ILIKE '%关键词%'`.
## Execution Tips
- Connect via `psql -h 172.24.240.1 -U postgres -d licensing_risks`.
- Export query results with `\copy (SELECT …) TO '/tmp/export.csv' WITH CSV HEADER;`.
- Run queries after imports commit; the loaders already wrap operations in transactions.
Use this view for checkpointing and diff operations instead of re-joining the normalized tables. The underlying tables already have composite primary keys on `(region_id, permit_id, …)`, so no additional indexes are required for the view.
