105 lines
3.7 KiB
Markdown
105 lines
3.7 KiB
Markdown
# DB Schema（新架構目標版）
|
||
|
||
> 本文件是新架構的目標資料模型，供後端 schema 重建與 migration 依據。
|
||
> DB 真實來源仍以 [backend/scripts/init_schema.sql](../backend/scripts/init_schema.sql) 為準。
|
||
|
||
## 1) companies
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `company_key` TEXT NOT NULL UNIQUE
|
||
- `name` TEXT NOT NULL
|
||
- `provider_group_id` TEXT
|
||
- `status` VARCHAR(16) NOT NULL default `'active'`
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
|
||
## 2) sites
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `site_key` TEXT NOT NULL UNIQUE
|
||
- `company_id` UUID NOT NULL FK -> `companies(id)` ON DELETE CASCADE
|
||
- `display_name` TEXT NOT NULL
|
||
- `domain` TEXT
|
||
- `provider_group_id` TEXT
|
||
- `status` VARCHAR(16) NOT NULL default `'active'`
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
|
||
## 3) systems
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `system_key` TEXT NOT NULL UNIQUE
|
||
- `name` TEXT NOT NULL
|
||
- `status` VARCHAR(16) NOT NULL default `'active'`
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
|
||
## 4) roles
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `role_key` TEXT NOT NULL UNIQUE
|
||
- `role_code` TEXT NOT NULL（語意代碼，建議格式：`<system>:<module>:<action>`，例如 `mkt:marketing_card:edit`）
|
||
- `system_id` UUID NOT NULL FK -> `systems(id)` ON DELETE CASCADE
|
||
- `name` TEXT NOT NULL
|
||
- `description` TEXT
|
||
- `status` VARCHAR(16) NOT NULL default `'active'`
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- UNIQUE(`system_id`, `name`)
|
||
- UNIQUE(`system_id`, `role_code`)
|
||
|
||
## 5) site_roles
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `site_id` UUID NOT NULL FK -> `sites(id)` ON DELETE CASCADE
|
||
- `role_id` UUID NOT NULL FK -> `roles(id)` ON DELETE CASCADE
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- UNIQUE(`site_id`, `role_id`)
|
||
|
||
## 6) users
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `user_sub` TEXT NOT NULL UNIQUE
|
||
- `provider_user_id` TEXT UNIQUE
|
||
- `username` TEXT UNIQUE
|
||
- `email` TEXT UNIQUE
|
||
- `display_name` TEXT
|
||
- `status` VARCHAR(16) NOT NULL default `'active'`
|
||
- `is_active` BOOLEAN NOT NULL default `true`
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
|
||
## 7) user_sites
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `user_id` UUID NOT NULL FK -> `users(id)` ON DELETE CASCADE
|
||
- `site_id` UUID NOT NULL FK -> `sites(id)` ON DELETE CASCADE
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- UNIQUE(`user_id`, `site_id`)
|
||
|
||
## 8) auth_sync_state
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `entity_type` VARCHAR(32) NOT NULL
|
||
- `entity_id` UUID NOT NULL
|
||
- `last_synced_at` TIMESTAMPTZ
|
||
- `source_version` TEXT
|
||
- `last_error` TEXT
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- UNIQUE(`entity_type`, `entity_id`)
|
||
|
||
## 9) api_clients
|
||
- `id` UUID PK default `gen_random_uuid()`
|
||
- `client_key` TEXT NOT NULL UNIQUE
|
||
- `name` TEXT NOT NULL
|
||
- `status` VARCHAR(16) NOT NULL default `'active'`
|
||
- `api_key_hash` TEXT NOT NULL
|
||
- `allowed_origins` JSONB NOT NULL default `'[]'::jsonb`
|
||
- `allowed_ips` JSONB NOT NULL default `'[]'::jsonb`
|
||
- `allowed_paths` JSONB NOT NULL default `'[]'::jsonb`
|
||
- `rate_limit_per_min` INTEGER
|
||
- `expires_at` TIMESTAMPTZ
|
||
- `last_used_at` TIMESTAMPTZ
|
||
- `created_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
- `updated_at` TIMESTAMPTZ NOT NULL default `now()`
|
||
|
||
## 關聯總結
|
||
- Company 1:N Site
|
||
- System 1:N Role
|
||
- Site M:N Role（`site_roles`）
|
||
- User M:N Site（`user_sites`）
|
||
- User 最終角色由 Site 推導，不做 user direct role 指派。
|