backbone_banking/infrastructure/persistence/
bank_transaction_repository.rs

1//! Repository for BankTransaction 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 BankTransaction SQL: the statement lines, the clearing engine's
6//! line+account reads, the allocation watermark, and the reconciliation close-gate's exception count
7//! (4-layer rule: services orchestrate, repos hold SQL).
8//!
9//! Thin newtype over `backbone_orm::GenericCrudRepository<BankTransaction, backbone_orm::SoftDelete>`.
10//! All standard CRUD methods are available via `Deref`.
11
12use anyhow::Result;
13use rust_decimal::Decimal;
14use sqlx::{PgPool, Row};
15use uuid::Uuid;
16
17use backbone_orm::org_scope;
18
19use crate::domain::entity::{BankTransaction, TxnStatus};
20
21/// Table name for BankTransaction entities
22pub const TABLE_NAME: &str = "banking.bank_transactions";
23
24/// Repository for BankTransaction entities.
25///
26/// All standard CRUD, soft-delete, pagination, and bulk methods are
27/// provided automatically via `Deref` to `backbone_orm::GenericCrudRepository`.
28pub struct BankTransactionRepository(
29    backbone_orm::GenericCrudRepository<BankTransaction, backbone_orm::SoftDelete>,
30);
31
32impl std::ops::Deref for BankTransactionRepository {
33    type Target = backbone_orm::GenericCrudRepository<BankTransaction, backbone_orm::SoftDelete>;
34    fn deref(&self) -> &Self::Target {
35        &self.0
36    }
37}
38
39impl BankTransactionRepository {
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/// The exact row a statement line writes.
47///
48/// `currency`/`status`/`allocated_amount` are not fields — the INSERT hard-codes `'IDR'`,
49/// `'unreconciled'` and `0` (a freshly imported line has cleared nothing). The amounts carry the
50/// SERVER-rounded values (2dp half-up).
51pub struct NewBankTransactionRow<'a> {
52    pub id: Uuid,
53    pub bank_account_id: Uuid,
54    pub import_id: Uuid,
55    pub txn_date: chrono::NaiveDate,
56    pub description: Option<&'a str>,
57    pub reference_no: Option<&'a str>,
58    pub deposit: Decimal,
59    pub withdrawal: Decimal,
60}
61
62/// The fields the match proposal selects a candidate against.
63pub struct MatchBasisRow {
64    pub deposit: Decimal,
65    pub withdrawal: Decimal,
66    pub reference_no: Option<String>,
67}
68
69/// A statement line's identity + match signals, as reconcile-preset candidate ordering reads it.
70pub struct CandidateLineBasisRow {
71    pub bank_account_id: Uuid,
72    pub deposit: Decimal,
73    pub withdrawal: Decimal,
74    pub txn_date: chrono::NaiveDate,
75    pub reference_no: Option<String>,
76}
77
78/// A statement line joined to its bank account's GL + clearing accounts — everything the clearing
79/// engine needs to build the bank-side leg.
80pub struct ClearingLineRow {
81    pub deposit: Decimal,
82    pub withdrawal: Decimal,
83    /// How much of this line has already been cleared — the line-level bound.
84    pub allocated_amount: Decimal,
85    pub currency: String,
86    /// The GL account the bank balance lives in.
87    pub gl_account_id: Uuid,
88    /// The account bank clearing nets to zero against.
89    pub clearing_account_id: Uuid,
90}
91
92/// A statement line joined to its bank account's GL account — the bank-charge path's narrower read
93/// (a charge never touches the clearing account).
94pub struct ChargeLineRow {
95    pub deposit: Decimal,
96    pub withdrawal: Decimal,
97    pub allocated_amount: Decimal,
98    pub currency: String,
99    pub gl_account_id: Uuid,
100}
101
102/// Hand-written BankTransaction SQL. Lives here (not in the write service) per the module's 4-layer
103/// rule.
104impl BankTransactionRepository {
105    /// Insert one statement line as `unreconciled`.
106    ///
107    /// Takes the CALLER'S connection so it commits with its import header. The caller has already
108    /// relayed the ambient org scope onto it (`org_scope::bind_org_scope_on`) — don't re-bind here
109    /// (ADR-0029).
110    pub async fn insert_transaction(
111        &self,
112        conn: &mut sqlx::PgConnection,
113        t: &NewBankTransactionRow<'_>,
114    ) -> Result<(), sqlx::Error> {
115        sqlx::query(
116            r#"INSERT INTO banking.bank_transactions
117                (id, bank_account_id, import_id, txn_date, description, reference_no,
118                 deposit, withdrawal, currency, status, allocated_amount)
119               VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'IDR','unreconciled'::txn_status,0)"#,
120        )
121        .bind(t.id)
122        .bind(t.bank_account_id)
123        .bind(t.import_id)
124        .bind(t.txn_date)
125        .bind(t.description)
126        .bind(t.reference_no)
127        .bind(t.deposit)
128        .bind(t.withdrawal)
129        .execute(conn)
130        .await?;
131        Ok(())
132    }
133
134    /// Read a line's amounts + reference for match proposal. `Ok(None)` = not found.
135    ///
136    /// ID-only: identified by the line id alone — no tenant argument. This rides the
137    /// request-dedicated connection carrying the composing service's org scope, so the
138    /// decorator-installed row-level fence decides visibility and another unit's line is simply
139    /// not found (ADR-0029).
140    pub async fn fetch_match_basis(
141        &self,
142        pool: &PgPool,
143        bank_transaction_id: Uuid,
144    ) -> Result<Option<MatchBasisRow>, sqlx::Error> {
145        let row = org_scope::fetch_optional_row_scoped(
146            pool,
147            sqlx::query(
148                "SELECT deposit, withdrawal, reference_no FROM banking.bank_transactions WHERE id=$1 AND (metadata->>'deleted_at') IS NULL",
149            )
150            .bind(bank_transaction_id),
151        )
152        .await?;
153        Ok(row.map(|r| MatchBasisRow {
154            deposit: r.get("deposit"),
155            withdrawal: r.get("withdrawal"),
156            reference_no: r.get("reference_no"),
157        }))
158    }
159
160    /// Read the line side of reconcile-preset candidate ordering: identity + the match signals
161    /// (reference, amount, date). `Ok(None)` = not found. ID-only + scoped read — see
162    /// [`Self::fetch_match_basis`].
163    pub async fn fetch_candidate_basis(
164        &self,
165        pool: &PgPool,
166        bank_transaction_id: Uuid,
167    ) -> Result<Option<crate::infrastructure::persistence::CandidateLineBasisRow>, sqlx::Error>
168    {
169        let row = org_scope::fetch_optional_row_scoped(
170            pool,
171            sqlx::query(
172                "SELECT bank_account_id, deposit, withdrawal, txn_date, reference_no FROM banking.bank_transactions WHERE id=$1 AND (metadata->>'deleted_at') IS NULL",
173            )
174            .bind(bank_transaction_id),
175        )
176        .await?;
177        Ok(row.map(
178            |r| crate::infrastructure::persistence::CandidateLineBasisRow {
179                bank_account_id: r.get("bank_account_id"),
180                deposit: r.get("deposit"),
181                withdrawal: r.get("withdrawal"),
182                txn_date: r.get("txn_date"),
183                reference_no: r.get("reference_no"),
184            },
185        ))
186    }
187
188    /// Read a line joined to its account's GL + clearing accounts. `Ok(None)` = not found. ID-only +
189    /// scoped read — see [`Self::fetch_match_basis`]. The caller relays the same ambient scope onto
190    /// its clearing transaction.
191    pub async fn fetch_clearing_line(
192        &self,
193        pool: &PgPool,
194        bank_transaction_id: Uuid,
195    ) -> Result<Option<ClearingLineRow>, sqlx::Error> {
196        let row = org_scope::fetch_optional_row_scoped(
197            pool,
198            sqlx::query(
199                r#"SELECT t.bank_account_id, t.deposit, t.withdrawal, t.allocated_amount, t.currency,
200                          a.gl_account_id, a.clearing_account_id
201                   FROM banking.bank_transactions t
202                   JOIN banking.bank_accounts a ON a.id = t.bank_account_id
203                   WHERE t.id=$1 AND (t.metadata->>'deleted_at') IS NULL"#,
204            )
205            .bind(bank_transaction_id),
206        )
207        .await?;
208        Ok(row.map(|r| ClearingLineRow {
209            deposit: r.get("deposit"),
210            withdrawal: r.get("withdrawal"),
211            allocated_amount: r.get("allocated_amount"),
212            currency: r.get("currency"),
213            gl_account_id: r.get("gl_account_id"),
214            clearing_account_id: r.get("clearing_account_id"),
215        }))
216    }
217
218    /// Read a line joined to its account's GL account, for the bank-charge path. `Ok(None)` = not
219    /// found. ID-only + scoped read — see [`Self::fetch_match_basis`].
220    pub async fn fetch_charge_line(
221        &self,
222        pool: &PgPool,
223        bank_transaction_id: Uuid,
224    ) -> Result<Option<ChargeLineRow>, sqlx::Error> {
225        let row = org_scope::fetch_optional_row_scoped(
226            pool,
227            sqlx::query(
228                r#"SELECT t.deposit, t.withdrawal, t.allocated_amount, t.currency, a.gl_account_id
229                   FROM banking.bank_transactions t JOIN banking.bank_accounts a ON a.id=t.bank_account_id
230                   WHERE t.id=$1 AND (t.metadata->>'deleted_at') IS NULL"#,
231            )
232            .bind(bank_transaction_id),
233        )
234        .await?;
235        Ok(row.map(|r| ChargeLineRow {
236            deposit: r.get("deposit"),
237            withdrawal: r.get("withdrawal"),
238            allocated_amount: r.get("allocated_amount"),
239            currency: r.get("currency"),
240            gl_account_id: r.get("gl_account_id"),
241        }))
242    }
243
244    /// Advance a line's allocation watermark + status after a clearance.
245    ///
246    /// `status` is the typed [`TxnStatus`] enum (ADR-0010 defense-in-depth): binding the enum — not a
247    /// `&str` cast at the DB — means an invalid status is a compile-time error at the caller, not a
248    /// runtime DB error after the watermark has already moved. Of the four `TxnStatus` variants, only
249    /// `Reconciled` and `PartlyReconciled` are valid post-clearance states (`Unreconciled` is the
250    /// import default and is advanced away from, never back to; `Ignored` is set by the ignore path,
251    /// not the allocation path) — the caller is expected to pick one of those two.
252    ///
253    /// Takes the CALLER'S connection so it commits with the clearance that justifies it — the
254    /// watermark and the clearance row must never disagree. The caller has already relayed the
255    /// ambient org scope onto it (`org_scope::bind_org_scope_on`) — don't re-bind here.
256    pub async fn set_allocation(
257        &self,
258        conn: &mut sqlx::PgConnection,
259        bank_transaction_id: Uuid,
260        allocated_amount: Decimal,
261        status: TxnStatus,
262    ) -> Result<(), sqlx::Error> {
263        sqlx::query(
264            "UPDATE banking.bank_transactions SET allocated_amount=$2, status=$3 WHERE id=$1",
265        )
266        .bind(bank_transaction_id)
267        .bind(allocated_amount)
268        .bind(status)
269        .execute(conn)
270        .await?;
271        Ok(())
272    }
273
274    /// Count the open lines (`unreconciled`/`partly_reconciled`) for an account in a period — the
275    /// reconciliation close-gate's exception count.
276    ///
277    /// This count IS the assertion behind "the bank agrees with our books", so its scope is
278    /// LOAD-BEARING: it rides the request-dedicated connection carrying the composing service's org
279    /// scope, so a decorated deployment's row-level fence decides which lines are counted — an
280    /// unscoped count under a live fence would read 0 rows and wrongly close a session that still
281    /// has open lines in another unit's books. An undecorated deployment is unfenced by design
282    /// (ADR-0029) and counts the account's lines plain.
283    pub async fn count_open_in_period(
284        &self,
285        pool: &PgPool,
286        bank_account_id: Uuid,
287        from_date: chrono::NaiveDate,
288        to_date: chrono::NaiveDate,
289    ) -> Result<i64, sqlx::Error> {
290        let row = org_scope::fetch_optional_row_scoped(
291            pool,
292            sqlx::query(
293                r#"SELECT count(*) AS open_lines FROM banking.bank_transactions
294                   WHERE bank_account_id=$1 AND txn_date BETWEEN $2 AND $3
295                     AND status IN ('unreconciled'::txn_status,'partly_reconciled'::txn_status)
296                     AND (metadata->>'deleted_at') IS NULL"#,
297            )
298            .bind(bank_account_id)
299            .bind(from_date)
300            .bind(to_date),
301        )
302        .await?;
303        row.map(|r| r.get::<i64, _>("open_lines"))
304            .ok_or(sqlx::Error::RowNotFound)
305    }
306}
307
308backbone_core::impl_crud_repository!(BankTransactionRepository, BankTransaction, soft_delete);
backbone_banking/infrastructure/persistence/bank_transaction_repository.rs

backbone_banking/infrastructure/persistence/
bank_transaction_repository.rs
