backbone_pos/infrastructure/persistence/pos_manager_pin_repository.rs
1//! Repository for PosManagerPin entities
2//!
3//! Originally generated by metaphor-schema; now **user-owned** — this exact path is declared under
4//! `user_owned` in `metaphor.codegen.yaml`, so the generator skips it wholesale. The custom methods
5//! below hold the hand-written credential SQL (4-layer rule: services orchestrate, repos hold SQL).
6//!
7//! The stored value is an ARGON2 HASH, never the PIN. These statements never SELECT the raw secret
8//! into anything but the verify path's one read, and the DTO layer strips the hash from every
9//! generated response surface (see `pos_manager_pin_dto.rs`).
10//!
11//! Tenancy is composition-installed (ADR-0029): pool reads/writes ride the ambient org scope's
12//! request-dedicated connection (the fence is the whole guard — a credential another unit owns is
13//! simply absent); the upsert takes the CALLER'S open, scope-relayed transaction so the
14//! first-PIN-exists proof and the write it gates commit together.
15
16use sqlx::{PgPool, Row};
17use uuid::Uuid;
18
19use backbone_orm::org_scope;
20
21use crate::domain::entity::PosManagerPin;
22
23/// Table name for PosManagerPin entities
24pub const TABLE_NAME: &str = "pos.pos_manager_pins";
25
26/// Repository for PosManagerPin entities.
27///
28/// All standard CRUD, soft-delete, pagination, and bulk methods are
29/// provided automatically via `Deref` to `backbone_orm::GenericCrudRepository`.
30pub struct PosManagerPinRepository(
31 backbone_orm::GenericCrudRepository<PosManagerPin, backbone_orm::SoftDelete>,
32);
33
34impl std::ops::Deref for PosManagerPinRepository {
35 type Target = backbone_orm::GenericCrudRepository<PosManagerPin, backbone_orm::SoftDelete>;
36 fn deref(&self) -> &Self::Target { &self.0 }
37}
38
39impl PosManagerPinRepository {
40 /// Create a new repository instance.
41 pub fn new(pool: PgPool) -> Self {
42 Self(backbone_orm::GenericCrudRepository::new(pool, TABLE_NAME))
43 }
44}
45
46/// A live credential as the verify path reads it: the hash to check against plus the lockout state.
47/// Read by manager — one live PIN per manager per unit (partial-unique re-declared by the composer).
48pub struct PinCredentialRow {
49 pub id: Uuid,
50 pub pin_hash: String,
51 pub failed_attempts: i32,
52 pub locked_until: Option<chrono::DateTime<chrono::Utc>>,
53}
54
55/// Hand-written credential SQL. Lives here (not in the write service) per the module's 4-layer rule.
56/// The ambient org scope's fence is the whole tenancy guard: every statement below is ID-only.
57impl PosManagerPinRepository {
58 /// Write (or replace) a manager's PIN hash, clearing any lockout and failure history — setting a
59 /// new PIN is the administrative unlock. Idempotent per manager within the caller's scope.
60 ///
61 /// The upsert ARBITRATES IN-TRANSACTION rather than via ON CONFLICT: the composer re-declares the
62 /// per-unit live-credential unique with an org-leading arbiter, and a company-led legacy arbiter
63 /// no longer exists to target. Taking `SELECT ... FOR UPDATE` on the live row serializes two
64 /// concurrent `set_pin` calls on the actual credential; the INSERT arm then cannot race. Takes
65 /// the CALLER'S connection so the first-PIN bootstrap proof and this write commit as one unit;
66 /// the caller has already relayed the ambient scope onto it.
67 pub async fn upsert_hash_on(
68 &self,
69 conn: &mut sqlx::PgConnection,
70 employee_party_id: Uuid,
71 pin_hash: &str,
72 now: chrono::DateTime<chrono::Utc>,
73 source_ip: Option<&str>,
74 ) -> Result<(), sqlx::Error> {
75 let existing = sqlx::query(
76 r#"SELECT id FROM pos.pos_manager_pins
77 WHERE employee_party_id=$1 AND (metadata->>'deleted_at') IS NULL
78 FOR UPDATE"#,
79 )
80 .bind(employee_party_id)
81 .fetch_optional(&mut *conn)
82 .await?;
83 if let Some(row) = existing {
84 sqlx::query(
85 r#"UPDATE pos.pos_manager_pins SET
86 pin_hash=$2, failed_attempts=0, locked_until=NULL,
87 last_attempt_at=$3, last_attempt_ip=$4
88 WHERE id=$1"#,
89 )
90 .bind(row.get::<Uuid, _>("id")).bind(pin_hash).bind(now).bind(source_ip)
91 .execute(conn)
92 .await?;
93 } else {
94 sqlx::query(
95 r#"INSERT INTO pos.pos_manager_pins
96 (id, employee_party_id, pin_hash, failed_attempts, locked_until, last_attempt_at, last_attempt_ip)
97 VALUES ($1,$2,$3,0,NULL,$4,$5)"#,
98 )
99 .bind(Uuid::new_v4()).bind(employee_party_id).bind(pin_hash).bind(now).bind(source_ip)
100 .execute(conn)
101 .await?;
102 }
103 Ok(())
104 }
105
106 /// Read a manager's live credential for verification. `Ok(None)` = no PIN set (or the credential
107 /// lives outside the caller's scope). ID-only: rides the ambient org scope.
108 pub async fn fetch_credential(
109 &self,
110 pool: &PgPool,
111 employee_party_id: Uuid,
112 ) -> Result<Option<PinCredentialRow>, sqlx::Error> {
113 let row = org_scope::fetch_optional_row_scoped(
114 pool,
115 sqlx::query(
116 r#"SELECT id, pin_hash, failed_attempts, locked_until FROM pos.pos_manager_pins
117 WHERE employee_party_id=$1 AND (metadata->>'deleted_at') IS NULL"#,
118 )
119 .bind(employee_party_id),
120 )
121 .await?;
122 Ok(row.map(|r| PinCredentialRow {
123 id: r.get("id"),
124 pin_hash: r.get("pin_hash"),
125 failed_attempts: r.get("failed_attempts"),
126 locked_until: r.get("locked_until"),
127 }))
128 }
129
130 /// Read and LOCK a manager's live credential for one verification, on the caller's
131 /// scope-relayed transaction. The row lock makes the lockout check, the hash comparison and
132 /// the counter update one step per manager: parallel guesses queue behind each other, so each
133 /// one sees the failures recorded before it and a locked credential stays locked.
134 pub async fn fetch_credential_for_update_on(
135 &self,
136 conn: &mut sqlx::PgConnection,
137 employee_party_id: Uuid,
138 ) -> Result<Option<PinCredentialRow>, sqlx::Error> {
139 let row = sqlx::query(
140 r#"SELECT id, pin_hash, failed_attempts, locked_until FROM pos.pos_manager_pins
141 WHERE employee_party_id=$1 AND (metadata->>'deleted_at') IS NULL
142 FOR UPDATE"#,
143 )
144 .bind(employee_party_id)
145 .fetch_optional(conn)
146 .await?;
147 Ok(row.map(|r| PinCredentialRow {
148 id: r.get("id"),
149 pin_hash: r.get("pin_hash"),
150 failed_attempts: r.get("failed_attempts"),
151 locked_until: r.get("locked_until"),
152 }))
153 }
154
155 /// Record a FAILED verification: bump the consecutive-failure counter and lock when it crosses
156 /// `max_attempts` (the service passes the computed unlock instant). Runs on the transaction
157 /// that locked the credential.
158 pub async fn record_failure_on(
159 &self,
160 conn: &mut sqlx::PgConnection,
161 id: Uuid,
162 max_attempts: u32,
163 lockout_until: chrono::DateTime<chrono::Utc>,
164 now: chrono::DateTime<chrono::Utc>,
165 source_ip: Option<&str>,
166 ) -> Result<(), sqlx::Error> {
167 sqlx::query(
168 r#"UPDATE pos.pos_manager_pins SET
169 failed_attempts = failed_attempts + 1,
170 locked_until = CASE WHEN failed_attempts + 1 >= $2 THEN $3 ELSE locked_until END,
171 last_attempt_at = $4,
172 last_attempt_ip = $5
173 WHERE id=$1"#,
174 )
175 .bind(id).bind(max_attempts as i32).bind(lockout_until).bind(now).bind(source_ip)
176 .execute(conn)
177 .await?;
178 Ok(())
179 }
180
181 /// Record a SUCCESSFUL verification: clear the failure counter and any lockout. Runs on the
182 /// transaction that locked the credential.
183 pub async fn record_success_on(
184 &self,
185 conn: &mut sqlx::PgConnection,
186 id: Uuid,
187 now: chrono::DateTime<chrono::Utc>,
188 source_ip: Option<&str>,
189 ) -> Result<(), sqlx::Error> {
190 sqlx::query(
191 "UPDATE pos.pos_manager_pins SET failed_attempts=0, locked_until=NULL, last_attempt_at=$2, last_attempt_ip=$3 WHERE id=$1",
192 )
193 .bind(id).bind(now).bind(source_ip)
194 .execute(conn)
195 .await?;
196 Ok(())
197 }
198}
199
200backbone_core::impl_crud_repository!(PosManagerPinRepository, PosManagerPin, soft_delete);