Giao diện
identity / bảng đơn lẻ (13)
Trang sinh tự động
Sinh từ ad83b0aea — chạy 820 migration lên PostgreSQL 17 rồi đọc pg_catalog. Mô tả bảng và cột lấy từ COMMENT ON. Không sửa tay: lần chạy docgen sau sẽ ghi đè.
13 bảng. Quay lại identity.
app_memberships
P1-01. Proof of access to one app. New APP_SALE/WEB_ADMIN rows start PENDING with no approver and no role. ACTIVE is the only usable status (matrix GAP-10: READY is not stored).
public.app_memberships · 10 cột · 5 check · 1 unique · 3 index · 4 policy · 7 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → accounts | |
app | app_code | NOT NULL | ||
status | text | NOT NULL | ||
status_reason | text | |||
approved_by | uuid | FK → accounts | ||
approved_at | timestamptz | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (2):
account_id→accounts·ON DELETE RESTRICTapproved_by→accounts·ON DELETE RESTRICT
Được trỏ tới (12): affiliate_links, customer_codes, customer_column_preferences, customer_group_memberships, customer_profiles, mission_assignments, product_column_preferences, referral_links, role_assignments, role_change_requests, sale_team_history, sale_teams
retention_policies
P1-14. PD-08 s31 retention schedule as config rows (PROPOSAL until Legal/Finance approve). SYSTEM_ONLY.
public.retention_policies · 10 cột · 8 check · 1 unique · 3 index · 0 policy · 5 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
category | text | NOT NULL | ||
version | integer | NOT NULL | ||
action | text | NOT NULL | ||
retention_period | interval | |||
append_only | boolean | NOT NULL | ||
status | text | NOT NULL | ||
source | text | NOT NULL | ||
approved_ref | text | |||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra: không có
Được trỏ tới (2): privacy_exports, privacy_purge_records
addresses
P1-10. Owner address book. Soft delete only; orders keep their own snapshot. One live default per account.
public.addresses · 14 cột · 5 check · 0 unique · 3 index · 3 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → accounts | |
recipient_name | text | NOT NULL | ||
recipient_phone | phone_e164 | NOT NULL | ||
province | text | NOT NULL | ||
district | text | NOT NULL | G2I-P3-04. Empty string = no district (2-level administrative units); never NULL, never blank spaces. | |
ward | text | NOT NULL | ||
address_line | text | NOT NULL | ||
address_type | text | NOT NULL | ||
is_default | boolean | NOT NULL | ||
deleted_at | timestamptz | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL |
Khoá chính: id
Trỏ ra (1):
account_id→accounts·ON DELETE RESTRICT
Được trỏ tới (1): orders
credential_challenges
P1-01. SECRET_BEARING. Hashed one-time code bound to phone+app+purpose; one open challenge per key.
public.credential_challenges · 16 cột · 8 check · 1 unique · 4 index · 0 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
phone_e164 | phone_e164 | NOT NULL | ||
app | app_code | NOT NULL | ||
purpose | text | NOT NULL | ||
code_hash | bytea | NOT NULL | ||
draft_ciphertext | bytea | |||
draft_key_id | text | |||
attempt_count | integer | NOT NULL | ||
max_attempts | integer | NOT NULL | ||
send_count | integer | NOT NULL | ||
last_sent_at | timestamptz | NOT NULL | ||
expires_at | timestamptz | NOT NULL | ||
consumed_at | timestamptz | |||
invalidated_at | timestamptz | |||
correlation_id | correlation_ref | NOT NULL | ||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra: không có
Được trỏ tới (1): recovery_grants
permissions
Chưa có mô tả — bảng này chưa có COMMENT ON TABLE.
public.permissions · 7 cột · 3 check · 1 unique · 2 index · 0 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
code | text | NOT NULL | ||
description | text | |||
risk_level | text | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
deprecated_at | timestamptz | |||
allowed_scopes | text[] | NOT NULL | P1-06. APP:SCOPE pairs a grant may use (PD-08 s15 allowed scopes), from the non-GAP matrix rules. |
Khoá chính: id
Trỏ ra: không có
Được trỏ tới (1): role_permission_grants
rbac_bootstrap_runs
P1-06, PPI-P2-06. APPEND_ONLY log of operator runs: BOOTSTRAP (first super_admin + admin pair) and BREAK_GLASS (E-06: MFA reset / temporary credential of one super_admin). Who ran it, which login, why, which assignments.
public.rbac_bootstrap_runs · 13 cột · 7 check · 0 unique · 1 index · 0 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
outcome | text | NOT NULL | ||
replay_of | uuid | FK → rbac_bootstrap_runs | ||
super_admin_account_id | uuid | NOT NULL | FK → accounts | |
admin_account_id | uuid | FK → accounts | ||
super_admin_assignment_id | uuid | NOT NULL | FK → role_assignments | |
admin_assignment_id | uuid | FK → role_assignments | ||
operator_name | text | NOT NULL | ||
operator_login | text | NOT NULL | ||
reason | text | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
kind | text | NOT NULL | ||
actions | text[] | NOT NULL |
Khoá chính: id
Trỏ ra (5):
admin_account_id→accounts·ON DELETE RESTRICTadmin_assignment_id→role_assignments·ON DELETE RESTRICTreplay_of→rbac_bootstrap_runs·ON DELETE RESTRICTsuper_admin_account_id→accounts·ON DELETE RESTRICTsuper_admin_assignment_id→role_assignments·ON DELETE RESTRICT
Được trỏ tới (1): rbac_bootstrap_runs
api_guest_throttles
#469 (DEC-469-03). SYSTEM_ONLY call counter of token-less GraphQL requests per client (sha256 of the client key: cf-connecting-ip or the shared bucket of requests without one; never an address). Written only by public.api_guest_throttle_begin (pi_api, definer); rows 10 minutes past their window are purged by it. No payload.
public.api_guest_throttles · 5 cột · 2 check · 0 unique · 2 index · 0 policy · 0 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
key_hash | bytea | NOT NULL | PK | |
total_count | integer | NOT NULL | ||
quote_count | integer | NOT NULL | ||
window_started_at | timestamptz | NOT NULL | ||
last_call_at | timestamptz | NOT NULL |
Khoá chính: key_hash
Trỏ ra: không có
Được trỏ tới: không có
identity_links
AF-P2-01. SYSTEM_ONLY map (provider, issuer, subject) -> accounts.account_id. Local provider: ('local', 'pideal', account_id::text). Adding a provider adds rows, never a business FK.
public.identity_links · 9 cột · 6 check · 1 unique · 4 index · 0 policy · 4 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
provider | text | NOT NULL | PK | |
issuer | text | NOT NULL | PK | |
subject | text | NOT NULL | PK | |
account_id | uuid | NOT NULL | FK → accounts | |
created_at | timestamptz | NOT NULL | ||
revoked_at | timestamptz | PPI-P3-03 (E-10). When the link died (account.deleted, support unlink, User App deletion); set once. A dead link never maps a subject to the account again and is not an identity conflict. | ||
revoke_reason | text | |||
link_id | uuid | NOT NULL | PPI-P3-08. Row id (unique, immutable), stable when the subject is released (subject = 'released:<link_id>'). | |
released_subject | text | PPI-P3-08 / PPI-P7-04. The original subject of a dead link whose subject was released (operator support unlink identity.pipoint_support_unlink, User App deletion): subject is then 'released:<link_id>', so the same uid can be linked again; set once. |
Khoá chính: provider, issuer, subject
Trỏ ra (1):
account_id→accounts·ON DELETE RESTRICT
Được trỏ tới: không có
password_credentials
AF-P2-01. SECRET_BEARING password credential of the local provider, one per phone. subject = account_id once the account exists (no FK: the sign-up saga creates it first). sign_up_challenge_id is the saga idempotency key. Hash only, self-describing format (credential-policy).
public.password_credentials · 7 cột · 1 check · 1 unique · 3 index · 0 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
subject | uuid | NOT NULL | PK | |
phone_e164 | phone_e164 | NOT NULL | ||
password_hash | text | NOT NULL | ||
sign_up_challenge_id | uuid | |||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
credential_changed_at | timestamptz | NOT NULL |
Khoá chính: subject
Trỏ ra: không có
Được trỏ tới: không có
profiles
P1-10. Self-managed profile fields of one account (all apps). Phone lives on accounts and is not changeable here; Sale field classification (SPEC-06) is enforced by the API and stays undecided.
public.profiles · 9 cột · 5 check · 0 unique · 3 index · 3 policy · 2 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
account_id | uuid | NOT NULL | PK FK → accounts | |
full_name | text | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL | ||
version | integer | NOT NULL | ||
email | text | U6-P2-23. Email declared on the User App profile (KH Spec); Admin read-only through customer.directory(). | ||
date_of_birth | date | U6-P2-23. Calendar date of birth (no time zone, C-09); Admin read-only through customer.directory(). | ||
gender | text | U6-P2-23. MALE | FEMALE | OTHER (Nam / Nu / Khac); null = not declared. Admin read-only. | ||
referral_code | text | U6-P2-23. Referral code the customer typed into the profile (9-12 characters, KH Spec); shown and filtered (exact) only, never attribution (GAP-06, C-11). |
Khoá chính: account_id
Trỏ ra (1):
account_id→accounts·ON DELETE RESTRICT
Được trỏ tới: không có
recovery_grants
P1-01. SECRET_BEARING. One-use grant from a consumed challenge; never a session.
public.recovery_grants · 9 cột · 6 check · 2 unique · 3 index · 0 policy · 3 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
id | uuid | NOT NULL | PK | |
challenge_id | uuid | NOT NULL | FK → credential_challenges | |
purpose | text | NOT NULL | FK → credential_challenges | |
grant_hash | bytea | NOT NULL | ||
auth_user_id | uuid | AF-P2-02. Subject the grant is bound to: accounts.account_id or password_credentials.subject (same uuid). Historical column name; no foreign key (bound before the sign-up saga creates the account). | ||
expires_at | timestamptz | NOT NULL | ||
consumed_at | timestamptz | |||
revoked_at | timestamptz | |||
created_at | timestamptz | NOT NULL |
Khoá chính: id
Trỏ ra (1):
challenge_id, purpose→credential_challenges·ON DELETE RESTRICT
Được trỏ tới: không có
search_history
G1S-P1-16. Search keywords of a signed-in APP_USER buyer (BR-KP-02: 5 newest kept by the API); one row per keyword ignoring case and spaces. Erased with the account (privacy.erase_subject SEARCH_HISTORY).
public.search_history · 4 cột · 1 check · 0 unique · 1 index · 1 policy · 0 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
account_id | uuid | NOT NULL | PK FK → accounts | |
keyword | text | NOT NULL | ||
keyword_norm | text | NOT NULL | PK | |
searched_at | timestamptz | NOT NULL |
Khoá chính: account_id, keyword_norm
Trỏ ra (1):
account_id→accounts·ON DELETE CASCADE
Được trỏ tới: không có
staff_credentials
PPI-P2-01 (E-05). SECRET_BEARING password credential of a STAFF account (Web Admin), one per account and phone. Hash only (credential-policy format). must_change + temporary_expires_at = a temporary password issued by another admin (D-15, 72 h); issued_by = that admin (null for the operator script). Reached only through identity_flow.
public.staff_credentials · 9 cột · 3 check · 1 unique · 3 index · 0 policy · 3 trigger · RLS bật
| Cột | Kiểu | Khoá | Mô tả | |
|---|---|---|---|---|
account_id | uuid | NOT NULL | PK FK → accounts | |
phone_e164 | phone_e164 | NOT NULL | ||
password_hash | text | NOT NULL | ||
must_change | boolean | NOT NULL | ||
temporary_expires_at | timestamptz | |||
issued_by | uuid | FK → accounts | ||
credential_changed_at | timestamptz | NOT NULL | ||
created_at | timestamptz | NOT NULL | ||
updated_at | timestamptz | NOT NULL |
Khoá chính: account_id
Trỏ ra (2):
account_id→accounts·ON DELETE RESTRICTissued_by→accounts·ON DELETE RESTRICT
Được trỏ tới: không có