92 lines
4.2 KiB
Markdown
92 lines
4.2 KiB
Markdown
# Database Schema & Query Guide
|
|
|
|
## Overview
|
|
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.
|
|
|
|
## Tables
|
|
| Table | Purpose | Key Columns |
|
|
| --- | --- | --- |
|
|
| `regions` | Administrative areas (市级、禅城区等) | `id` (PK), `name` (unique) |
|
|
| `business_scopes` | Scoped经营范围条目 | `id` (PK), `description` |
|
|
| `region_scopes` | Region-to-scope mapping | `region_id` → `regions.id`, `scope_id` → `business_scopes.id` |
|
|
| `themes` | “一照通行”主题事项 | `id` (PK), `name` |
|
|
| `region_themes` | Region-to-theme mapping | `region_id`, `theme_id` |
|
|
| `permits` | 许可（备案）事项 | `id` (PK), `name` |
|
|
| `region_theme_permits` | Region + theme + permit linkage | `region_id`, `theme_id`, `permit_id` |
|
|
| `risks` | 风险提示主体信息 | `id` (PK), `risk_content`, `legal_basis`, `document_no`, `summary` |
|
|
| `region_permit_risks` | Region + permit + risk linkage | `region_id`, `permit_id`, `risk_id` |
|
|
| `permit_risk_snapshots` | Versioned checkpoints per region/permit/risk | `(region_id, permit_id, risk_id, version)` |
|
|
|
|
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.
|
|
|
|
## Views
|
|
### `region_permit_risk_vw`
|
|
Provides a flattened row per `(region_id, permit_id, risk_id)` that includes:
|
|
- Region/permit names
|
|
- Risk narrative fields (`risk_content`, `legal_basis`, `document_no`, `summary`)
|
|
- Related detail columns from `region_permit_details`
|
|
- Aggregated theme, business-scope, and subitem identifiers/names
|
|
- Synthetic key `permit_risk_key = region_id::text || '::' || permit_id::text || '::' || risk_id::text`
|
|
|
|
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.
|
|
|
|
### 快照与更新服务
|
|
- `create_permit_risk_snapshot(region_id, permit_id, risk_id, edited_by=None, change_summary=None)`
|
|
- 独立保存当前视图行到 `permit_risk_snapshots`，常用于人工备份。
|
|
- `update_permit_risk_record(...)`
|
|
- 支持在事务内同时：① 生成快照；② 更新 `risks` 表（风险提示内容/法律依据等）；③ UPSERT `region_permit_details`（许可状态、责任人等）。
|
|
- 未显式传参的列保持原样；传入 `None` 可将对应字段置空。
|