sciagent/docs/user-profile-manager-state-machine-plan.md

User profile manager — integration analysis and implementation state machine

This document analyzes how fe0/src/auth/LoginRegisterCard.tsx connects to the rest of the stack, clarifies boundaries with PostgreSQL and MinIO, and specifies a state-machine–driven plan for institutional profile fields, admin verification, and applicant self-service edits.

Companion document (canonical DB engineering guidance): user-profile-manager-db-review.md — schema shape, DDL skeleton, indexes, concurrency, audit, and constraint-level enforcement. This plan inherits those decisions below so implementation does not fork.

Related: auth-registration-and-user-management.md (broader auth; some paths may lag the tree).

---

1. How LoginRegisterCard connects today

1.1 Frontend wiring

Layer	Role
fe0/src/pages/Login.tsx	Renders LoginRegisterCard only.
fe0/src/auth/LoginRegisterCard.tsx	Tabs (login/register); validates institutional email (fe0/src/auth/institutionalEmail); collects full name, email, password (+ confirm); calls useAuth() from fe0/src/contexts/AuthContext.tsx.
AuthContext	login → authService.login; register → authService.register; on success stores JWT via authService, builds session user via fe0/src/auth/sessionUser.ts (buildUserFromAuthPayload).
fe0/src/lib/auth-service.ts	HTTP to POST {API_URL}/api/v1/auth/register with JSON { fullName, email, password, passwordConfirm }; stores accessToken in localStorage on success.
Routing	Successful login/register navigates with resolvePostLoginPath (fe0/src/lib/dashboardNavigation.ts) using the resolved role.

Session user shape (AuthSessionUser): id, email, name, phone, roles (effective + available), computed permissions.

1.2 Backend wiring

Layer	Role
be0/src/auth_api.py	POST /auth/register (mounted under /api/v1): normalizes @ump.edu.vn / @umc.edu.vn, enforces password policy, creates users row + user_roles row (server-derived admin vs viewer; client role ignored). Issues JWT embedding roles. Writes audit_log for registration.
be0/src/initiative_db/models.py User	Persists email, password_hash, full_name, optional phone, optional unit_id, is_active, timestamps — no institutional extended profile tables yet.

1.3 Database today

• users + user_roles: credentials, display identity (full_name, phone), unit_id (catalog hook — see §2), is_active, RBAC-like roles (admin, editor, viewer, …).

1.4 MinIO — explicit boundary

Registration and profile HTTP APIs must not persist profile scalars in MinIO. Object storage (attachments, exports, quarantine) lives in be0/src/minio/storage.py — binary blobs only.

Concern	Store
Profile fields, verification status, timestamps	PostgreSQL — preferably user_staff_profiles + catalog tables (§5), not widening hot users rows for HR text (see DB review §2).
Optional future HR proof uploads	MinIO keys + metadata rows referencing user_staff_profiles; out of scope until product demands it

---

2. Gap analysis vs desired product

2.1 Registration (LoginRegisterCard)

Field	UI	Persistence / naming
Họ và tên	<input>	Stays users.full_name (already collected as fullName).
Mã số nhân sự	<input>	user_staff_profiles.employee_id — format + uniqueness at DB (partial unique index when non-NULL — see DB review §3.1 / §9); Pydantic + CHECK in migration. NULL/required policy decided before DDL (§9 open questions).
Học hàm, học vị	dropdown + “Khác” <input>	academic_title_code FK → academic_titles lookup table (not raw Postgres ENUM for title list — avoids DDL drift); academic_title_other mandatory iff code='other' — enforce with CHECK (DB review §3.2).
Đơn vị công tác	<input> or catalog	Must reconcile users.unit_id: either catalog only (unit_id FK, drop parallel free-text) or staged unit_id XOR unit_name_freetext with CHECK; never ship orphan department text alongside a real units catalog (DB review §3.3).
Chức vụ (job)	<input>	job_title in DB/API — never overload JWT roles. Nullable for non-person accounts; length capped (CHECK or varchar).
Email / SĐT	<input>	Email unchanged (authoritative normalization on server); phone aligns with auth_api PATCH rules.

Backend: INSERT users + INSERT user_staff_profiles + user_roles in one transaction — profile row created at registration (possibly all NULL + draft) or seeded in a follow-on migration backfill (DB review §8).

2.2 Applicant profile (ApplicantProfileView)

Today: GET .../auth/me, PATCH .../auth/profile — fullName, phone only.

Target: Joint reads join User ↔ UserStaffProfile for /me (or dedicated profile projection). PATCH applicant-owned staff fields bumps UserStaffProfiles.version (optimistic concurrency for admin verify — §4.4). Policy: freeze after verified vs material edits → pending — pick one (§9).

2.3 Admin “Users” (DashboardSidebar)

fe0/src/components/admin/DashboardSidebar.tsx links /dashboard/users via management menu items (~MenuItem + Link).

Routing gap: fe0/src/App.tsx has no users route → 404 until User Profile Manager ships.

---

3. Cross-cutting correctness (security, concurrency, audit)

1. Server validation: domain, enums, lengths, employee_id shape — mirror in CHECK where feasible (DB review §3).
2. RBAC vs job title: API names job_title; JWT / user_roles = admin | viewer | … only.
3. Authz: Applicants read/update own staff profile subset; admins list / verify / reject via /admin/...; same patterns as existing audit actors.
4. Two axes: users.is_active (login disabled) orthogonal to profile_verification_status (HR trust) — do not collapse.
5. TIMESTAMPTZ everywhere for _at columns (DB review §3.6).
6. Admin idempotency: Verify twice → choose 409 Conflict vs 204/200 idempotent no-op; document next to handler; conditional UPDATE makes lost races observable (§4.4).

Audit (DB review §6):

• JSONB before/after snapshots with a whitelist of fields — never blacklist (avoids leaking password_hash or future secrets).
• Same transaction as profile state transitions; rollback state if audit insert fails.
• Ordering: predictable sort key (BIGSERIAL/monotonic event_id + created_at); index (entity_type, entity_id, event_id) for history timelines.
• Growth: retention policy + partitioning (e.g. monthly RANGE on created_at) for append-only volumes.

---

4. State machines

Treat the diagram as the logical spec. The database implements invariants (status type + cross-column CHECKs); illegal rows must fail to commit (DB review §4).

4.1 Profile verification lifecycle

State	Meaning
draft	Account exists; profile incomplete or not submitted for verification.
pending	Submitted queue; awaits admin decision.
verified	Admin approved → verified_at + verified_by_user_id required (DB CHECK).
rejected	Admin declined → rejection_reason non-empty required (DB CHECK).

Cross-column constraints (PostgreSQL) — align code with DDL in DB review §4.2 / §9, e.g.:

• Verified ⇒ verified_at + verified_by_user_id non-NULL
• Rejected ⇒ trimmed rejection_reason
• Draft/pending ⇒ verification metadata cleared

Transitions hard to encode as CHECK (e.g. no direct verified → draft): use UPDATE … WHERE user_id=$1 AND profile_verification_status=$expected AND version=$etag — zero rows ⇒ illegal transition or stale read ⇒ 409 (DB review §4.3).

Mermaid — verification lifecycle

stateDiagram-v2
  [*] --> draft: Registration + empty staff profile / backfill

  draft --> pending: Submit for review meets completeness rules
  pending --> verified: Admin approves conditional UPDATE succeeds
  pending --> rejected: Admin rejects with reason

  rejected --> pending: Applicant resubmits clears reason per policy

  verified --> pending: Material edit triggers re-verification strict policy optional
  verified --> verified: Cosmetic-only edit under pragmatic policy optional

  note right of verified
    DB VERIFY CHECK verified_at verifier NOT NULL rejection cleared
    Applicant PATCH increments version optimistic lock
  end note

Product/policy encoded in tests (mirror diagram + illegal edges): see §8.

4.2 Request / ownership (actors)

stateDiagram-v2
  [*] --> Anonymous
  Anonymous --> AuthenticatedApplicant: Register or Login JWT

  state AuthenticatedApplicant {
    [*] --> SelfRead
    SelfRead --> SelfPatch: PATCH own profile increments version optional
    SelfPatch --> SelfRead
  }

  AuthenticatedApplicant --> pending: Submit transitions draft→pending

  state AdminAuthenticated {
    [*] --> Listing
    Listing --> Detail: Open profile sends version seen
    Detail --> Listing: VERIFY REJECT conditional on prior status + version
  }

4.3 Data flow (target shape)

flowchart LR
  subgraph fe0
    LR[LoginRegisterCard]
    AS[auth-service]
    LR --> AS
  end

  subgraph be0
    API[auth_api register/profile]
    U[(users + user_roles)]
    P[user_staff_profiles]
    AU[audit same txn]
    API --> U
    API --> P
    API --> AU
  end

  AS -->|HTTPS JSON| API

  subgraph minio_optional
    S3[(MinIO blobs)]
  end

  S3 -.->|not profile scalars| API

4.4 Concurrency: two admins, PATCH while reviewing

Scenario	Required behavior
Two admins verify same pending row	Conditional UPDATE — second gets 0 rows → 409 Conflict or explicit idempotent 200; never pretend success while row unchanged inconsistently (DB review §5).
Applicant PATCH while admin reads	If verify must bind to reviewed snapshot, admin submits expected_version (etag from detail API); mismatch → 409 reload. Bump version on every applicant mutating PATCH.

---

5. Canonical database shape (summary)

Decision: user_staff_profiles as 1:1 child — PRIMARY KEY (user_id) REFERENCES users(id) ON DELETE CASCADE** — **do not widen users` for HR fields (DB review §2).

Concern	Approach
Academic titles	Table academic_titles (code, label_vi, …); FK academic_title_code; other invariant CHECK.
employee_id	Partial UNIQUE WHERE employee_id IS NOT NULL; shape CHECK; NULL/required/product rule in §9.
Department	users.unit_id vs unit_name_freetext — single story; XOR CHECK during transition (DB review §3.3).
Status typing	Postgres ENUM or CHECK (... IN (...)) — no free-text status.
Optimistic lock	version INTEGER NOT NULL DEFAULT 1 on user_staff_profiles; bump every applicant-facing mutating UPDATE.

Indexing (day-one hot paths) — detail in DB review §7:

• Partial index: WHERE profile_verification_status = 'pending' + verification_submitted_at.
• Composite filters (unit/status/submitted_at) when query shape is confirmed.
• (verified_by_user_id, verified_at DESC) for verifier dashboards.

DDL skeleton: DB review §9 — use as migration author starter; reconcile column names (academic_title_code, unit_name_freetext) with API DTO naming in code.

---

6. Migration mechanics (DB review §8)

1. user_staff_profiles migration is additive — avoids long ACCESS EXCLUSIVE on users for nullable-widen patterns.
2. Backfill: INSERT … SELECT FROM users with defaults draft, institutional columns NULL — same migration if row count modest; batched otherwise.
3. NOT NULL rollout: nullable column → backfill → SET NOT NULL in steps.
4. downgrade(): explicit (e.g. DROP TABLE user_staff_profiles), accepting HR data loss on rollback.

---

7. Implementation backlog (ordered)

1. DDL + seed academic_titles (+ migration conventions + TIMESTAMPTZ audit columns if partitioning introduced).
2. SQLAlchemy models — UserStaffProfile, relationship from User; avoid SELECT * antipatterns on wide auth paths.
3. Transactional registration — User + UserStaffProfile + UserRoleRow + registration audit branch.
4. Applicant APIs — extend RegisterBody, /me, PATCH /profile; join profile; version/etag semantics for PATCH.
5. Admin APIs — queue list indexes; verify/reject with conditional UPDATE + 409/200 policy; JSONB whitelist audit in same txn.
6. Frontend — LoginRegisterCard, ApplicantProfileView, /dashboard/users, sidebar permission alignment.
7. Observability — document idempotency and conflict responses in OpenAPI or internal README.

MinIO: unchanged unless attaching proof blobs later.

---

8. Testing matrix

Beyond API/integration tests originally listed:

Class	Goal
Constraint violations	INSERT/UPDATE rows breaking CHECK / FK / partial unique → expect DB error surfaced as 4xx/mapped.
Transition matrix	Every legal Mermaid transition succeeds; illegal transitions (0-row UPDATE) fail as specified.
Concurrency	Two DB connections / threads race verify → one wins; second behavior matches documented idempotency.
Migration round-trip	upgrade → downgrade → upgrade on seeded DB (DB review §10).

---

9. Open questions (before merge)

Blocking “migration done”; align product then freeze DDL (DB review §11):

1. employee_id: required at register, optional, or required-before-pending?
2. Re-verification: strict (any PATCH from verified → pending) vs pragmatic (field whitelist).
3. Duplicate verify: idempotent 200 vs 409 — document in handler + client.
4. units catalog: authoritative? If yes, drop free-text arm; else XOR + backfill strategy.
5. Audit: retention horizon + partition cadence.
6. verified_until / expiry: periodic re-verification for HR compliance — schema + job vs defer.

---

10. File reference map

Area	Path
Register UI	fe0/src/auth/LoginRegisterCard.tsx
Auth state	fe0/src/contexts/AuthContext.tsx
HTTP client	fe0/src/lib/auth-service.ts
Applicant profile UI	fe0/src/components/applicant/profile/ApplicantProfileView.tsx
Admin sidebar (Users link)	fe0/src/components/admin/DashboardSidebar.tsx
Routes	fe0/src/App.tsx
Register / login / profile API	be0/src/auth_api.py
User ORM	be0/src/initiative_db/models.py
MinIO abstraction	be0/src/minio/storage.py
DB engineering review	docs/user-profile-manager-db-review.md

---

Refined against user-profile-manager-db-review.md: committed schema (user_staff_profiles), DB-level state invariants, concurrency (version/conditional UPDATE), audit JSONB whitelist + same-transaction writes, indexing and migration posture, expanded testing and open questions.