1use async_trait::async_trait;
2use sqlx::SqlitePool;
3use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions};
4use std::str::FromStr;
5use uuid::Uuid;
6
7#[allow(clippy::wildcard_imports)] use crate::storage::types::*;
9use crate::storage::{
10 CustomerStore, EnrollmentStore, LicenseStore, PaymentStore, SeatStore, SecurityStore,
11 VendorStore,
12};
13
14pub struct SqliteStorage {
15 pool: SqlitePool,
16}
17
18impl SqliteStorage {
19 pub async fn new(database_url: &str) -> crate::Result<Self> {
20 let opts = SqliteConnectOptions::from_str(database_url)
21 .map_err(|e| crate::Error::Database(e.to_string()))?
22 .pragma("foreign_keys", "ON")
23 .journal_mode(SqliteJournalMode::Wal)
24 .create_if_missing(true);
25
26 let pool = SqlitePoolOptions::new()
27 .connect_with(opts)
28 .await
29 .map_err(|e| crate::Error::Database(e.to_string()))?;
30
31 sqlx::migrate!("migrations/sqlite")
32 .run(&pool)
33 .await
34 .map_err(|e| crate::Error::Migration(e.to_string()))?;
35
36 Ok(Self { pool })
37 }
38
39 pub async fn in_memory() -> crate::Result<Self> {
40 let opts = SqliteConnectOptions::from_str("sqlite::memory:")
41 .map_err(|e| crate::Error::Database(e.to_string()))?
42 .pragma("foreign_keys", "ON");
43
44 let pool = SqlitePoolOptions::new()
45 .max_connections(1)
46 .connect_with(opts)
47 .await
48 .map_err(|e| crate::Error::Database(e.to_string()))?;
49
50 sqlx::migrate!("migrations/sqlite")
51 .run(&pool)
52 .await
53 .map_err(|e| crate::Error::Migration(e.to_string()))?;
54
55 Ok(Self { pool })
56 }
57}
58
59fn uuid_to_blob(id: Uuid) -> Vec<u8> {
60 id.as_bytes().to_vec()
61}
62
63fn blob_to_uuid(b: &[u8]) -> crate::Result<Uuid> {
64 Uuid::from_slice(b).map_err(|e| crate::Error::Corrupt(format!("invalid UUID bytes: {e}")))
65}
66
67fn bool_to_int(b: bool) -> i64 {
68 i64::from(b)
69}
70
71fn int_to_bool(n: i64) -> bool {
72 n != 0
73}
74
75fn encode_enum<T: std::fmt::Display>(t: &T) -> String {
76 t.to_string()
77}
78
79fn decode_enum<T: std::str::FromStr<Err = crate::Error>>(s: impl AsRef<str>) -> crate::Result<T> {
80 s.as_ref().parse()
81}
82
83fn decode_opt_uuid(b: Option<Vec<u8>>) -> crate::Result<Option<Uuid>> {
84 b.map(|bytes| blob_to_uuid(&bytes)).transpose()
85}
86
87#[derive(sqlx::FromRow)]
88struct DbVendorConfig {
89 id: i64,
90 public_key: Vec<u8>,
91 public_key_fingerprint: String,
92 registered_at: i64,
93 rotated_from_key: Option<Vec<u8>>,
94 rotated_at: Option<i64>,
95}
96
97#[derive(sqlx::FromRow)]
98struct DbProduct {
99 id: Vec<u8>,
100 name: String,
101 description: String,
102 connectivity_mode: String,
103 seat_count: i64,
104 expiry_policy: String,
105 grace_period_days: Option<i64>,
106 heartbeat_interval_secs: Option<i64>,
107 heartbeat_grace_secs: Option<i64>,
108 shutdown_countdown_secs: Option<i64>,
109 tsa_tier: String,
110 bundle_version: String,
111 transfer_policy: String,
112 pricing_amount: i64,
113 pricing_currency: String,
114 payment_provider: String,
115 min_client_version_warning: Option<String>,
116 min_client_version_required: Option<String>,
117 active: i64,
118 created_at: i64,
119 updated_at: i64,
120}
121
122#[derive(sqlx::FromRow)]
123struct DbProductTermDeclaration {
124 product_id: Vec<u8>,
125 warranty: String,
126 refund: String,
127 revocation: String,
128 expiry: String,
129 support_available: i64,
130 support_channels: String,
131 response_sla_hours: Option<i64>,
132 support_scope: Option<String>,
133 support_coverage: Option<String>,
134 updates_policy: String,
135}
136
137#[derive(sqlx::FromRow)]
138struct DbProductTermsDocument {
139 id: Vec<u8>,
140 product_id: Vec<u8>,
141 typst_source: String,
142 rendered_hash: String,
143 validation_status: String,
144 validation_findings: String,
145 vendor_acknowledged_at: Option<i64>,
146 vendor_acknowledged_findings: Option<String>,
147 activated_at: Option<i64>,
148 created_at: i64,
149}
150
151#[derive(sqlx::FromRow)]
152struct DbProductCustomerField {
153 id: Vec<u8>,
154 product_id: Vec<u8>,
155 field_key: String,
156 required: i64,
157 gdpr_basis: String,
158}
159
160#[derive(sqlx::FromRow)]
161struct DbUpgradePolicy {
162 id: Vec<u8>,
163 product_id: Vec<u8>,
164 from_version: String,
165 to_version: String,
166 policy: String,
167}
168
169#[derive(sqlx::FromRow)]
170struct DbCustomer {
171 id: Vec<u8>,
172 product_id: Vec<u8>,
173 full_name: String,
174 email: String,
175 field_values: String,
176 created_at: i64,
177 updated_at: i64,
178}
179
180#[derive(sqlx::FromRow)]
181struct DbConsentRecord {
182 id: Vec<u8>,
183 customer_id: Vec<u8>,
184 license_id: Vec<u8>,
185 terms_document_id: Vec<u8>,
186 terms_rendered_hash: String,
187 checkboxes_ticked: String,
188 accepted_at_ns: i64,
189 ip_address: String,
190 client_version: String,
191 protocol_version: i64,
192 terms_findings_shown: String,
193 payment_provider: String,
194 payment_provider_tier: String,
195}
196
197#[derive(sqlx::FromRow)]
198struct DbLicense {
199 id: Vec<u8>,
200 customer_id: Vec<u8>,
201 product_id: Vec<u8>,
202 bundle_version: String,
203 connectivity_mode: String,
204 seat_count: i64,
205 expiry_at: Option<i64>,
206 status: String,
207 superseded_by: Option<Vec<u8>>,
208 revoked_at: Option<i64>,
209 revocation_reason: Option<String>,
210 created_at: i64,
211 email_sent_at: Option<i64>,
212}
213
214#[derive(sqlx::FromRow)]
215struct DbFingerprintSeatBinding {
216 id: Vec<u8>,
217 license_id: Vec<u8>,
218 fingerprint_commitment: Vec<u8>,
219 seat_index: i64,
220 bound_at: i64,
221 last_verified_at: Option<i64>,
222 revoked_at: Option<i64>,
223 transfer_pending_at: Option<i64>,
224}
225
226#[derive(sqlx::FromRow)]
227struct DbIssuanceSecret {
228 license_id: Vec<u8>,
229 secret: Vec<u8>,
230 created_at: i64,
231}
232
233#[derive(sqlx::FromRow)]
234struct DbPaymentTransaction {
235 id: Vec<u8>,
236 license_id: Vec<u8>,
237 provider: String,
238 provider_transaction_id: String,
239 amount: i64,
240 currency: String,
241 provider_tier: String,
242 test_mode: i64,
243 status: String,
244 created_at: i64,
245 confirmed_at: Option<i64>,
246}
247
248#[derive(sqlx::FromRow)]
249struct DbTransferRequest {
250 id: Vec<u8>,
251 license_id: Vec<u8>,
252 old_fingerprint_commitment: Vec<u8>,
253 new_fingerprint_commitment: Vec<u8>,
254 requested_at: i64,
255 status: String,
256 vendor_note: Option<String>,
257 resolved_at: Option<i64>,
258}
259
260#[derive(sqlx::FromRow)]
261struct DbActiveSession {
262 id: Vec<u8>,
263 binding_id: Vec<u8>,
264 ephemeral_pubkey: Vec<u8>,
265 issued_at: i64,
266 expires_at: i64,
267 last_heartbeat_at: Option<i64>,
268 seq_no: i64,
269 status: String,
270}
271
272#[derive(sqlx::FromRow)]
273struct DbQuarantineCase {
274 id: Vec<u8>,
275 case_id: Vec<u8>,
276 binding_id: Vec<u8>,
277 session_id: Option<Vec<u8>>,
278 trigger: String,
279 triggered_at: i64,
280 status: String,
281 resolution: Option<String>,
282 resolved_at: Option<i64>,
283 vendor_note: Option<String>,
284}
285
286#[derive(sqlx::FromRow)]
287struct DbSecurityEvent {
288 id: i64,
289 event_id: Vec<u8>,
290 license_id: Vec<u8>,
291 binding_id: Vec<u8>,
292 session_id: Option<Vec<u8>>,
293 occurred_at_ns: i64,
294 received_at_ns: i64,
295 event_type: String,
296 severity: String,
297 payload: String,
298 response: String,
299 reviewed_by: Option<String>,
300 reviewed_at: Option<i64>,
301 case_id: Option<Vec<u8>>,
302}
303
304#[derive(sqlx::FromRow)]
305struct DbRevocationRecord {
306 license_id: Vec<u8>,
307 revoked_at: i64,
308 revoked_by: String,
309 reason: Option<String>,
310}
311
312#[derive(sqlx::FromRow)]
313struct DbEmailLogEntry {
314 id: Vec<u8>,
315 license_id: Vec<u8>,
316 email_type: String,
317 sent_to: String,
318 sent_at: i64,
319 success: i64,
320 error_message: Option<String>,
321}
322
323#[allow(clippy::unnecessary_wraps)] fn from_db_vendor_config(r: DbVendorConfig) -> crate::Result<VendorConfig> {
325 Ok(VendorConfig {
326 id: r.id,
327 public_key: r.public_key,
328 public_key_fingerprint: r.public_key_fingerprint,
329 registered_at: r.registered_at,
330 rotated_from_key: r.rotated_from_key,
331 rotated_at: r.rotated_at,
332 })
333}
334
335fn from_db_product(r: DbProduct) -> crate::Result<Product> {
336 Ok(Product {
337 id: blob_to_uuid(&r.id)?,
338 name: r.name,
339 description: r.description,
340 connectivity_mode: decode_enum(r.connectivity_mode)?,
341 seat_count: r.seat_count,
342 expiry_policy: r.expiry_policy,
343 grace_period_days: r.grace_period_days,
344 heartbeat_interval_secs: r.heartbeat_interval_secs,
345 heartbeat_grace_secs: r.heartbeat_grace_secs,
346 shutdown_countdown_secs: r.shutdown_countdown_secs,
347 tsa_tier: decode_enum(r.tsa_tier)?,
348 bundle_version: r.bundle_version,
349 transfer_policy: decode_enum(r.transfer_policy)?,
350 pricing_amount: r.pricing_amount,
351 pricing_currency: r.pricing_currency,
352 payment_provider: decode_enum(r.payment_provider)?,
353 min_client_version_warning: r.min_client_version_warning,
354 min_client_version_required: r.min_client_version_required,
355 active: int_to_bool(r.active),
356 created_at: r.created_at,
357 updated_at: r.updated_at,
358 })
359}
360
361fn from_db_term_declaration(r: DbProductTermDeclaration) -> crate::Result<ProductTermDeclaration> {
362 Ok(ProductTermDeclaration {
363 product_id: blob_to_uuid(&r.product_id)?,
364 warranty: r.warranty,
365 refund: r.refund,
366 revocation: r.revocation,
367 expiry: r.expiry,
368 support_available: int_to_bool(r.support_available),
369 support_channels: r.support_channels,
370 response_sla_hours: r.response_sla_hours,
371 support_scope: r.support_scope,
372 support_coverage: r.support_coverage,
373 updates_policy: r.updates_policy,
374 })
375}
376
377fn from_db_terms_document(r: DbProductTermsDocument) -> crate::Result<ProductTermsDocument> {
378 Ok(ProductTermsDocument {
379 id: blob_to_uuid(&r.id)?,
380 product_id: blob_to_uuid(&r.product_id)?,
381 typst_source: r.typst_source,
382 rendered_hash: r.rendered_hash,
383 validation_status: decode_enum(r.validation_status)?,
384 validation_findings: r.validation_findings,
385 vendor_acknowledged_at: r.vendor_acknowledged_at,
386 vendor_acknowledged_findings: r.vendor_acknowledged_findings,
387 activated_at: r.activated_at,
388 created_at: r.created_at,
389 })
390}
391
392fn from_db_customer_field(r: DbProductCustomerField) -> crate::Result<ProductCustomerField> {
393 Ok(ProductCustomerField {
394 id: blob_to_uuid(&r.id)?,
395 product_id: blob_to_uuid(&r.product_id)?,
396 field_key: r.field_key,
397 required: int_to_bool(r.required),
398 gdpr_basis: decode_enum(r.gdpr_basis)?,
399 })
400}
401
402fn from_db_upgrade_policy(r: DbUpgradePolicy) -> crate::Result<UpgradePolicyRow> {
403 Ok(UpgradePolicyRow {
404 id: blob_to_uuid(&r.id)?,
405 product_id: blob_to_uuid(&r.product_id)?,
406 from_version: r.from_version,
407 to_version: r.to_version,
408 policy: decode_enum(r.policy)?,
409 })
410}
411
412fn from_db_customer(r: DbCustomer) -> crate::Result<Customer> {
413 Ok(Customer {
414 id: blob_to_uuid(&r.id)?,
415 product_id: blob_to_uuid(&r.product_id)?,
416 full_name: r.full_name,
417 email: r.email,
418 field_values: r.field_values,
419 created_at: r.created_at,
420 updated_at: r.updated_at,
421 })
422}
423
424fn from_db_consent_record(r: DbConsentRecord) -> crate::Result<ConsentRecord> {
425 Ok(ConsentRecord {
426 id: blob_to_uuid(&r.id)?,
427 customer_id: blob_to_uuid(&r.customer_id)?,
428 license_id: blob_to_uuid(&r.license_id)?,
429 terms_document_id: blob_to_uuid(&r.terms_document_id)?,
430 terms_rendered_hash: r.terms_rendered_hash,
431 checkboxes_ticked: r.checkboxes_ticked,
432 accepted_at_ns: r.accepted_at_ns,
433 ip_address: r.ip_address,
434 client_version: r.client_version,
435 protocol_version: r.protocol_version,
436 terms_findings_shown: r.terms_findings_shown,
437 payment_provider: decode_enum(r.payment_provider)?,
438 payment_provider_tier: decode_enum(r.payment_provider_tier)?,
439 })
440}
441
442fn from_db_license(r: DbLicense) -> crate::Result<License> {
443 Ok(License {
444 id: blob_to_uuid(&r.id)?,
445 customer_id: blob_to_uuid(&r.customer_id)?,
446 product_id: blob_to_uuid(&r.product_id)?,
447 bundle_version: r.bundle_version,
448 connectivity_mode: decode_enum(r.connectivity_mode)?,
449 seat_count: r.seat_count,
450 expiry_at: r.expiry_at,
451 status: decode_enum(r.status)?,
452 superseded_by: decode_opt_uuid(r.superseded_by)?,
453 revoked_at: r.revoked_at,
454 revocation_reason: r.revocation_reason,
455 created_at: r.created_at,
456 email_sent_at: r.email_sent_at,
457 })
458}
459
460fn from_db_seat_binding(r: DbFingerprintSeatBinding) -> crate::Result<FingerprintSeatBinding> {
461 Ok(FingerprintSeatBinding {
462 id: blob_to_uuid(&r.id)?,
463 license_id: blob_to_uuid(&r.license_id)?,
464 fingerprint_commitment: r.fingerprint_commitment,
465 seat_index: r.seat_index,
466 bound_at: r.bound_at,
467 last_verified_at: r.last_verified_at,
468 revoked_at: r.revoked_at,
469 transfer_pending_at: r.transfer_pending_at,
470 })
471}
472
473fn from_db_issuance_secret(r: DbIssuanceSecret) -> crate::Result<IssuanceSecret> {
474 Ok(IssuanceSecret {
475 license_id: blob_to_uuid(&r.license_id)?,
476 secret: r.secret,
477 created_at: r.created_at,
478 })
479}
480
481fn from_db_payment_transaction(r: DbPaymentTransaction) -> crate::Result<PaymentTransaction> {
482 Ok(PaymentTransaction {
483 id: blob_to_uuid(&r.id)?,
484 license_id: blob_to_uuid(&r.license_id)?,
485 provider: decode_enum(r.provider)?,
486 provider_transaction_id: r.provider_transaction_id,
487 amount: r.amount,
488 currency: r.currency,
489 provider_tier: decode_enum(r.provider_tier)?,
490 test_mode: int_to_bool(r.test_mode),
491 status: decode_enum(r.status)?,
492 created_at: r.created_at,
493 confirmed_at: r.confirmed_at,
494 })
495}
496
497fn from_db_transfer_request(r: DbTransferRequest) -> crate::Result<TransferRequest> {
498 Ok(TransferRequest {
499 id: blob_to_uuid(&r.id)?,
500 license_id: blob_to_uuid(&r.license_id)?,
501 old_fingerprint_commitment: r.old_fingerprint_commitment,
502 new_fingerprint_commitment: r.new_fingerprint_commitment,
503 requested_at: r.requested_at,
504 status: decode_enum(r.status)?,
505 vendor_note: r.vendor_note,
506 resolved_at: r.resolved_at,
507 })
508}
509
510fn from_db_active_session(r: DbActiveSession) -> crate::Result<ActiveSession> {
511 Ok(ActiveSession {
512 id: blob_to_uuid(&r.id)?,
513 binding_id: blob_to_uuid(&r.binding_id)?,
514 ephemeral_pubkey: r.ephemeral_pubkey,
515 issued_at: r.issued_at,
516 expires_at: r.expires_at,
517 last_heartbeat_at: r.last_heartbeat_at,
518 seq_no: r.seq_no,
519 status: decode_enum(r.status)?,
520 })
521}
522
523fn from_db_quarantine_case(r: DbQuarantineCase) -> crate::Result<QuarantineCase> {
524 Ok(QuarantineCase {
525 id: blob_to_uuid(&r.id)?,
526 case_id: blob_to_uuid(&r.case_id)?,
527 binding_id: blob_to_uuid(&r.binding_id)?,
528 session_id: decode_opt_uuid(r.session_id)?,
529 trigger: decode_enum(r.trigger)?,
530 triggered_at: r.triggered_at,
531 status: decode_enum(r.status)?,
532 resolution: r.resolution,
533 resolved_at: r.resolved_at,
534 vendor_note: r.vendor_note,
535 })
536}
537
538fn from_db_security_event(r: DbSecurityEvent) -> crate::Result<SecurityEvent> {
539 Ok(SecurityEvent {
540 id: r.id,
541 event_id: blob_to_uuid(&r.event_id)?,
542 license_id: blob_to_uuid(&r.license_id)?,
543 binding_id: blob_to_uuid(&r.binding_id)?,
544 session_id: decode_opt_uuid(r.session_id)?,
545 occurred_at_ns: r.occurred_at_ns,
546 received_at_ns: r.received_at_ns,
547 event_type: r.event_type,
548 severity: decode_enum(r.severity)?,
549 payload: r.payload,
550 response: decode_enum(r.response)?,
551 reviewed_by: r.reviewed_by,
552 reviewed_at: r.reviewed_at,
553 case_id: decode_opt_uuid(r.case_id)?,
554 })
555}
556
557fn from_db_revocation_record(r: DbRevocationRecord) -> crate::Result<RevocationRecord> {
558 Ok(RevocationRecord {
559 license_id: blob_to_uuid(&r.license_id)?,
560 revoked_at: r.revoked_at,
561 revoked_by: decode_enum(r.revoked_by)?,
562 reason: r.reason,
563 })
564}
565
566fn from_db_email_log_entry(r: DbEmailLogEntry) -> crate::Result<EmailLogEntry> {
567 Ok(EmailLogEntry {
568 id: blob_to_uuid(&r.id)?,
569 license_id: blob_to_uuid(&r.license_id)?,
570 email_type: decode_enum(r.email_type)?,
571 sent_to: r.sent_to,
572 sent_at: r.sent_at,
573 success: int_to_bool(r.success),
574 error_message: r.error_message,
575 })
576}
577
578#[derive(sqlx::FromRow)]
579struct DbEnrollmentSession {
580 id: Vec<u8>,
581 product_id: Vec<u8>,
582 fingerprint_commitment: Vec<u8>,
583 customer_pubkey: Vec<u8>,
584 client_version: String,
585 protocol_version: i64,
586 state: String,
587 offer_nonce: Option<Vec<u8>>,
588 offer_expires_at: Option<i64>,
589 terms_document_id: Option<Vec<u8>>,
590 request_bytes: Vec<u8>,
591 offer_bytes: Option<Vec<u8>>,
592 receipt_bytes: Option<Vec<u8>>,
593 payment_intent_id: Option<String>,
594 payment_captured: i64,
595 grant_bytes: Option<Vec<u8>>,
596 transfer_request_id: Option<Vec<u8>>,
597 license_id: Option<Vec<u8>>,
598 abandon_reason: Option<String>,
599 created_at: i64,
600 updated_at: i64,
601}
602
603fn from_db_enrollment_session(r: DbEnrollmentSession) -> crate::Result<EnrollmentSession> {
604 Ok(EnrollmentSession {
605 id: blob_to_uuid(&r.id)?,
606 product_id: blob_to_uuid(&r.product_id)?,
607 fingerprint_commitment: r.fingerprint_commitment,
608 customer_pubkey: r.customer_pubkey,
609 client_version: r.client_version,
610 protocol_version: r.protocol_version,
611 state: decode_enum(r.state)?,
612 offer_nonce: r.offer_nonce,
613 offer_expires_at: r.offer_expires_at,
614 terms_document_id: decode_opt_uuid(r.terms_document_id)?,
615 request_bytes: r.request_bytes,
616 offer_bytes: r.offer_bytes,
617 receipt_bytes: r.receipt_bytes,
618 payment_intent_id: r.payment_intent_id,
619 payment_captured: int_to_bool(r.payment_captured),
620 grant_bytes: r.grant_bytes,
621 transfer_request_id: decode_opt_uuid(r.transfer_request_id)?,
622 license_id: decode_opt_uuid(r.license_id)?,
623 abandon_reason: r.abandon_reason,
624 created_at: r.created_at,
625 updated_at: r.updated_at,
626 })
627}
628
629#[async_trait]
630impl VendorStore for SqliteStorage {
631 async fn get_vendor_config(&self) -> crate::Result<Option<VendorConfig>> {
632 sqlx::query_as::<_, DbVendorConfig>("SELECT * FROM vendor_config WHERE id = 1")
633 .fetch_optional(&self.pool)
634 .await?
635 .map(from_db_vendor_config)
636 .transpose()
637 }
638
639 async fn upsert_vendor_config(&self, config: &VendorConfig) -> crate::Result<()> {
640 sqlx::query(
641 "INSERT OR REPLACE INTO vendor_config \
642 (id, public_key, public_key_fingerprint, registered_at, rotated_from_key, rotated_at) \
643 VALUES (1, ?, ?, ?, ?, ?)",
644 )
645 .bind(&config.public_key)
646 .bind(&config.public_key_fingerprint)
647 .bind(config.registered_at)
648 .bind(&config.rotated_from_key)
649 .bind(config.rotated_at)
650 .execute(&self.pool)
651 .await?;
652 Ok(())
653 }
654
655 async fn rotate_vendor_key(
656 &self,
657 new_public_key: &[u8],
658 new_fingerprint: &str,
659 rotated_from: &[u8],
660 rotated_at: i64,
661 ) -> crate::Result<()> {
662 sqlx::query(
663 "UPDATE vendor_config SET public_key = ?, public_key_fingerprint = ?, \
664 rotated_from_key = ?, rotated_at = ? WHERE id = 1",
665 )
666 .bind(new_public_key)
667 .bind(new_fingerprint)
668 .bind(rotated_from)
669 .bind(rotated_at)
670 .execute(&self.pool)
671 .await?;
672 Ok(())
673 }
674
675 async fn create_product(&self, p: &Product) -> crate::Result<()> {
676 sqlx::query(
677 "INSERT INTO products \
678 (id, name, description, connectivity_mode, seat_count, expiry_policy, \
679 grace_period_days, heartbeat_interval_secs, heartbeat_grace_secs, \
680 shutdown_countdown_secs, tsa_tier, bundle_version, transfer_policy, \
681 pricing_amount, pricing_currency, payment_provider, \
682 min_client_version_warning, min_client_version_required, \
683 active, created_at, updated_at) \
684 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
685 )
686 .bind(uuid_to_blob(p.id))
687 .bind(&p.name)
688 .bind(&p.description)
689 .bind(encode_enum(&p.connectivity_mode))
690 .bind(p.seat_count)
691 .bind(&p.expiry_policy)
692 .bind(p.grace_period_days)
693 .bind(p.heartbeat_interval_secs)
694 .bind(p.heartbeat_grace_secs)
695 .bind(p.shutdown_countdown_secs)
696 .bind(encode_enum(&p.tsa_tier))
697 .bind(&p.bundle_version)
698 .bind(encode_enum(&p.transfer_policy))
699 .bind(p.pricing_amount)
700 .bind(&p.pricing_currency)
701 .bind(encode_enum(&p.payment_provider))
702 .bind(&p.min_client_version_warning)
703 .bind(&p.min_client_version_required)
704 .bind(bool_to_int(p.active))
705 .bind(p.created_at)
706 .bind(p.updated_at)
707 .execute(&self.pool)
708 .await?;
709 Ok(())
710 }
711
712 async fn get_product(&self, id: Uuid) -> crate::Result<Option<Product>> {
713 sqlx::query_as::<_, DbProduct>("SELECT * FROM products WHERE id = ?")
714 .bind(uuid_to_blob(id))
715 .fetch_optional(&self.pool)
716 .await?
717 .map(from_db_product)
718 .transpose()
719 }
720
721 async fn list_products(&self) -> crate::Result<Vec<Product>> {
722 sqlx::query_as::<_, DbProduct>("SELECT * FROM products ORDER BY created_at ASC")
723 .fetch_all(&self.pool)
724 .await?
725 .into_iter()
726 .map(from_db_product)
727 .collect()
728 }
729
730 async fn update_product(&self, p: &Product) -> crate::Result<()> {
731 sqlx::query(
732 "UPDATE products SET \
733 name = ?, description = ?, connectivity_mode = ?, seat_count = ?, \
734 expiry_policy = ?, grace_period_days = ?, heartbeat_interval_secs = ?, \
735 heartbeat_grace_secs = ?, shutdown_countdown_secs = ?, tsa_tier = ?, \
736 bundle_version = ?, transfer_policy = ?, pricing_amount = ?, \
737 pricing_currency = ?, payment_provider = ?, min_client_version_warning = ?, \
738 min_client_version_required = ?, active = ?, updated_at = ? \
739 WHERE id = ?",
740 )
741 .bind(&p.name)
742 .bind(&p.description)
743 .bind(encode_enum(&p.connectivity_mode))
744 .bind(p.seat_count)
745 .bind(&p.expiry_policy)
746 .bind(p.grace_period_days)
747 .bind(p.heartbeat_interval_secs)
748 .bind(p.heartbeat_grace_secs)
749 .bind(p.shutdown_countdown_secs)
750 .bind(encode_enum(&p.tsa_tier))
751 .bind(&p.bundle_version)
752 .bind(encode_enum(&p.transfer_policy))
753 .bind(p.pricing_amount)
754 .bind(&p.pricing_currency)
755 .bind(encode_enum(&p.payment_provider))
756 .bind(&p.min_client_version_warning)
757 .bind(&p.min_client_version_required)
758 .bind(bool_to_int(p.active))
759 .bind(p.updated_at)
760 .bind(uuid_to_blob(p.id))
761 .execute(&self.pool)
762 .await?;
763 Ok(())
764 }
765
766 async fn upsert_term_declaration(&self, d: &ProductTermDeclaration) -> crate::Result<()> {
767 sqlx::query(
768 "INSERT OR REPLACE INTO product_term_declarations \
769 (product_id, warranty, refund, revocation, expiry, support_available, \
770 support_channels, response_sla_hours, support_scope, support_coverage, updates_policy) \
771 VALUES (?,?,?,?,?,?,?,?,?,?,?)",
772 )
773 .bind(uuid_to_blob(d.product_id))
774 .bind(&d.warranty)
775 .bind(&d.refund)
776 .bind(&d.revocation)
777 .bind(&d.expiry)
778 .bind(bool_to_int(d.support_available))
779 .bind(&d.support_channels)
780 .bind(d.response_sla_hours)
781 .bind(&d.support_scope)
782 .bind(&d.support_coverage)
783 .bind(&d.updates_policy)
784 .execute(&self.pool)
785 .await?;
786 Ok(())
787 }
788
789 async fn get_term_declaration(
790 &self,
791 product_id: Uuid,
792 ) -> crate::Result<Option<ProductTermDeclaration>> {
793 sqlx::query_as::<_, DbProductTermDeclaration>(
794 "SELECT * FROM product_term_declarations WHERE product_id = ?",
795 )
796 .bind(uuid_to_blob(product_id))
797 .fetch_optional(&self.pool)
798 .await?
799 .map(from_db_term_declaration)
800 .transpose()
801 }
802
803 async fn create_terms_document(&self, d: &ProductTermsDocument) -> crate::Result<()> {
804 sqlx::query(
805 "INSERT INTO product_terms_documents \
806 (id, product_id, typst_source, rendered_hash, validation_status, \
807 validation_findings, vendor_acknowledged_at, vendor_acknowledged_findings, \
808 activated_at, created_at) \
809 VALUES (?,?,?,?,?,?,?,?,?,?)",
810 )
811 .bind(uuid_to_blob(d.id))
812 .bind(uuid_to_blob(d.product_id))
813 .bind(&d.typst_source)
814 .bind(&d.rendered_hash)
815 .bind(encode_enum(&d.validation_status))
816 .bind(&d.validation_findings)
817 .bind(d.vendor_acknowledged_at)
818 .bind(&d.vendor_acknowledged_findings)
819 .bind(d.activated_at)
820 .bind(d.created_at)
821 .execute(&self.pool)
822 .await?;
823 Ok(())
824 }
825
826 async fn get_terms_document(&self, id: Uuid) -> crate::Result<Option<ProductTermsDocument>> {
827 sqlx::query_as::<_, DbProductTermsDocument>(
828 "SELECT * FROM product_terms_documents WHERE id = ?",
829 )
830 .bind(uuid_to_blob(id))
831 .fetch_optional(&self.pool)
832 .await?
833 .map(from_db_terms_document)
834 .transpose()
835 }
836
837 async fn get_active_terms_document(
838 &self,
839 product_id: Uuid,
840 ) -> crate::Result<Option<ProductTermsDocument>> {
841 sqlx::query_as::<_, DbProductTermsDocument>(
842 "SELECT * FROM product_terms_documents WHERE product_id = ? AND activated_at IS NOT NULL",
843 )
844 .bind(uuid_to_blob(product_id))
845 .fetch_optional(&self.pool)
846 .await?
847 .map(from_db_terms_document)
848 .transpose()
849 }
850
851 async fn list_terms_documents(
852 &self,
853 product_id: Uuid,
854 ) -> crate::Result<Vec<ProductTermsDocument>> {
855 sqlx::query_as::<_, DbProductTermsDocument>(
856 "SELECT * FROM product_terms_documents WHERE product_id = ? ORDER BY created_at ASC",
857 )
858 .bind(uuid_to_blob(product_id))
859 .fetch_all(&self.pool)
860 .await?
861 .into_iter()
862 .map(from_db_terms_document)
863 .collect()
864 }
865
866 async fn update_terms_document_validation(
867 &self,
868 id: Uuid,
869 status: TermsValidationStatus,
870 findings: &str,
871 ) -> crate::Result<()> {
872 sqlx::query(
873 "UPDATE product_terms_documents SET validation_status = ?, validation_findings = ? WHERE id = ?",
874 )
875 .bind(encode_enum(&status))
876 .bind(findings)
877 .bind(uuid_to_blob(id))
878 .execute(&self.pool)
879 .await?;
880 Ok(())
881 }
882
883 async fn acknowledge_terms_findings(
884 &self,
885 id: Uuid,
886 acknowledged_at: i64,
887 findings: &str,
888 ) -> crate::Result<()> {
889 sqlx::query(
890 "UPDATE product_terms_documents SET vendor_acknowledged_at = ?, vendor_acknowledged_findings = ? WHERE id = ?",
891 )
892 .bind(acknowledged_at)
893 .bind(findings)
894 .bind(uuid_to_blob(id))
895 .execute(&self.pool)
896 .await?;
897 Ok(())
898 }
899
900 async fn activate_terms_document(&self, id: Uuid, activated_at: i64) -> crate::Result<()> {
901 let id_blob = uuid_to_blob(id);
902
903 let row = sqlx::query_as::<_, (Vec<u8>,)>(
904 "SELECT product_id FROM product_terms_documents WHERE id = ?",
905 )
906 .bind(&id_blob)
907 .fetch_optional(&self.pool)
908 .await?;
909
910 let product_id_blob = match row {
911 Some(r) => r.0,
912 None => return Err(crate::Error::NotFound),
913 };
914
915 let mut tx = self.pool.begin().await?;
916
917 sqlx::query(
918 "UPDATE product_terms_documents SET activated_at = NULL WHERE product_id = ? AND id != ?",
919 )
920 .bind(&product_id_blob)
921 .bind(&id_blob)
922 .execute(&mut *tx)
923 .await?;
924
925 sqlx::query("UPDATE product_terms_documents SET activated_at = ? WHERE id = ?")
926 .bind(activated_at)
927 .bind(&id_blob)
928 .execute(&mut *tx)
929 .await?;
930
931 tx.commit().await?;
932 Ok(())
933 }
934
935 async fn replace_customer_fields(
936 &self,
937 product_id: Uuid,
938 fields: &[ProductCustomerField],
939 ) -> crate::Result<()> {
940 let product_id_blob = uuid_to_blob(product_id);
941 let mut tx = self.pool.begin().await?;
942
943 sqlx::query("DELETE FROM product_customer_fields WHERE product_id = ?")
944 .bind(&product_id_blob)
945 .execute(&mut *tx)
946 .await?;
947
948 for f in fields {
949 sqlx::query(
950 "INSERT INTO product_customer_fields \
951 (id, product_id, field_key, required, gdpr_basis) VALUES (?,?,?,?,?)",
952 )
953 .bind(uuid_to_blob(f.id))
954 .bind(&product_id_blob)
955 .bind(&f.field_key)
956 .bind(bool_to_int(f.required))
957 .bind(encode_enum(&f.gdpr_basis))
958 .execute(&mut *tx)
959 .await?;
960 }
961
962 tx.commit().await?;
963 Ok(())
964 }
965
966 async fn get_customer_fields(
967 &self,
968 product_id: Uuid,
969 ) -> crate::Result<Vec<ProductCustomerField>> {
970 sqlx::query_as::<_, DbProductCustomerField>(
971 "SELECT * FROM product_customer_fields WHERE product_id = ?",
972 )
973 .bind(uuid_to_blob(product_id))
974 .fetch_all(&self.pool)
975 .await?
976 .into_iter()
977 .map(from_db_customer_field)
978 .collect()
979 }
980
981 async fn create_upgrade_policy(&self, p: &UpgradePolicyRow) -> crate::Result<()> {
982 sqlx::query(
983 "INSERT INTO upgrade_policies (id, product_id, from_version, to_version, policy) VALUES (?,?,?,?,?)",
984 )
985 .bind(uuid_to_blob(p.id))
986 .bind(uuid_to_blob(p.product_id))
987 .bind(&p.from_version)
988 .bind(&p.to_version)
989 .bind(encode_enum(&p.policy))
990 .execute(&self.pool)
991 .await?;
992 Ok(())
993 }
994
995 async fn get_upgrade_policy(&self, id: Uuid) -> crate::Result<Option<UpgradePolicyRow>> {
996 sqlx::query_as::<_, DbUpgradePolicy>("SELECT * FROM upgrade_policies WHERE id = ?")
997 .bind(uuid_to_blob(id))
998 .fetch_optional(&self.pool)
999 .await?
1000 .map(from_db_upgrade_policy)
1001 .transpose()
1002 }
1003
1004 async fn find_upgrade_policy(
1005 &self,
1006 product_id: Uuid,
1007 from_version: &str,
1008 to_version: &str,
1009 ) -> crate::Result<Option<UpgradePolicyRow>> {
1010 sqlx::query_as::<_, DbUpgradePolicy>(
1011 "SELECT * FROM upgrade_policies WHERE product_id = ? AND from_version = ? AND to_version = ?",
1012 )
1013 .bind(uuid_to_blob(product_id))
1014 .bind(from_version)
1015 .bind(to_version)
1016 .fetch_optional(&self.pool)
1017 .await?
1018 .map(from_db_upgrade_policy)
1019 .transpose()
1020 }
1021
1022 async fn list_upgrade_policies(
1023 &self,
1024 product_id: Uuid,
1025 ) -> crate::Result<Vec<UpgradePolicyRow>> {
1026 sqlx::query_as::<_, DbUpgradePolicy>("SELECT * FROM upgrade_policies WHERE product_id = ?")
1027 .bind(uuid_to_blob(product_id))
1028 .fetch_all(&self.pool)
1029 .await?
1030 .into_iter()
1031 .map(from_db_upgrade_policy)
1032 .collect()
1033 }
1034
1035 async fn delete_upgrade_policy(&self, id: Uuid) -> crate::Result<()> {
1036 let result = sqlx::query("DELETE FROM upgrade_policies WHERE id = ?")
1037 .bind(uuid_to_blob(id))
1038 .execute(&self.pool)
1039 .await?;
1040 if result.rows_affected() == 0 {
1041 return Err(crate::Error::NotFound);
1042 }
1043 Ok(())
1044 }
1045}
1046
1047#[async_trait]
1048impl CustomerStore for SqliteStorage {
1049 async fn create_customer(&self, c: &Customer) -> crate::Result<()> {
1050 sqlx::query(
1051 "INSERT INTO customers (id, product_id, full_name, email, field_values, created_at, updated_at) \
1052 VALUES (?,?,?,?,?,?,?)",
1053 )
1054 .bind(uuid_to_blob(c.id))
1055 .bind(uuid_to_blob(c.product_id))
1056 .bind(&c.full_name)
1057 .bind(&c.email)
1058 .bind(&c.field_values)
1059 .bind(c.created_at)
1060 .bind(c.updated_at)
1061 .execute(&self.pool)
1062 .await?;
1063 Ok(())
1064 }
1065
1066 async fn get_customer(&self, id: Uuid) -> crate::Result<Option<Customer>> {
1067 sqlx::query_as::<_, DbCustomer>("SELECT * FROM customers WHERE id = ?")
1068 .bind(uuid_to_blob(id))
1069 .fetch_optional(&self.pool)
1070 .await?
1071 .map(from_db_customer)
1072 .transpose()
1073 }
1074
1075 async fn find_customer_by_email(
1076 &self,
1077 product_id: Uuid,
1078 email: &str,
1079 ) -> crate::Result<Option<Customer>> {
1080 sqlx::query_as::<_, DbCustomer>(
1081 "SELECT * FROM customers WHERE product_id = ? AND email = ?",
1082 )
1083 .bind(uuid_to_blob(product_id))
1084 .bind(email)
1085 .fetch_optional(&self.pool)
1086 .await?
1087 .map(from_db_customer)
1088 .transpose()
1089 }
1090
1091 async fn update_customer(&self, c: &Customer) -> crate::Result<()> {
1092 sqlx::query(
1093 "UPDATE customers SET full_name = ?, email = ?, field_values = ?, updated_at = ? WHERE id = ?",
1094 )
1095 .bind(&c.full_name)
1096 .bind(&c.email)
1097 .bind(&c.field_values)
1098 .bind(c.updated_at)
1099 .bind(uuid_to_blob(c.id))
1100 .execute(&self.pool)
1101 .await?;
1102 Ok(())
1103 }
1104}
1105
1106#[async_trait]
1107impl LicenseStore for SqliteStorage {
1108 async fn create_consent_record(&self, r: &ConsentRecord) -> crate::Result<()> {
1109 sqlx::query(
1110 "INSERT INTO consent_records \
1111 (id, customer_id, license_id, terms_document_id, terms_rendered_hash, \
1112 checkboxes_ticked, accepted_at_ns, ip_address, client_version, protocol_version, \
1113 terms_findings_shown, payment_provider, payment_provider_tier) \
1114 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
1115 )
1116 .bind(uuid_to_blob(r.id))
1117 .bind(uuid_to_blob(r.customer_id))
1118 .bind(uuid_to_blob(r.license_id))
1119 .bind(uuid_to_blob(r.terms_document_id))
1120 .bind(&r.terms_rendered_hash)
1121 .bind(&r.checkboxes_ticked)
1122 .bind(r.accepted_at_ns)
1123 .bind(&r.ip_address)
1124 .bind(&r.client_version)
1125 .bind(r.protocol_version)
1126 .bind(&r.terms_findings_shown)
1127 .bind(encode_enum(&r.payment_provider))
1128 .bind(encode_enum(&r.payment_provider_tier))
1129 .execute(&self.pool)
1130 .await?;
1131 Ok(())
1132 }
1133
1134 async fn get_consent_record(&self, id: Uuid) -> crate::Result<Option<ConsentRecord>> {
1135 sqlx::query_as::<_, DbConsentRecord>("SELECT * FROM consent_records WHERE id = ?")
1136 .bind(uuid_to_blob(id))
1137 .fetch_optional(&self.pool)
1138 .await?
1139 .map(from_db_consent_record)
1140 .transpose()
1141 }
1142
1143 async fn get_consent_records_for_license(
1144 &self,
1145 license_id: Uuid,
1146 ) -> crate::Result<Vec<ConsentRecord>> {
1147 sqlx::query_as::<_, DbConsentRecord>("SELECT * FROM consent_records WHERE license_id = ?")
1148 .bind(uuid_to_blob(license_id))
1149 .fetch_all(&self.pool)
1150 .await?
1151 .into_iter()
1152 .map(from_db_consent_record)
1153 .collect()
1154 }
1155
1156 async fn create_license(&self, l: &License) -> crate::Result<()> {
1157 let superseded_by = l.superseded_by.map(uuid_to_blob);
1158 sqlx::query(
1159 "INSERT INTO licenses \
1160 (id, customer_id, product_id, bundle_version, connectivity_mode, seat_count, \
1161 expiry_at, status, superseded_by, revoked_at, revocation_reason, created_at, email_sent_at) \
1162 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
1163 )
1164 .bind(uuid_to_blob(l.id))
1165 .bind(uuid_to_blob(l.customer_id))
1166 .bind(uuid_to_blob(l.product_id))
1167 .bind(&l.bundle_version)
1168 .bind(encode_enum(&l.connectivity_mode))
1169 .bind(l.seat_count)
1170 .bind(l.expiry_at)
1171 .bind(encode_enum(&l.status))
1172 .bind(superseded_by)
1173 .bind(l.revoked_at)
1174 .bind(&l.revocation_reason)
1175 .bind(l.created_at)
1176 .bind(l.email_sent_at)
1177 .execute(&self.pool)
1178 .await?;
1179 Ok(())
1180 }
1181
1182 async fn get_license(&self, id: Uuid) -> crate::Result<Option<License>> {
1183 sqlx::query_as::<_, DbLicense>("SELECT * FROM licenses WHERE id = ?")
1184 .bind(uuid_to_blob(id))
1185 .fetch_optional(&self.pool)
1186 .await?
1187 .map(from_db_license)
1188 .transpose()
1189 }
1190
1191 async fn list_licenses_for_customer(&self, customer_id: Uuid) -> crate::Result<Vec<License>> {
1192 sqlx::query_as::<_, DbLicense>(
1193 "SELECT * FROM licenses WHERE customer_id = ? ORDER BY created_at ASC",
1194 )
1195 .bind(uuid_to_blob(customer_id))
1196 .fetch_all(&self.pool)
1197 .await?
1198 .into_iter()
1199 .map(from_db_license)
1200 .collect()
1201 }
1202
1203 async fn update_license_status(
1204 &self,
1205 id: Uuid,
1206 status: LicenseStatus,
1207 revoked_at: Option<i64>,
1208 revocation_reason: Option<&str>,
1209 superseded_by: Option<Uuid>,
1210 ) -> crate::Result<()> {
1211 let superseded_by_blob = superseded_by.map(uuid_to_blob);
1212 sqlx::query(
1213 "UPDATE licenses SET status = ?, revoked_at = ?, revocation_reason = ?, superseded_by = ? WHERE id = ?",
1214 )
1215 .bind(encode_enum(&status))
1216 .bind(revoked_at)
1217 .bind(revocation_reason)
1218 .bind(superseded_by_blob)
1219 .bind(uuid_to_blob(id))
1220 .execute(&self.pool)
1221 .await?;
1222 Ok(())
1223 }
1224
1225 async fn update_license_email_sent(&self, id: Uuid, sent_at: i64) -> crate::Result<()> {
1226 sqlx::query("UPDATE licenses SET email_sent_at = ? WHERE id = ?")
1227 .bind(sent_at)
1228 .bind(uuid_to_blob(id))
1229 .execute(&self.pool)
1230 .await?;
1231 Ok(())
1232 }
1233}
1234
1235#[async_trait]
1236impl SeatStore for SqliteStorage {
1237 async fn create_seat_binding(&self, b: &FingerprintSeatBinding) -> crate::Result<()> {
1238 sqlx::query(
1239 "INSERT INTO fingerprint_seat_bindings \
1240 (id, license_id, fingerprint_commitment, seat_index, bound_at, last_verified_at, revoked_at, transfer_pending_at) \
1241 VALUES (?,?,?,?,?,?,?,?)",
1242 )
1243 .bind(uuid_to_blob(b.id))
1244 .bind(uuid_to_blob(b.license_id))
1245 .bind(&b.fingerprint_commitment)
1246 .bind(b.seat_index)
1247 .bind(b.bound_at)
1248 .bind(b.last_verified_at)
1249 .bind(b.revoked_at)
1250 .bind(b.transfer_pending_at)
1251 .execute(&self.pool)
1252 .await?;
1253 Ok(())
1254 }
1255
1256 async fn get_seat_binding(&self, id: Uuid) -> crate::Result<Option<FingerprintSeatBinding>> {
1257 sqlx::query_as::<_, DbFingerprintSeatBinding>(
1258 "SELECT * FROM fingerprint_seat_bindings WHERE id = ?",
1259 )
1260 .bind(uuid_to_blob(id))
1261 .fetch_optional(&self.pool)
1262 .await?
1263 .map(from_db_seat_binding)
1264 .transpose()
1265 }
1266
1267 async fn find_seat_binding_by_commitment(
1268 &self,
1269 license_id: Uuid,
1270 commitment: &[u8],
1271 ) -> crate::Result<Option<FingerprintSeatBinding>> {
1272 sqlx::query_as::<_, DbFingerprintSeatBinding>(
1273 "SELECT * FROM fingerprint_seat_bindings WHERE license_id = ? AND fingerprint_commitment = ?",
1274 )
1275 .bind(uuid_to_blob(license_id))
1276 .bind(commitment)
1277 .fetch_optional(&self.pool)
1278 .await?
1279 .map(from_db_seat_binding)
1280 .transpose()
1281 }
1282
1283 async fn list_seat_bindings(
1284 &self,
1285 license_id: Uuid,
1286 ) -> crate::Result<Vec<FingerprintSeatBinding>> {
1287 sqlx::query_as::<_, DbFingerprintSeatBinding>(
1288 "SELECT * FROM fingerprint_seat_bindings WHERE license_id = ?",
1289 )
1290 .bind(uuid_to_blob(license_id))
1291 .fetch_all(&self.pool)
1292 .await?
1293 .into_iter()
1294 .map(from_db_seat_binding)
1295 .collect()
1296 }
1297
1298 async fn count_active_seat_bindings(&self, license_id: Uuid) -> crate::Result<u32> {
1299 let row = sqlx::query_as::<_, (i64,)>(
1300 "SELECT COUNT(*) FROM fingerprint_seat_bindings WHERE license_id = ? AND revoked_at IS NULL",
1301 )
1302 .bind(uuid_to_blob(license_id))
1303 .fetch_one(&self.pool)
1304 .await?;
1305 Ok(u32::try_from(row.0)
1306 .map_err(|_| crate::Error::Corrupt("seat binding count out of u32 range".into()))?)
1307 }
1308
1309 async fn revoke_seat_binding(&self, id: Uuid, revoked_at: i64) -> crate::Result<()> {
1310 sqlx::query("UPDATE fingerprint_seat_bindings SET revoked_at = ? WHERE id = ?")
1311 .bind(revoked_at)
1312 .bind(uuid_to_blob(id))
1313 .execute(&self.pool)
1314 .await?;
1315 Ok(())
1316 }
1317
1318 async fn update_seat_binding_verified(&self, id: Uuid, verified_at: i64) -> crate::Result<()> {
1319 sqlx::query("UPDATE fingerprint_seat_bindings SET last_verified_at = ? WHERE id = ?")
1320 .bind(verified_at)
1321 .bind(uuid_to_blob(id))
1322 .execute(&self.pool)
1323 .await?;
1324 Ok(())
1325 }
1326
1327 async fn create_issuance_secret(&self, s: &IssuanceSecret) -> crate::Result<()> {
1328 sqlx::query("INSERT INTO issuance_secrets (license_id, secret, created_at) VALUES (?,?,?)")
1329 .bind(uuid_to_blob(s.license_id))
1330 .bind(&s.secret)
1331 .bind(s.created_at)
1332 .execute(&self.pool)
1333 .await?;
1334 Ok(())
1335 }
1336
1337 async fn get_issuance_secret(&self, license_id: Uuid) -> crate::Result<Option<IssuanceSecret>> {
1338 sqlx::query_as::<_, DbIssuanceSecret>("SELECT * FROM issuance_secrets WHERE license_id = ?")
1339 .bind(uuid_to_blob(license_id))
1340 .fetch_optional(&self.pool)
1341 .await?
1342 .map(from_db_issuance_secret)
1343 .transpose()
1344 }
1345
1346 async fn delete_issuance_secret(&self, license_id: Uuid) -> crate::Result<()> {
1347 let result = sqlx::query("DELETE FROM issuance_secrets WHERE license_id = ?")
1348 .bind(uuid_to_blob(license_id))
1349 .execute(&self.pool)
1350 .await?;
1351 if result.rows_affected() == 0 {
1352 return Err(crate::Error::NotFound);
1353 }
1354 Ok(())
1355 }
1356
1357 async fn create_session(&self, s: &ActiveSession) -> crate::Result<()> {
1358 sqlx::query(
1359 "INSERT INTO active_sessions \
1360 (id, binding_id, ephemeral_pubkey, issued_at, expires_at, last_heartbeat_at, seq_no, status) \
1361 VALUES (?,?,?,?,?,?,?,?)",
1362 )
1363 .bind(uuid_to_blob(s.id))
1364 .bind(uuid_to_blob(s.binding_id))
1365 .bind(&s.ephemeral_pubkey)
1366 .bind(s.issued_at)
1367 .bind(s.expires_at)
1368 .bind(s.last_heartbeat_at)
1369 .bind(s.seq_no)
1370 .bind(encode_enum(&s.status))
1371 .execute(&self.pool)
1372 .await?;
1373 Ok(())
1374 }
1375
1376 async fn get_session(&self, id: Uuid) -> crate::Result<Option<ActiveSession>> {
1377 sqlx::query_as::<_, DbActiveSession>("SELECT * FROM active_sessions WHERE id = ?")
1378 .bind(uuid_to_blob(id))
1379 .fetch_optional(&self.pool)
1380 .await?
1381 .map(from_db_active_session)
1382 .transpose()
1383 }
1384
1385 async fn get_active_session_for_binding(
1386 &self,
1387 binding_id: Uuid,
1388 ) -> crate::Result<Option<ActiveSession>> {
1389 sqlx::query_as::<_, DbActiveSession>(
1390 "SELECT * FROM active_sessions WHERE binding_id = ? AND status = 'Active'",
1391 )
1392 .bind(uuid_to_blob(binding_id))
1393 .fetch_optional(&self.pool)
1394 .await?
1395 .map(from_db_active_session)
1396 .transpose()
1397 }
1398
1399 async fn update_session_heartbeat(
1400 &self,
1401 id: Uuid,
1402 heartbeat_at: i64,
1403 seq_no: i64,
1404 ) -> crate::Result<()> {
1405 sqlx::query("UPDATE active_sessions SET last_heartbeat_at = ?, seq_no = ? WHERE id = ?")
1406 .bind(heartbeat_at)
1407 .bind(seq_no)
1408 .bind(uuid_to_blob(id))
1409 .execute(&self.pool)
1410 .await?;
1411 Ok(())
1412 }
1413
1414 async fn update_session_status(&self, id: Uuid, status: SessionStatus) -> crate::Result<()> {
1415 sqlx::query("UPDATE active_sessions SET status = ? WHERE id = ?")
1416 .bind(encode_enum(&status))
1417 .bind(uuid_to_blob(id))
1418 .execute(&self.pool)
1419 .await?;
1420 Ok(())
1421 }
1422
1423 async fn expire_sessions_before(&self, expires_at: i64) -> crate::Result<u64> {
1424 let result = sqlx::query(
1425 "UPDATE active_sessions SET status = 'Expired' WHERE expires_at < ? AND status = 'Active'",
1426 )
1427 .bind(expires_at)
1428 .execute(&self.pool)
1429 .await?;
1430 Ok(result.rows_affected())
1431 }
1432}
1433
1434#[async_trait]
1435impl PaymentStore for SqliteStorage {
1436 async fn create_payment_transaction(&self, t: &PaymentTransaction) -> crate::Result<()> {
1437 sqlx::query(
1438 "INSERT INTO payment_transactions \
1439 (id, license_id, provider, provider_transaction_id, amount, currency, \
1440 provider_tier, test_mode, status, created_at, confirmed_at) \
1441 VALUES (?,?,?,?,?,?,?,?,?,?,?)",
1442 )
1443 .bind(uuid_to_blob(t.id))
1444 .bind(uuid_to_blob(t.license_id))
1445 .bind(encode_enum(&t.provider))
1446 .bind(&t.provider_transaction_id)
1447 .bind(t.amount)
1448 .bind(&t.currency)
1449 .bind(encode_enum(&t.provider_tier))
1450 .bind(bool_to_int(t.test_mode))
1451 .bind(encode_enum(&t.status))
1452 .bind(t.created_at)
1453 .bind(t.confirmed_at)
1454 .execute(&self.pool)
1455 .await?;
1456 Ok(())
1457 }
1458
1459 async fn get_payment_transaction(&self, id: Uuid) -> crate::Result<Option<PaymentTransaction>> {
1460 sqlx::query_as::<_, DbPaymentTransaction>("SELECT * FROM payment_transactions WHERE id = ?")
1461 .bind(uuid_to_blob(id))
1462 .fetch_optional(&self.pool)
1463 .await?
1464 .map(from_db_payment_transaction)
1465 .transpose()
1466 }
1467
1468 async fn get_payment_transaction_for_license(
1469 &self,
1470 license_id: Uuid,
1471 ) -> crate::Result<Option<PaymentTransaction>> {
1472 sqlx::query_as::<_, DbPaymentTransaction>(
1473 "SELECT * FROM payment_transactions WHERE license_id = ?",
1474 )
1475 .bind(uuid_to_blob(license_id))
1476 .fetch_optional(&self.pool)
1477 .await?
1478 .map(from_db_payment_transaction)
1479 .transpose()
1480 }
1481
1482 async fn update_payment_status(
1483 &self,
1484 id: Uuid,
1485 status: PaymentStatus,
1486 confirmed_at: Option<i64>,
1487 ) -> crate::Result<()> {
1488 sqlx::query("UPDATE payment_transactions SET status = ?, confirmed_at = ? WHERE id = ?")
1489 .bind(encode_enum(&status))
1490 .bind(confirmed_at)
1491 .bind(uuid_to_blob(id))
1492 .execute(&self.pool)
1493 .await?;
1494 Ok(())
1495 }
1496
1497 async fn create_transfer_request(&self, r: &TransferRequest) -> crate::Result<()> {
1498 sqlx::query(
1499 "INSERT INTO transfer_requests \
1500 (id, license_id, old_fingerprint_commitment, new_fingerprint_commitment, \
1501 requested_at, status, vendor_note, resolved_at) \
1502 VALUES (?,?,?,?,?,?,?,?)",
1503 )
1504 .bind(uuid_to_blob(r.id))
1505 .bind(uuid_to_blob(r.license_id))
1506 .bind(&r.old_fingerprint_commitment)
1507 .bind(&r.new_fingerprint_commitment)
1508 .bind(r.requested_at)
1509 .bind(encode_enum(&r.status))
1510 .bind(&r.vendor_note)
1511 .bind(r.resolved_at)
1512 .execute(&self.pool)
1513 .await?;
1514 Ok(())
1515 }
1516
1517 async fn get_transfer_request(&self, id: Uuid) -> crate::Result<Option<TransferRequest>> {
1518 sqlx::query_as::<_, DbTransferRequest>("SELECT * FROM transfer_requests WHERE id = ?")
1519 .bind(uuid_to_blob(id))
1520 .fetch_optional(&self.pool)
1521 .await?
1522 .map(from_db_transfer_request)
1523 .transpose()
1524 }
1525
1526 async fn list_transfer_requests_for_license(
1527 &self,
1528 license_id: Uuid,
1529 ) -> crate::Result<Vec<TransferRequest>> {
1530 sqlx::query_as::<_, DbTransferRequest>(
1531 "SELECT * FROM transfer_requests WHERE license_id = ? ORDER BY requested_at ASC",
1532 )
1533 .bind(uuid_to_blob(license_id))
1534 .fetch_all(&self.pool)
1535 .await?
1536 .into_iter()
1537 .map(from_db_transfer_request)
1538 .collect()
1539 }
1540
1541 async fn resolve_transfer_request(
1542 &self,
1543 id: Uuid,
1544 status: TransferStatus,
1545 vendor_note: Option<&str>,
1546 resolved_at: i64,
1547 ) -> crate::Result<()> {
1548 sqlx::query(
1549 "UPDATE transfer_requests SET status = ?, vendor_note = ?, resolved_at = ? WHERE id = ?",
1550 )
1551 .bind(encode_enum(&status))
1552 .bind(vendor_note)
1553 .bind(resolved_at)
1554 .bind(uuid_to_blob(id))
1555 .execute(&self.pool)
1556 .await?;
1557 Ok(())
1558 }
1559}
1560
1561#[async_trait]
1562impl SecurityStore for SqliteStorage {
1563 async fn create_quarantine_case(&self, c: &QuarantineCase) -> crate::Result<()> {
1564 let session_id_blob = c.session_id.map(uuid_to_blob);
1565 sqlx::query(
1566 "INSERT INTO quarantine_cases \
1567 (id, case_id, binding_id, session_id, trigger, triggered_at, status, \
1568 resolution, resolved_at, vendor_note) \
1569 VALUES (?,?,?,?,?,?,?,?,?,?)",
1570 )
1571 .bind(uuid_to_blob(c.id))
1572 .bind(uuid_to_blob(c.case_id))
1573 .bind(uuid_to_blob(c.binding_id))
1574 .bind(session_id_blob)
1575 .bind(encode_enum(&c.trigger))
1576 .bind(c.triggered_at)
1577 .bind(encode_enum(&c.status))
1578 .bind(&c.resolution)
1579 .bind(c.resolved_at)
1580 .bind(&c.vendor_note)
1581 .execute(&self.pool)
1582 .await?;
1583 Ok(())
1584 }
1585
1586 async fn get_quarantine_case(&self, id: Uuid) -> crate::Result<Option<QuarantineCase>> {
1587 sqlx::query_as::<_, DbQuarantineCase>("SELECT * FROM quarantine_cases WHERE id = ?")
1588 .bind(uuid_to_blob(id))
1589 .fetch_optional(&self.pool)
1590 .await?
1591 .map(from_db_quarantine_case)
1592 .transpose()
1593 }
1594
1595 async fn get_quarantine_case_by_case_id(
1596 &self,
1597 case_id: Uuid,
1598 ) -> crate::Result<Option<QuarantineCase>> {
1599 sqlx::query_as::<_, DbQuarantineCase>("SELECT * FROM quarantine_cases WHERE case_id = ?")
1600 .bind(uuid_to_blob(case_id))
1601 .fetch_optional(&self.pool)
1602 .await?
1603 .map(from_db_quarantine_case)
1604 .transpose()
1605 }
1606
1607 async fn resolve_quarantine_case(
1608 &self,
1609 id: Uuid,
1610 status: QuarantineStatus,
1611 resolution: Option<&str>,
1612 resolved_at: i64,
1613 vendor_note: Option<&str>,
1614 ) -> crate::Result<()> {
1615 sqlx::query(
1616 "UPDATE quarantine_cases SET status = ?, resolution = ?, resolved_at = ?, vendor_note = ? WHERE id = ?",
1617 )
1618 .bind(encode_enum(&status))
1619 .bind(resolution)
1620 .bind(resolved_at)
1621 .bind(vendor_note)
1622 .bind(uuid_to_blob(id))
1623 .execute(&self.pool)
1624 .await?;
1625 Ok(())
1626 }
1627
1628 async fn create_security_event(&self, e: &SecurityEvent) -> crate::Result<()> {
1629 let session_id_blob = e.session_id.map(uuid_to_blob);
1630 let case_id_blob = e.case_id.map(uuid_to_blob);
1631 sqlx::query(
1632 "INSERT INTO security_events \
1633 (event_id, license_id, binding_id, session_id, occurred_at_ns, received_at_ns, \
1634 event_type, severity, payload, response, reviewed_by, reviewed_at, case_id) \
1635 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)",
1636 )
1637 .bind(uuid_to_blob(e.event_id))
1638 .bind(uuid_to_blob(e.license_id))
1639 .bind(uuid_to_blob(e.binding_id))
1640 .bind(session_id_blob)
1641 .bind(e.occurred_at_ns)
1642 .bind(e.received_at_ns)
1643 .bind(&e.event_type)
1644 .bind(encode_enum(&e.severity))
1645 .bind(&e.payload)
1646 .bind(encode_enum(&e.response))
1647 .bind(&e.reviewed_by)
1648 .bind(e.reviewed_at)
1649 .bind(case_id_blob)
1650 .execute(&self.pool)
1651 .await?;
1652 Ok(())
1653 }
1654
1655 async fn get_security_events_for_license(
1656 &self,
1657 license_id: Uuid,
1658 ) -> crate::Result<Vec<SecurityEvent>> {
1659 sqlx::query_as::<_, DbSecurityEvent>(
1660 "SELECT * FROM security_events WHERE license_id = ? ORDER BY occurred_at_ns ASC",
1661 )
1662 .bind(uuid_to_blob(license_id))
1663 .fetch_all(&self.pool)
1664 .await?
1665 .into_iter()
1666 .map(from_db_security_event)
1667 .collect()
1668 }
1669
1670 async fn mark_security_event_reviewed(
1671 &self,
1672 id: i64,
1673 reviewed_by: &str,
1674 reviewed_at: i64,
1675 ) -> crate::Result<()> {
1676 sqlx::query("UPDATE security_events SET reviewed_by = ?, reviewed_at = ? WHERE id = ?")
1677 .bind(reviewed_by)
1678 .bind(reviewed_at)
1679 .bind(id)
1680 .execute(&self.pool)
1681 .await?;
1682 Ok(())
1683 }
1684
1685 async fn create_revocation_record(&self, r: &RevocationRecord) -> crate::Result<()> {
1686 sqlx::query(
1687 "INSERT INTO revocation_records (license_id, revoked_at, revoked_by, reason) VALUES (?,?,?,?)",
1688 )
1689 .bind(uuid_to_blob(r.license_id))
1690 .bind(r.revoked_at)
1691 .bind(encode_enum(&r.revoked_by))
1692 .bind(&r.reason)
1693 .execute(&self.pool)
1694 .await?;
1695 Ok(())
1696 }
1697
1698 async fn get_revocation_record(
1699 &self,
1700 license_id: Uuid,
1701 ) -> crate::Result<Option<RevocationRecord>> {
1702 sqlx::query_as::<_, DbRevocationRecord>(
1703 "SELECT * FROM revocation_records WHERE license_id = ?",
1704 )
1705 .bind(uuid_to_blob(license_id))
1706 .fetch_optional(&self.pool)
1707 .await?
1708 .map(from_db_revocation_record)
1709 .transpose()
1710 }
1711
1712 async fn create_email_log_entry(&self, e: &EmailLogEntry) -> crate::Result<()> {
1713 sqlx::query(
1714 "INSERT INTO email_log (id, license_id, email_type, sent_to, sent_at, success, error_message) \
1715 VALUES (?,?,?,?,?,?,?)",
1716 )
1717 .bind(uuid_to_blob(e.id))
1718 .bind(uuid_to_blob(e.license_id))
1719 .bind(encode_enum(&e.email_type))
1720 .bind(&e.sent_to)
1721 .bind(e.sent_at)
1722 .bind(bool_to_int(e.success))
1723 .bind(&e.error_message)
1724 .execute(&self.pool)
1725 .await?;
1726 Ok(())
1727 }
1728
1729 async fn list_email_log_for_license(
1730 &self,
1731 license_id: Uuid,
1732 ) -> crate::Result<Vec<EmailLogEntry>> {
1733 sqlx::query_as::<_, DbEmailLogEntry>(
1734 "SELECT * FROM email_log WHERE license_id = ? ORDER BY sent_at ASC",
1735 )
1736 .bind(uuid_to_blob(license_id))
1737 .fetch_all(&self.pool)
1738 .await?
1739 .into_iter()
1740 .map(from_db_email_log_entry)
1741 .collect()
1742 }
1743}
1744
1745#[async_trait]
1746impl EnrollmentStore for SqliteStorage {
1747 async fn count_transferable_seat_bindings(&self, product_id: Uuid) -> crate::Result<u32> {
1748 let row = sqlx::query_as::<_, (i64,)>(
1749 "SELECT COUNT(*) FROM fingerprint_seat_bindings fsb \
1750 JOIN licenses l ON l.id = fsb.license_id \
1751 WHERE l.product_id = ? AND fsb.revoked_at IS NULL AND fsb.transfer_pending_at IS NULL",
1752 )
1753 .bind(uuid_to_blob(product_id))
1754 .fetch_one(&self.pool)
1755 .await?;
1756 Ok(u32::try_from(row.0).map_err(|_| {
1757 crate::Error::Corrupt("transferable binding count out of u32 range".into())
1758 })?)
1759 }
1760
1761 async fn set_seat_binding_transfer_pending(
1762 &self,
1763 id: Uuid,
1764 pending_at: Option<i64>,
1765 ) -> crate::Result<()> {
1766 sqlx::query("UPDATE fingerprint_seat_bindings SET transfer_pending_at = ? WHERE id = ?")
1767 .bind(pending_at)
1768 .bind(uuid_to_blob(id))
1769 .execute(&self.pool)
1770 .await?;
1771 Ok(())
1772 }
1773
1774 async fn create_enrollment_session(&self, s: &EnrollmentSession) -> crate::Result<()> {
1775 let terms_doc_id = s.terms_document_id.map(uuid_to_blob);
1776 let transfer_req_id = s.transfer_request_id.map(uuid_to_blob);
1777 let license_id_blob = s.license_id.map(uuid_to_blob);
1778 sqlx::query(
1779 "INSERT INTO enrollment_sessions \
1780 (id, product_id, fingerprint_commitment, customer_pubkey, client_version, \
1781 protocol_version, state, offer_nonce, offer_expires_at, terms_document_id, \
1782 request_bytes, offer_bytes, receipt_bytes, payment_intent_id, payment_captured, \
1783 grant_bytes, transfer_request_id, license_id, abandon_reason, created_at, updated_at) \
1784 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
1785 )
1786 .bind(uuid_to_blob(s.id))
1787 .bind(uuid_to_blob(s.product_id))
1788 .bind(&s.fingerprint_commitment)
1789 .bind(&s.customer_pubkey)
1790 .bind(&s.client_version)
1791 .bind(s.protocol_version)
1792 .bind(encode_enum(&s.state))
1793 .bind(&s.offer_nonce)
1794 .bind(s.offer_expires_at)
1795 .bind(terms_doc_id)
1796 .bind(&s.request_bytes)
1797 .bind(&s.offer_bytes)
1798 .bind(&s.receipt_bytes)
1799 .bind(&s.payment_intent_id)
1800 .bind(bool_to_int(s.payment_captured))
1801 .bind(&s.grant_bytes)
1802 .bind(transfer_req_id)
1803 .bind(license_id_blob)
1804 .bind(&s.abandon_reason)
1805 .bind(s.created_at)
1806 .bind(s.updated_at)
1807 .execute(&self.pool)
1808 .await?;
1809 Ok(())
1810 }
1811
1812 async fn get_enrollment_session(&self, id: Uuid) -> crate::Result<Option<EnrollmentSession>> {
1813 sqlx::query_as::<_, DbEnrollmentSession>("SELECT * FROM enrollment_sessions WHERE id = ?")
1814 .bind(uuid_to_blob(id))
1815 .fetch_optional(&self.pool)
1816 .await?
1817 .map(from_db_enrollment_session)
1818 .transpose()
1819 }
1820
1821 async fn get_session_by_payment_intent(
1822 &self,
1823 intent_id: &str,
1824 ) -> crate::Result<Option<EnrollmentSession>> {
1825 sqlx::query_as::<_, DbEnrollmentSession>(
1826 "SELECT * FROM enrollment_sessions WHERE payment_intent_id = ?",
1827 )
1828 .bind(intent_id)
1829 .fetch_optional(&self.pool)
1830 .await?
1831 .map(from_db_enrollment_session)
1832 .transpose()
1833 }
1834
1835 async fn update_enrollment_session(
1836 &self,
1837 id: Uuid,
1838 expected_updated_at: i64,
1839 update: EnrollmentSessionUpdate,
1840 ) -> crate::Result<()> {
1841 let mut sets: Vec<&str> = Vec::new();
1842 if update.state.is_some() {
1843 sets.push("state = ?");
1844 }
1845 if update.receipt_bytes.is_some() {
1846 sets.push("receipt_bytes = ?");
1847 }
1848 if update.payment_intent_id.is_some() {
1849 sets.push("payment_intent_id = ?");
1850 }
1851 if update.payment_captured.is_some() {
1852 sets.push("payment_captured = ?");
1853 }
1854 if update.grant_bytes.is_some() {
1855 sets.push("grant_bytes = ?");
1856 }
1857 if update.license_id.is_some() {
1858 sets.push("license_id = ?");
1859 }
1860 if update.abandon_reason.is_some() {
1861 sets.push("abandon_reason = ?");
1862 }
1863 sets.push("updated_at = ?");
1864
1865 let sql = format!(
1866 "UPDATE enrollment_sessions SET {} WHERE id = ? AND (updated_at = ? OR ? = 0)",
1867 sets.join(", ")
1868 );
1869
1870 let mut q = sqlx::query(&sql);
1871 if let Some(v) = update.state {
1872 q = q.bind(encode_enum(&v));
1873 }
1874 if let Some(v) = update.receipt_bytes {
1875 q = q.bind(v);
1876 }
1877 if let Some(v) = update.payment_intent_id {
1878 q = q.bind(v);
1879 }
1880 if let Some(v) = update.payment_captured {
1881 q = q.bind(bool_to_int(v));
1882 }
1883 if let Some(v) = update.grant_bytes {
1884 q = q.bind(v);
1885 }
1886 if let Some(v) = update.license_id {
1887 q = q.bind(v.map(uuid_to_blob));
1888 }
1889 if let Some(v) = update.abandon_reason {
1890 q = q.bind(v);
1891 }
1892 q = q.bind(update.updated_at);
1893 q = q.bind(uuid_to_blob(id));
1894 q = q.bind(expected_updated_at);
1895 q = q.bind(expected_updated_at);
1896
1897 let result = q.execute(&self.pool).await?;
1898 if result.rows_affected() == 0 {
1899 return Err(crate::Error::Conflict("concurrent update".to_string()));
1900 }
1901 Ok(())
1902 }
1903
1904 async fn list_grant_ready_sessions(&self) -> crate::Result<Vec<EnrollmentSession>> {
1905 sqlx::query_as::<_, DbEnrollmentSession>(
1906 "SELECT * FROM enrollment_sessions WHERE state = 'GrantReady'",
1907 )
1908 .fetch_all(&self.pool)
1909 .await?
1910 .into_iter()
1911 .map(from_db_enrollment_session)
1912 .collect()
1913 }
1914}