Skip to content

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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → accounts
appapp_codeNOT NULL
statustextNOT NULL
status_reasontext
approved_byuuidFK → accounts
approved_attimestamptz
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL

Khoá chính: id

Trỏ ra (2):

  • account_id → accounts · ON DELETE RESTRICT
  • approved_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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
categorytextNOT NULL
versionintegerNOT NULL
actiontextNOT NULL
retention_periodinterval
append_onlybooleanNOT NULL
statustextNOT NULL
sourcetextNOT NULL
approved_reftext
created_attimestamptzNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
account_iduuidNOT NULLFK → accounts
recipient_nametextNOT NULL
recipient_phonephone_e164NOT NULL
provincetextNOT NULL
districttextNOT NULLG2I-P3-04. Empty string = no district (2-level administrative units); never NULL, never blank spaces.
wardtextNOT NULL
address_linetextNOT NULL
address_typetextNOT NULL
is_defaultbooleanNOT NULL
deleted_attimestamptz
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
phone_e164phone_e164NOT NULL
appapp_codeNOT NULL
purposetextNOT NULL
code_hashbyteaNOT NULL
draft_ciphertextbytea
draft_key_idtext
attempt_countintegerNOT NULL
max_attemptsintegerNOT NULL
send_countintegerNOT NULL
last_sent_attimestamptzNOT NULL
expires_attimestamptzNOT NULL
consumed_attimestamptz
invalidated_attimestamptz
correlation_idcorrelation_refNOT NULL
created_attimestamptzNOT 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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
codetextNOT NULL
descriptiontext
risk_leveltextNOT NULL
created_attimestamptzNOT NULL
deprecated_attimestamptz
allowed_scopestext[]NOT NULLP1-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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
outcometextNOT NULL
replay_ofuuidFK → rbac_bootstrap_runs
super_admin_account_iduuidNOT NULLFK → accounts
admin_account_iduuidFK → accounts
super_admin_assignment_iduuidNOT NULLFK → role_assignments
admin_assignment_iduuidFK → role_assignments
operator_nametextNOT NULL
operator_logintextNOT NULL
reasontextNOT NULL
created_attimestamptzNOT NULL
kindtextNOT NULL
actionstext[]NOT NULL

Khoá chính: id

Trỏ ra (5):

  • admin_account_id → accounts · ON DELETE RESTRICT
  • admin_assignment_id → role_assignments · ON DELETE RESTRICT
  • replay_of → rbac_bootstrap_runs · ON DELETE RESTRICT
  • super_admin_account_id → accounts · ON DELETE RESTRICT
  • super_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ộtKiểuKhoáMô tả
key_hashbyteaNOT NULLPK
total_countintegerNOT NULL
quote_countintegerNOT NULL
window_started_attimestamptzNOT NULL
last_call_attimestamptzNOT NULL

Khoá chính: key_hash

Trỏ ra: không có

Được trỏ tới: không có

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ộtKiểuKhoáMô tả
providertextNOT NULLPK
issuertextNOT NULLPK
subjecttextNOT NULLPK
account_iduuidNOT NULLFK → accounts
created_attimestamptzNOT NULL
revoked_attimestamptzPPI-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_reasontext
link_iduuidNOT NULLPPI-P3-08. Row id (unique, immutable), stable when the subject is released (subject = 'released:<link_id>').
released_subjecttextPPI-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ộtKiểuKhoáMô tả
subjectuuidNOT NULLPK
phone_e164phone_e164NOT NULL
password_hashtextNOT NULL
sign_up_challenge_iduuid
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
credential_changed_attimestamptzNOT 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ộtKiểuKhoáMô tả
account_iduuidNOT NULLPK FK → accounts
full_nametextNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL
versionintegerNOT NULL
emailtextU6-P2-23. Email declared on the User App profile (KH Spec); Admin read-only through customer.directory().
date_of_birthdateU6-P2-23. Calendar date of birth (no time zone, C-09); Admin read-only through customer.directory().
gendertextU6-P2-23. MALE | FEMALE | OTHER (Nam / Nu / Khac); null = not declared. Admin read-only.
referral_codetextU6-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ộtKiểuKhoáMô tả
iduuidNOT NULLPK
challenge_iduuidNOT NULLFK → credential_challenges
purposetextNOT NULLFK → credential_challenges
grant_hashbyteaNOT NULL
auth_user_iduuidAF-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_attimestamptzNOT NULL
consumed_attimestamptz
revoked_attimestamptz
created_attimestamptzNOT 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ộtKiểuKhoáMô tả
account_iduuidNOT NULLPK FK → accounts
keywordtextNOT NULL
keyword_normtextNOT NULLPK
searched_attimestamptzNOT 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ộtKiểuKhoáMô tả
account_iduuidNOT NULLPK FK → accounts
phone_e164phone_e164NOT NULL
password_hashtextNOT NULL
must_changebooleanNOT NULL
temporary_expires_attimestamptz
issued_byuuidFK → accounts
credential_changed_attimestamptzNOT NULL
created_attimestamptzNOT NULL
updated_attimestamptzNOT NULL

Khoá chính: account_id

Trỏ ra (2):

  • account_id → accounts · ON DELETE RESTRICT
  • issued_by → accounts · ON DELETE RESTRICT

Được trỏ tới: không có

Sinh từ ad83b0aea · 820 migration · PostgreSQL 17