user_sessions + ip_login_locks — sessiya va IP qulfi

team-lead-ep-09 yangiladi

Yangilandi: pr_url

team-lead-ep-09 bog'liqlik qo'shdi

→ EP-09-T01-S03

team-lead-ep-09 yangiladi

Yangilandi: estimate_hours

qa-ep-09-t01-s05 izoh qoldirdi

QA tasdiqladi. Spec §2.3c/§2.3g bilan maydon-maydon solishtirildi: user_sessions barcha 12 ustun (id uuid PK gen_random_uuid(), user_id FK, refresh_token_hash UNIQUE, audience CHECK 5 qiymat, session_family_id, device_info/device_id/user_agent/ip/last_used_at nullable, expires_at NOT NULL, revoked_at/revoked_reason CHECK 9 qiymat aynan roʻyxat bilan mos, created_at) va ip_login_locks barcha 4 ustun (ip inet PK, failed_login_count default 0, locked_until, updated_at default now()) toʻgʻri. Ikkalasida ham tenant_id/organization_id yoʻq (AR-1/R18). Nomlash konvensiyasi (fk_/chk_/uq_/idx_) postgresql skill bilan mos. (c) qarori tasdiqlandi: postgresql skill §5 aynan shu jadval partial indeksini ("09_authdagi kabi") kanonik namuna sifatida keltiradi — qisman indeks baribir user_id FK ustunini qamraydi, umumiy qoidaga ziddiyat yoʻq, faqat spec belgilagan ixtisoslashuv. 4 indeks soni va tarkibi spec jadvali bilan aynan mos (UNIQUE(refresh_token_hash) + partial(user_id) WHERE revoked_at IS NULL + expires_at + session_family_id). mvn -Dtest=UserSessionsAndIpLoginLocksMigrationTest test -> 13/13 yashil (Testcontainers, real Postgres 16). Mutatsiya tekshiruvi: revoked_reason CHECKdan rotated qiymatini olib tashladim -> revokedReasonCheckAcceptsAllNineValues qizil (chk_user_sessions_revoked_reason buzildi) -> tikladim -> qayta 13/13 yashil. Chekka holatlar qoplangan: CHECK rad etish (audience/revoked_reason 10-qiymat), UNIQUE dublikat, FK orphan, PK ikkinchi qator.

qa-ep-09-t01-s05 statusni o'zgartirdi Review → Bajarildi

qa-ep-09-t01-s05 qabul mezonini belgiladi

✓ `ip_login_locks` PK = `ip`; bitta IP uchun ikkinchi qator yozib bo'lmaydi

qa-ep-09-t01-s05 qabul mezonini belgiladi

✓ 4 indeksning hammasi mavjud (`pg_indexes` tekshiruvi), `(user_id) WHERE revoked_at IS NULL` qisman indeks

qa-ep-09-t01-s05 qabul mezonini belgiladi

✓ `audience` CHECK aynan 5 qiymatni qabul qiladi

qa-ep-09-t01-s05 qabul mezonini belgiladi

✓ `revoked_reason` CHECK aynan 9 qiymatni qabul qiladi; 10-qiymat rad etiladi

qa-ep-09-t01-s05 qabul mezonini belgiladi

✓ `user_sessions.id` uuid va default `gen_random_uuid()` bilan to'ladi

coder-ep-09-t01-s05 izoh qoldirdi

V6 migratsiya: user_sessions (uuid PK, audience/revoked_reason CHECK, 4 indeks) + ip_login_locks (ip PK). Testcontainers (Postgres 16) ustida 13/13 test yashil — flyway V1..V6 (V5 parallel S04 tomonidan qo'shilgan edi, konflikt yo'q). Faqat o'z fayllarim commit qilindi (openapi/v1/auth.yaml S06/T00 ishi, tegilmadi).

coder-ep-09-t01-s05 statusni o'zgartirdi Ishlanyapti → Review

coder-ep-09-t01-s05 yangiladi

Yangilandi: branch, commit_sha

coder-ep-09-t01-s05 qabul mezonini belgiladi

✓ `ip_login_locks` PK = `ip`; bitta IP uchun ikkinchi qator yozib bo'lmaydi

coder-ep-09-t01-s05 qabul mezonini belgiladi

✓ 4 indeksning hammasi mavjud (`pg_indexes` tekshiruvi), `(user_id) WHERE revoked_at IS NULL` qisman indeks

coder-ep-09-t01-s05 qabul mezonini belgiladi

✓ `audience` CHECK aynan 5 qiymatni qabul qiladi

coder-ep-09-t01-s05 qabul mezonini belgiladi

✓ `revoked_reason` CHECK aynan 9 qiymatni qabul qiladi; 10-qiymat rad etiladi

coder-ep-09-t01-s05 qabul mezonini belgiladi

✓ `user_sessions.id` uuid va default `gen_random_uuid()` bilan to'ladi

coder-ep-09-t01-s05 fayllarni qayd etdi

2 ta fayl qayd etildi

coder-ep-09-t01-s05 statusni o'zgartirdi Backlog → Ishlanyapti

Agent ishni boshladi

coder-ep-09-t01-s05 ishga oldi

pm-agent bog'liqlik qo'shdi

→ EP-09-T01-S03

pm-agent yaratdi

user_sessions + ip_login_locks — sessiya va IP qulfi