Skip to main content

valence_backend_sql/
typed_table.rs

1//! Typed SQL table DDL and row encode/decode (schema-driven columns).
2
3use std::collections::{HashMap, HashSet};
4use std::sync::{Arc, Mutex};
5
6use serde_json::{Map, Value};
7use sqlx::{Column, Row};
8use valence_core::error::{Error, Result};
9use valence_core::safe_ident::quote_sql_ident;
10use valence_core::schema::SchemaRegistry;
11use valence_core::storage_layout::{
12    decode_sql_cell, layout_diff, postgres_add_column, postgres_drop_default, postgres_set_default,
13    postgres_set_not_null, postgres_set_nullable, row_from_columns, split_record_fields,
14    sql_bind_text, sqlite_add_column, validate_write_types, AdditiveOp, FieldStorage, LayoutField,
15    SafeTweak, StorageLayout,
16};
17use valence_core::KnownEngines;
18
19use crate::sqlite_ops::assert_safe_table;
20
21/// Edge junction table (unchanged).
22pub use crate::document::{ensure_edges_table_ddl, EDGES_TABLE, ID_COLUMN};
23
24/// Per-backend cache of tables/fields already ensured for writes.
25///
26/// Skips inspect when every field in the requested layout was already ensured for that table.
27#[derive(Debug, Default, Clone)]
28pub struct WriteEnsureCache {
29    /// table → field names covered by a prior ensure.
30    inner: Arc<Mutex<HashMap<String, HashSet<String>>>>,
31}
32
33impl WriteEnsureCache {
34    /// Empty cache for a new backend connection.
35    #[must_use]
36    pub fn new() -> Self {
37        Self::default()
38    }
39
40    fn covers(&self, table: &str, layout: &StorageLayout) -> bool {
41        let guard = self.inner.lock().unwrap_or_else(|e| e.into_inner());
42        let Some(known) = guard.get(table) else {
43            return false;
44        };
45        layout.fields.iter().all(|f| known.contains(&f.name))
46    }
47
48    fn record(&self, layout: &StorageLayout) {
49        let mut guard = self.inner.lock().unwrap_or_else(|e| e.into_inner());
50        let entry = guard.entry(layout.table.clone()).or_default();
51        for f in &layout.fields {
52            entry.insert(f.name.clone());
53        }
54    }
55}
56
57fn registry_table(table: &str) -> bool {
58    SchemaRegistry::global().get_full_schema(table).is_some()
59}
60
61/// Create-table DDL for SQLite.
62pub fn ensure_typed_ddl_sqlite(layout: &StorageLayout) -> Result<String> {
63    layout.to_ddl(KnownEngines::SQLITE)
64}
65
66/// Create-table DDL for Postgres.
67pub fn ensure_typed_ddl_postgres(layout: &StorageLayout) -> Result<String> {
68    layout.to_ddl(KnownEngines::POSTGRES)
69}
70
71/// Ensure typed table exists (SQLite).
72pub async fn ensure_typed_table_sqlite(
73    pool: &sqlx::SqlitePool,
74    layout: &StorageLayout,
75) -> Result<()> {
76    assert_safe_table(&layout.table)?;
77    let ddl = ensure_typed_ddl_sqlite(layout)?;
78    sqlx::query(&ddl)
79        .execute(pool)
80        .await
81        .map_err(|e| Error::database(e.to_string()))?;
82    for f in &layout.fields {
83        if f.unique && f.name != "id" {
84            define_unique_index_column_sqlite(pool, &layout.table, &f.name).await?;
85        } else if f.indexed && f.name != "id" {
86            define_index_column_sqlite(pool, &layout.table, &f.name).await?;
87        }
88    }
89    Ok(())
90}
91
92/// Ensure typed table exists (Postgres).
93pub async fn ensure_typed_table_postgres(
94    pool: &sqlx::PgPool,
95    layout: &StorageLayout,
96) -> Result<()> {
97    assert_safe_table(&layout.table)?;
98    let ddl = ensure_typed_ddl_postgres(layout)?;
99    sqlx::query(&ddl)
100        .execute(pool)
101        .await
102        .map_err(|e| Error::database(e.to_string()))?;
103    for f in &layout.fields {
104        if f.unique && f.name != "id" {
105            define_unique_index_column_postgres(pool, &layout.table, &f.name).await?;
106        } else if f.indexed && f.name != "id" {
107            define_index_column_postgres(pool, &layout.table, &f.name).await?;
108        }
109    }
110    Ok(())
111}
112
113/// Inspect live SQLite columns → [`valence_core::storage_layout::StorageLayout`] (storage kinds inferred coarsely).
114pub async fn inspect_typed_layout_sqlite(
115    pool: &sqlx::SqlitePool,
116    table: &str,
117) -> Result<Option<StorageLayout>> {
118    assert_safe_table(table)?;
119    let rows = sqlx::query(&format!("PRAGMA table_info({})", quote_sql_ident(table)))
120        .fetch_all(pool)
121        .await
122        .map_err(|e| Error::database(e.to_string()))?;
123    if rows.is_empty() {
124        return Ok(None);
125    }
126    let mut fields = Vec::new();
127    for r in rows {
128        let name: String = r
129            .try_get("name")
130            .map_err(|e| Error::database(e.to_string()))?;
131        let ctype: String = r
132            .try_get::<String, _>("type")
133            .unwrap_or_default()
134            .to_ascii_uppercase();
135        let notnull: i64 = r.try_get("notnull").unwrap_or(0);
136        let pk: i64 = r.try_get("pk").unwrap_or(0);
137        let storage = match ctype.as_str() {
138            "INTEGER" => FieldStorage::Integer,
139            "REAL" => FieldStorage::Decimal,
140            "BOOLEAN" => FieldStorage::Boolean,
141            _ => FieldStorage::String,
142        };
143        fields.push(LayoutField {
144            name,
145            storage,
146            primary_key: pk != 0,
147            nullable: notnull == 0 && pk == 0,
148            unique: false,
149            indexed: false,
150            default: None,
151            record_table: None,
152        });
153    }
154    Ok(Some(StorageLayout {
155        table: table.to_string(),
156        fields,
157    }))
158}
159
160/// Inspect live Postgres columns.
161pub async fn inspect_typed_layout_postgres(
162    pool: &sqlx::PgPool,
163    table: &str,
164) -> Result<Option<StorageLayout>> {
165    assert_safe_table(table)?;
166    let rows = sqlx::query(
167        "SELECT column_name, data_type, is_nullable \
168         FROM information_schema.columns \
169         WHERE table_schema = 'public' AND table_name = $1 \
170         ORDER BY ordinal_position",
171    )
172    .bind(table)
173    .fetch_all(pool)
174    .await
175    .map_err(|e| Error::database(e.to_string()))?;
176    if rows.is_empty() {
177        return Ok(None);
178    }
179    let mut fields = Vec::new();
180    for r in rows {
181        let name: String = r
182            .try_get("column_name")
183            .map_err(|e| Error::database(e.to_string()))?;
184        let dtype: String = r.try_get("data_type").unwrap_or_default();
185        let nullable: String = r.try_get("is_nullable").unwrap_or_else(|_| "YES".into());
186        let storage = match dtype.as_str() {
187            "bigint" | "integer" | "smallint" => FieldStorage::Integer,
188            "boolean" => FieldStorage::Boolean,
189            "double precision" | "real" | "numeric" => FieldStorage::Decimal,
190            "jsonb" | "json" => FieldStorage::Json,
191            _ => FieldStorage::String,
192        };
193        fields.push(LayoutField {
194            primary_key: name == "id",
195            name,
196            storage,
197            nullable: nullable == "YES",
198            unique: false,
199            indexed: false,
200            default: None,
201            record_table: None,
202        });
203    }
204    Ok(Some(StorageLayout {
205        table: table.to_string(),
206        fields,
207    }))
208}
209
210/// Additive sync for SQLite.
211pub async fn sync_typed_table_sqlite(
212    pool: &sqlx::SqlitePool,
213    layout: &StorageLayout,
214) -> Result<()> {
215    let Some(live) = inspect_typed_layout_sqlite(pool, &layout.table).await? else {
216        return ensure_typed_table_sqlite(pool, layout).await;
217    };
218    // Inspect does not know unique/index flags — treat live unique/indexed as false so
219    // desired unique/index still emit CREATE INDEX IF NOT EXISTS.
220    let diff = layout_diff(layout, &live, KnownEngines::SQLITE)?;
221    for op in diff.ops {
222        match op {
223            AdditiveOp::AddField(f) => {
224                let ddl = sqlite_add_column(&layout.table, &f)?;
225                sqlx::query(&ddl)
226                    .execute(pool)
227                    .await
228                    .map_err(|e| Error::database(e.to_string()))?;
229                if f.unique {
230                    define_unique_index_column_sqlite(pool, &layout.table, &f.name).await?;
231                } else if f.indexed {
232                    define_index_column_sqlite(pool, &layout.table, &f.name).await?;
233                }
234            }
235            AdditiveOp::AddUniqueIndex { field } => {
236                define_unique_index_column_sqlite(pool, &layout.table, &field).await?;
237            }
238            AdditiveOp::AddIndex { field } => {
239                define_index_column_sqlite(pool, &layout.table, &field).await?;
240            }
241        }
242    }
243    if !diff.tweaks.is_empty() {
244        return Err(Error::Validation(format!(
245            "sqlite sync produced {} safe tweaks (unsupported)",
246            diff.tweaks.len()
247        )));
248    }
249    Ok(())
250}
251
252/// Additive sync for Postgres (includes safe nullability/default tweaks).
253pub async fn sync_typed_table_postgres(pool: &sqlx::PgPool, layout: &StorageLayout) -> Result<()> {
254    let Some(live) = inspect_typed_layout_postgres(pool, &layout.table).await? else {
255        return ensure_typed_table_postgres(pool, layout).await;
256    };
257    let diff = layout_diff(layout, &live, KnownEngines::POSTGRES)?;
258    for op in diff.ops {
259        match op {
260            AdditiveOp::AddField(f) => {
261                let ddl = postgres_add_column(&layout.table, &f)?;
262                sqlx::query(&ddl)
263                    .execute(pool)
264                    .await
265                    .map_err(|e| Error::database(e.to_string()))?;
266                if f.unique {
267                    define_unique_index_column_postgres(pool, &layout.table, &f.name).await?;
268                } else if f.indexed {
269                    define_index_column_postgres(pool, &layout.table, &f.name).await?;
270                }
271            }
272            AdditiveOp::AddUniqueIndex { field } => {
273                define_unique_index_column_postgres(pool, &layout.table, &field).await?;
274            }
275            AdditiveOp::AddIndex { field } => {
276                define_index_column_postgres(pool, &layout.table, &field).await?;
277            }
278        }
279    }
280    for tweak in diff.tweaks {
281        let ddl = match &tweak {
282            SafeTweak::SetNullable { field } => postgres_set_nullable(&layout.table, field)?,
283            SafeTweak::SetNotNull { field } => postgres_set_not_null(&layout.table, field)?,
284            SafeTweak::SetDefault { field, value } => {
285                postgres_set_default(&layout.table, field, value)?
286            }
287            SafeTweak::DropDefault { field } => postgres_drop_default(&layout.table, field)?,
288        };
289        tracing::info!(
290            target: "valence_storage",
291            table = layout.table.as_str(),
292            ?tweak,
293            "valence.storage.safe_tweak"
294        );
295        sqlx::query(&ddl)
296            .execute(pool)
297            .await
298            .map_err(|e| Error::database(e.to_string()))?;
299    }
300    Ok(())
301}
302
303pub async fn define_unique_index_column_sqlite(
304    pool: &sqlx::SqlitePool,
305    table: &str,
306    field: &str,
307) -> Result<()> {
308    assert_safe_table(table)?;
309    valence_core::safe_ident::assert_safe_ident(field)?;
310    let idx = format!("valence_unique_{table}_{field}");
311    let q = format!(
312        "CREATE UNIQUE INDEX IF NOT EXISTS {idx} ON {} ({})",
313        quote_sql_ident(table),
314        quote_sql_ident(field)
315    );
316    sqlx::query(&q)
317        .execute(pool)
318        .await
319        .map_err(|e| Error::database(e.to_string()))?;
320    Ok(())
321}
322
323pub async fn define_index_column_sqlite(
324    pool: &sqlx::SqlitePool,
325    table: &str,
326    field: &str,
327) -> Result<()> {
328    assert_safe_table(table)?;
329    valence_core::safe_ident::assert_safe_ident(field)?;
330    let idx = format!("valence_idx_{table}_{field}");
331    let q = format!(
332        "CREATE INDEX IF NOT EXISTS {idx} ON {} ({})",
333        quote_sql_ident(table),
334        quote_sql_ident(field)
335    );
336    sqlx::query(&q)
337        .execute(pool)
338        .await
339        .map_err(|e| Error::database(e.to_string()))?;
340    Ok(())
341}
342
343pub async fn define_unique_index_column_postgres(
344    pool: &sqlx::PgPool,
345    table: &str,
346    field: &str,
347) -> Result<()> {
348    assert_safe_table(table)?;
349    valence_core::safe_ident::assert_safe_ident(field)?;
350    let idx = format!("valence_unique_{table}_{field}");
351    let q = format!(
352        "CREATE UNIQUE INDEX IF NOT EXISTS {idx} ON {} ({})",
353        quote_sql_ident(table),
354        quote_sql_ident(field)
355    );
356    sqlx::query(&q)
357        .execute(pool)
358        .await
359        .map_err(|e| Error::database(e.to_string()))?;
360    Ok(())
361}
362
363pub async fn define_index_column_postgres(
364    pool: &sqlx::PgPool,
365    table: &str,
366    field: &str,
367) -> Result<()> {
368    assert_safe_table(table)?;
369    valence_core::safe_ident::assert_safe_ident(field)?;
370    let idx = format!("valence_idx_{table}_{field}");
371    let q = format!(
372        "CREATE INDEX IF NOT EXISTS {idx} ON {} ({})",
373        quote_sql_ident(table),
374        quote_sql_ident(field)
375    );
376    sqlx::query(&q)
377        .execute(pool)
378        .await
379        .map_err(|e| Error::database(e.to_string()))?;
380    Ok(())
381}
382
383/// Ensure layout columns exist for a write (create table or add missing columns only).
384///
385/// Schema-backed (registry) tables: at most one inspect/create per process (boot sync owns
386/// additive alters). Ad-hoc tables: same once-per-process cache, may add missing columns.
387///
388/// Unlike [`sync_typed_table_sqlite`], this does **not** refuse extra live columns —
389/// ad-hoc writes often send a field subset.
390pub async fn ensure_layout_for_write_sqlite(
391    pool: &sqlx::SqlitePool,
392    layout: &StorageLayout,
393    ensured: &WriteEnsureCache,
394) -> Result<()> {
395    let registry = registry_table(&layout.table);
396    if ensured.covers(&layout.table, layout) {
397        return Ok(());
398    }
399    match inspect_typed_layout_sqlite(pool, &layout.table).await? {
400        None => {
401            ensure_typed_table_sqlite(pool, layout).await?;
402        }
403        Some(live) => {
404            for f in &layout.fields {
405                if live.fields.iter().any(|l| l.name == f.name) {
406                    continue;
407                }
408                if registry {
409                    return Err(Error::Validation(format!(
410                        "missing column {}.{} — bump Schema.version and run sync_typed_tables_from_registry",
411                        layout.table, f.name
412                    )));
413                }
414                let ddl = sqlite_add_column(&layout.table, f)?;
415                sqlx::query(&ddl)
416                    .execute(pool)
417                    .await
418                    .map_err(|e| Error::database(e.to_string()))?;
419                if f.unique {
420                    define_unique_index_column_sqlite(pool, &layout.table, &f.name).await?;
421                } else if f.indexed {
422                    define_index_column_sqlite(pool, &layout.table, &f.name).await?;
423                }
424            }
425        }
426    }
427    ensured.record(layout);
428    Ok(())
429}
430
431pub async fn ensure_layout_for_write_postgres(
432    pool: &sqlx::PgPool,
433    layout: &StorageLayout,
434    ensured: &WriteEnsureCache,
435) -> Result<()> {
436    let registry = registry_table(&layout.table);
437    if ensured.covers(&layout.table, layout) {
438        return Ok(());
439    }
440    match inspect_typed_layout_postgres(pool, &layout.table).await? {
441        None => {
442            ensure_typed_table_postgres(pool, layout).await?;
443        }
444        Some(live) => {
445            for f in &layout.fields {
446                if live.fields.iter().any(|l| l.name == f.name) {
447                    continue;
448                }
449                if registry {
450                    return Err(Error::Validation(format!(
451                        "missing column {}.{} — bump Schema.version and run sync_typed_tables_from_registry",
452                        layout.table, f.name
453                    )));
454                }
455                let ddl = postgres_add_column(&layout.table, f)?;
456                sqlx::query(&ddl)
457                    .execute(pool)
458                    .await
459                    .map_err(|e| Error::database(e.to_string()))?;
460                if f.unique {
461                    define_unique_index_column_postgres(pool, &layout.table, &f.name).await?;
462                } else if f.indexed {
463                    define_index_column_postgres(pool, &layout.table, &f.name).await?;
464                }
465            }
466        }
467    }
468    ensured.record(layout);
469    Ok(())
470}
471
472fn column_list(layout: &StorageLayout) -> String {
473    layout
474        .fields
475        .iter()
476        .map(|f| quote_sql_ident(&f.name))
477        .collect::<Vec<_>>()
478        .join(", ")
479}
480
481fn placeholders_sqlite(n: usize) -> String {
482    (0..n).map(|_| "?").collect::<Vec<_>>().join(", ")
483}
484
485fn placeholders_postgres(n: usize) -> String {
486    (1..=n)
487        .map(|i| format!("${i}"))
488        .collect::<Vec<_>>()
489        .join(", ")
490}
491
492/// Insert typed row (SQLite).
493pub async fn create_record_typed_sqlite(
494    pool: &sqlx::SqlitePool,
495    layout: &StorageLayout,
496    content: Value,
497    ensured: &WriteEnsureCache,
498) -> Result<Value> {
499    validate_write_types(layout, &content)?;
500    ensure_layout_for_write_sqlite(pool, layout, ensured).await?;
501    let mut content = content;
502    valence_core::ttl::prepare_create_content_with_capability(
503        &layout.table,
504        valence_core::ttl::BackendTtlCapability::Deferred,
505        &mut content,
506    )?;
507    // Re-resolve layout after TTL stamp may add expire field.
508    let layout = StorageLayout::resolve_for_write(&layout.table, &content)?;
509    ensure_layout_for_write_sqlite(pool, &layout, ensured).await?;
510
511    let (id_opt, fields) = split_record_fields(content);
512    let id = id_opt.unwrap_or_else(|| uuid::Uuid::new_v4().to_string());
513
514    let cols = column_list(&layout);
515    let ph = placeholders_sqlite(layout.fields.len());
516    let q = format!(
517        "INSERT INTO {} ({cols}) VALUES ({ph})",
518        quote_sql_ident(&layout.table)
519    );
520    let mut query = sqlx::query(&q);
521    let mut out_fields = Map::new();
522    for f in &layout.fields {
523        if f.name == "id" {
524            query = query.bind(&id);
525            continue;
526        }
527        let val = fields.get(&f.name).cloned().unwrap_or(Value::Null);
528        out_fields.insert(f.name.clone(), val.clone());
529        query = bind_sqlite(query, f.storage, &val)?;
530    }
531    query
532        .execute(pool)
533        .await
534        .map_err(|e| Error::database(e.to_string()))?;
535    Ok(row_from_columns(&layout.table, &id, out_fields))
536}
537
538/// Insert typed row (Postgres).
539pub async fn create_record_typed_postgres(
540    pool: &sqlx::PgPool,
541    layout: &StorageLayout,
542    content: Value,
543    ensured: &WriteEnsureCache,
544) -> Result<Value> {
545    validate_write_types(layout, &content)?;
546    ensure_layout_for_write_postgres(pool, layout, ensured).await?;
547    let mut content = content;
548    valence_core::ttl::prepare_create_content_with_capability(
549        &layout.table,
550        valence_core::ttl::BackendTtlCapability::Deferred,
551        &mut content,
552    )?;
553    let layout = StorageLayout::resolve_for_write(&layout.table, &content)?;
554    ensure_layout_for_write_postgres(pool, &layout, ensured).await?;
555
556    let (id_opt, fields) = split_record_fields(content);
557    let id = id_opt.unwrap_or_else(|| uuid::Uuid::new_v4().to_string());
558
559    let cols = column_list(&layout);
560    let ph = placeholders_postgres(layout.fields.len());
561    let q = format!(
562        "INSERT INTO {} ({cols}) VALUES ({ph})",
563        quote_sql_ident(&layout.table)
564    );
565    let mut query = sqlx::query(&q);
566    let mut out_fields = Map::new();
567    for f in &layout.fields {
568        if f.name == "id" {
569            query = query.bind(&id);
570            continue;
571        }
572        let val = fields.get(&f.name).cloned().unwrap_or(Value::Null);
573        out_fields.insert(f.name.clone(), val.clone());
574        query = bind_postgres(query, f.storage, &val)?;
575    }
576    query
577        .execute(pool)
578        .await
579        .map_err(|e| Error::database(e.to_string()))?;
580    Ok(row_from_columns(&layout.table, &id, out_fields))
581}
582
583fn bind_sqlite<'q>(
584    query: sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>>,
585    storage: FieldStorage,
586    value: &Value,
587) -> Result<sqlx::query::Query<'q, sqlx::Sqlite, sqlx::sqlite::SqliteArguments<'q>>> {
588    if value.is_null() {
589        return Ok(query.bind(None::<String>));
590    }
591    match storage {
592        FieldStorage::Integer => {
593            let v = value
594                .as_i64()
595                .ok_or_else(|| Error::Validation(format!("expected integer, got {value}")))?;
596            Ok(query.bind(v))
597        }
598        FieldStorage::Boolean => Ok(query.bind(value.as_bool().unwrap_or(false))),
599        FieldStorage::Decimal => {
600            let v = value
601                .as_f64()
602                .ok_or_else(|| Error::Validation(format!("expected decimal, got {value}")))?;
603            Ok(query.bind(v))
604        }
605        FieldStorage::Json | FieldStorage::Currency => {
606            let s = serde_json::to_string(value).map_err(Error::serialization)?;
607            Ok(query.bind(s))
608        }
609        FieldStorage::String | FieldStorage::Date => {
610            let s = sql_bind_text(storage, value)?.unwrap_or_default();
611            Ok(query.bind(s))
612        }
613    }
614}
615
616fn bind_postgres<'q>(
617    query: sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>,
618    storage: FieldStorage,
619    value: &Value,
620) -> Result<sqlx::query::Query<'q, sqlx::Postgres, sqlx::postgres::PgArguments>> {
621    if value.is_null() {
622        return Ok(query.bind(None::<String>));
623    }
624    match storage {
625        FieldStorage::Integer => {
626            let v = value
627                .as_i64()
628                .ok_or_else(|| Error::Validation(format!("expected integer, got {value}")))?;
629            Ok(query.bind(v))
630        }
631        FieldStorage::Boolean => Ok(query.bind(value.as_bool().unwrap_or(false))),
632        FieldStorage::Decimal => {
633            let v = value
634                .as_f64()
635                .ok_or_else(|| Error::Validation(format!("expected decimal, got {value}")))?;
636            Ok(query.bind(v))
637        }
638        FieldStorage::Json | FieldStorage::Currency => Ok(query.bind(value.clone())),
639        FieldStorage::String | FieldStorage::Date => {
640            let s = sql_bind_text(storage, value)?.unwrap_or_default();
641            Ok(query.bind(s))
642        }
643    }
644}
645
646/// Prefer registry field storage kinds (Json/Currency/DateTime) over PRAGMA TEXT→String.
647fn layout_with_registry_storage(table: &str, live: StorageLayout) -> StorageLayout {
648    let Ok(reg) = StorageLayout::from_registry_table(table) else {
649        return live;
650    };
651    let mut fields = live.fields;
652    for f in &mut fields {
653        if let Some(rf) = reg.fields.iter().find(|r| r.name == f.name) {
654            f.storage = rf.storage;
655        }
656    }
657    StorageLayout {
658        table: live.table,
659        fields,
660    }
661}
662
663/// Fetch one typed row (SQLite).
664pub async fn get_record_typed_sqlite(
665    pool: &sqlx::SqlitePool,
666    table: &str,
667    id: &str,
668) -> Result<Option<Value>> {
669    assert_safe_table(table)?;
670    let layout = match inspect_typed_layout_sqlite(pool, table).await? {
671        Some(l) if !l.fields.is_empty() => layout_with_registry_storage(table, l),
672        _ => {
673            // Table missing — try registry layout ensure then miss.
674            if let Ok(layout) = StorageLayout::from_registry_table(table) {
675                ensure_typed_table_sqlite(pool, &layout).await?;
676            }
677            return Ok(None);
678        }
679    };
680    let cols = column_list(&layout);
681    let q = format!(
682        "SELECT {cols} FROM {} WHERE {} = ?",
683        quote_sql_ident(table),
684        quote_sql_ident("id")
685    );
686    let row = sqlx::query(&q)
687        .bind(id)
688        .fetch_optional(pool)
689        .await
690        .map_err(|e| Error::database(e.to_string()))?;
691    Ok(row.map(|r| row_from_sqlx_sqlite(table, &layout, &r)))
692}
693
694/// Fetch one typed row (Postgres).
695pub async fn get_record_typed_postgres(
696    pool: &sqlx::PgPool,
697    table: &str,
698    id: &str,
699) -> Result<Option<Value>> {
700    assert_safe_table(table)?;
701    let layout = match inspect_typed_layout_postgres(pool, table).await? {
702        Some(l) if !l.fields.is_empty() => layout_with_registry_storage(table, l),
703        _ => {
704            if let Ok(layout) = StorageLayout::from_registry_table(table) {
705                ensure_typed_table_postgres(pool, &layout).await?;
706            }
707            return Ok(None);
708        }
709    };
710    let cols = column_list(&layout);
711    let q = format!(
712        "SELECT {cols} FROM {} WHERE {} = $1",
713        quote_sql_ident(table),
714        quote_sql_ident("id")
715    );
716    let row = sqlx::query(&q)
717        .bind(id)
718        .fetch_optional(pool)
719        .await
720        .map_err(|e| Error::database(e.to_string()))?;
721    Ok(row.map(|r| row_from_sqlx_postgres(table, &layout, &r)))
722}
723
724fn row_from_sqlx_sqlite(
725    table: &str,
726    layout: &StorageLayout,
727    row: &sqlx::sqlite::SqliteRow,
728) -> Value {
729    let mut fields = Map::new();
730    let mut id = String::new();
731    for (i, f) in layout.fields.iter().enumerate() {
732        if f.name == "id" {
733            id = row.try_get::<String, _>(i).unwrap_or_default();
734            continue;
735        }
736        let val = match f.storage {
737            FieldStorage::Integer => {
738                let n: Option<i64> = row.try_get(i).unwrap_or(None);
739                n.map(|x| Value::Number(x.into())).unwrap_or(Value::Null)
740            }
741            FieldStorage::Boolean => {
742                let n: Option<i64> = row.try_get(i).unwrap_or(None);
743                n.map(|x| Value::Bool(x != 0)).unwrap_or(Value::Null)
744            }
745            FieldStorage::Decimal => {
746                let n: Option<f64> = row.try_get(i).unwrap_or(None);
747                n.and_then(serde_json::Number::from_f64)
748                    .map(Value::Number)
749                    .unwrap_or(Value::Null)
750            }
751            FieldStorage::Json | FieldStorage::Currency => {
752                let s: Option<String> = row.try_get(i).unwrap_or(None);
753                match s.as_deref() {
754                    None | Some("") => Value::Null,
755                    Some("null") => Value::Null,
756                    Some(t) => decode_sqlite_text_json(t),
757                }
758            }
759            FieldStorage::String | FieldStorage::Date => {
760                // Keep "" as a string. Mapping empty → Null breaks required `String`
761                // fields (e.g. Neutrino audit `error_message`) on read-back.
762                let s: Option<String> = row.try_get(i).unwrap_or(None);
763                match s {
764                    None => Value::Null,
765                    Some(t) => Value::String(t),
766                }
767            }
768        };
769        fields.insert(f.name.clone(), val);
770    }
771    row_from_columns(table, &id, fields)
772}
773
774fn row_from_sqlx_postgres(
775    table: &str,
776    layout: &StorageLayout,
777    row: &sqlx::postgres::PgRow,
778) -> Value {
779    let mut fields = Map::new();
780    let mut id = String::new();
781    for (i, f) in layout.fields.iter().enumerate() {
782        if f.name == "id" {
783            id = row.try_get::<String, _>(i).unwrap_or_default();
784            continue;
785        }
786        let val = match f.storage {
787            FieldStorage::Integer => {
788                let n: Option<i64> = row.try_get(i).ok();
789                n.map(|x| Value::Number(x.into())).unwrap_or(Value::Null)
790            }
791            FieldStorage::Boolean => {
792                let n: Option<bool> = row.try_get(i).ok();
793                n.map(Value::Bool).unwrap_or(Value::Null)
794            }
795            FieldStorage::Decimal => {
796                let n: Option<f64> = row.try_get(i).ok();
797                n.and_then(serde_json::Number::from_f64)
798                    .map(Value::Number)
799                    .unwrap_or(Value::Null)
800            }
801            FieldStorage::Json | FieldStorage::Currency => {
802                let v: Option<Value> = row.try_get(i).ok();
803                v.unwrap_or(Value::Null)
804            }
805            FieldStorage::String | FieldStorage::Date => {
806                let s: Option<String> = row.try_get(i).ok();
807                s.map(Value::String).unwrap_or(Value::Null)
808            }
809        };
810        fields.insert(f.name.clone(), val);
811    }
812    row_from_columns(table, &id, fields)
813}
814
815/// Update replaces all non-id columns from content.
816pub async fn update_record_typed_sqlite(
817    pool: &sqlx::SqlitePool,
818    layout: &StorageLayout,
819    id: &str,
820    content: Value,
821    ensured: &WriteEnsureCache,
822) -> Result<Value> {
823    if get_record_typed_sqlite(pool, &layout.table, id)
824        .await?
825        .is_none()
826    {
827        return Err(Error::NotFound(format!("{}:{id}", layout.table)));
828    }
829    ensure_layout_for_write_sqlite(pool, layout, ensured).await?;
830    let (_, fields) = split_record_fields(content);
831    let data: Vec<&LayoutField> = layout.fields.iter().filter(|f| f.name != "id").collect();
832    if data.is_empty() {
833        return get_record_typed_sqlite(pool, &layout.table, id)
834            .await?
835            .ok_or_else(|| Error::NotFound(format!("{}:{id}", layout.table)));
836    }
837    let sets = data
838        .iter()
839        .map(|f| format!("{} = ?", quote_sql_ident(&f.name)))
840        .collect::<Vec<_>>()
841        .join(", ");
842    let q = format!(
843        "UPDATE {} SET {sets} WHERE {} = ?",
844        quote_sql_ident(&layout.table),
845        quote_sql_ident("id")
846    );
847    let mut query = sqlx::query(&q);
848    let mut out = Map::new();
849    for f in &data {
850        let val = fields.get(&f.name).cloned().unwrap_or(Value::Null);
851        out.insert(f.name.clone(), val.clone());
852        query = bind_sqlite(query, f.storage, &val)?;
853    }
854    query = query.bind(id);
855    query
856        .execute(pool)
857        .await
858        .map_err(|e| Error::database(e.to_string()))?;
859    Ok(row_from_columns(&layout.table, id, out))
860}
861
862pub async fn update_record_typed_postgres(
863    pool: &sqlx::PgPool,
864    layout: &StorageLayout,
865    id: &str,
866    content: Value,
867    ensured: &WriteEnsureCache,
868) -> Result<Value> {
869    if get_record_typed_postgres(pool, &layout.table, id)
870        .await?
871        .is_none()
872    {
873        return Err(Error::NotFound(format!("{}:{id}", layout.table)));
874    }
875    ensure_layout_for_write_postgres(pool, layout, ensured).await?;
876    let (_, fields) = split_record_fields(content);
877    let data: Vec<&LayoutField> = layout.fields.iter().filter(|f| f.name != "id").collect();
878    if data.is_empty() {
879        return get_record_typed_postgres(pool, &layout.table, id)
880            .await?
881            .ok_or_else(|| Error::NotFound(format!("{}:{id}", layout.table)));
882    }
883    let sets = data
884        .iter()
885        .enumerate()
886        .map(|(i, f)| format!("{} = ${}", quote_sql_ident(&f.name), i + 1))
887        .collect::<Vec<_>>()
888        .join(", ");
889    let id_ph = data.len() + 1;
890    let q = format!(
891        "UPDATE {} SET {sets} WHERE {} = ${id_ph}",
892        quote_sql_ident(&layout.table),
893        quote_sql_ident("id")
894    );
895    let mut query = sqlx::query(&q);
896    let mut out = Map::new();
897    for f in &data {
898        let val = fields.get(&f.name).cloned().unwrap_or(Value::Null);
899        out.insert(f.name.clone(), val.clone());
900        query = bind_postgres(query, f.storage, &val)?;
901    }
902    query = query.bind(id);
903    query
904        .execute(pool)
905        .await
906        .map_err(|e| Error::database(e.to_string()))?;
907    Ok(row_from_columns(&layout.table, id, out))
908}
909
910/// Map a sqlx SQLite row with arbitrary columns into Valence JSON (SELECT *).
911///
912/// SQLite type affinity lets `try_get::<String>` succeed for INTEGER columns
913/// (e.g. unix-second datetimes). Prefer the declared column type so those stay
914/// JSON numbers — otherwise `datetime_unix` treats `"1700000000"` as RFC3339 and
915/// fails with chrono's "premature end of input".
916///
917/// When the table is registered, decode cells with [`StorageLayout`] field kinds so
918/// JSON/Currency TEXT columns become objects (not opaque strings).
919pub fn map_select_row_sqlite(table: &str, row: &sqlx::sqlite::SqliteRow) -> Value {
920    use sqlx::TypeInfo;
921
922    if let Ok(layout) = StorageLayout::from_registry_table(table) {
923        return map_select_row_sqlite_with_layout(table, &layout, row);
924    }
925
926    let cols = row.columns();
927    let mut fields = Map::new();
928    let mut id = String::new();
929    for col in cols {
930        let name = col.name();
931        if name.eq_ignore_ascii_case("id") {
932            id = row.try_get::<String, _>(name).unwrap_or_default();
933            continue;
934        }
935        let type_name = col.type_info().name();
936        let upper = type_name.to_ascii_uppercase();
937        if upper == "BOOLEAN" {
938            if let Ok(b) = row.try_get::<bool, _>(name) {
939                fields.insert(name.to_string(), Value::Bool(b));
940            } else if let Ok(i) = row.try_get::<i64, _>(name) {
941                fields.insert(name.to_string(), Value::Bool(i != 0));
942            }
943            continue;
944        }
945        if upper == "INTEGER" || upper == "INT" || upper == "BIGINT" {
946            // Prefer i64: sqlx can coerce any integer to bool (nonzero → true), which
947            // would destroy unix-second datetimes and other numeric cells.
948            if let Ok(i) = row.try_get::<i64, _>(name) {
949                fields.insert(name.to_string(), Value::Number(i.into()));
950            }
951            continue;
952        }
953        if upper == "REAL" || upper == "FLOAT" || upper == "DOUBLE" {
954            if let Ok(f) = row.try_get::<f64, _>(name) {
955                if let Some(n) = serde_json::Number::from_f64(f) {
956                    fields.insert(name.to_string(), Value::Number(n));
957                }
958            }
959            continue;
960        }
961        // TEXT / BLOB / unknown: prefer JSON object/array parse, then string.
962        if let Ok(s) = row.try_get::<String, _>(name) {
963            fields.insert(name.to_string(), decode_sqlite_text_json(&s));
964        } else if let Ok(i) = row.try_get::<i64, _>(name) {
965            fields.insert(name.to_string(), Value::Number(i.into()));
966        } else if let Ok(f) = row.try_get::<f64, _>(name) {
967            if let Some(n) = serde_json::Number::from_f64(f) {
968                fields.insert(name.to_string(), Value::Number(n));
969            }
970        } else if let Ok(b) = row.try_get::<bool, _>(name) {
971            fields.insert(name.to_string(), Value::Bool(b));
972        }
973    }
974    if id.is_empty() {
975        return Value::Object(fields);
976    }
977    row_from_columns(table, &id, fields)
978}
979
980fn decode_sqlite_text_json(s: &str) -> Value {
981    match serde_json::from_str::<Value>(s) {
982        Ok(Value::String(inner)) => {
983            // Double-encoded JSON document stored as a JSON string cell.
984            serde_json::from_str(&inner).unwrap_or(Value::String(inner))
985        }
986        Ok(v) => v,
987        Err(_) => Value::String(s.to_string()),
988    }
989}
990
991fn map_select_row_sqlite_with_layout(
992    table: &str,
993    layout: &StorageLayout,
994    row: &sqlx::sqlite::SqliteRow,
995) -> Value {
996    let mut fields = Map::new();
997    let mut id = String::new();
998    for f in &layout.fields {
999        if f.name == "id" {
1000            id = row
1001                .try_get::<String, _>(f.name.as_str())
1002                .unwrap_or_default();
1003            continue;
1004        }
1005        let val = match f.storage {
1006            FieldStorage::Integer => {
1007                let n: Option<i64> = row.try_get(f.name.as_str()).unwrap_or(None);
1008                n.map(|x| Value::Number(x.into())).unwrap_or(Value::Null)
1009            }
1010            FieldStorage::Boolean => {
1011                let n: Option<i64> = row.try_get(f.name.as_str()).unwrap_or(None);
1012                n.map(|x| Value::Bool(x != 0)).unwrap_or(Value::Null)
1013            }
1014            FieldStorage::Decimal => {
1015                let n: Option<f64> = row.try_get(f.name.as_str()).unwrap_or(None);
1016                n.and_then(serde_json::Number::from_f64)
1017                    .map(Value::Number)
1018                    .unwrap_or(Value::Null)
1019            }
1020            FieldStorage::Json | FieldStorage::Currency => {
1021                let s: Option<String> = row.try_get(f.name.as_str()).unwrap_or(None);
1022                match s.as_deref() {
1023                    None | Some("") => Value::Null,
1024                    Some("null") => Value::Null,
1025                    Some(t) => decode_sqlite_text_json(t),
1026                }
1027            }
1028            FieldStorage::String | FieldStorage::Date => {
1029                // Keep "" as a string. Mapping empty → Null breaks required `String`
1030                // fields (e.g. Neutrino audit `error_message`) on read-back.
1031                let s: Option<String> = row.try_get(f.name.as_str()).unwrap_or(None);
1032                match s {
1033                    None => Value::Null,
1034                    Some(t) => Value::String(t),
1035                }
1036            }
1037        };
1038        fields.insert(f.name.clone(), val);
1039    }
1040    // Include any unexpected SELECT columns (best-effort).
1041    for col in row.columns() {
1042        let name = col.name();
1043        if name.eq_ignore_ascii_case("id") || fields.contains_key(name) {
1044            continue;
1045        }
1046        if let Ok(s) = row.try_get::<String, _>(name) {
1047            fields.insert(name.to_string(), decode_sqlite_text_json(&s));
1048        } else if let Ok(i) = row.try_get::<i64, _>(name) {
1049            fields.insert(name.to_string(), Value::Number(i.into()));
1050        }
1051    }
1052    if id.is_empty() {
1053        return Value::Object(fields);
1054    }
1055    row_from_columns(table, &id, fields)
1056}
1057
1058pub fn map_select_row_postgres(table: &str, row: &sqlx::postgres::PgRow) -> Value {
1059    use sqlx::Column;
1060    let cols = row.columns();
1061    let mut fields = Map::new();
1062    let mut id = String::new();
1063    for col in cols {
1064        let name = col.name();
1065        if name.eq_ignore_ascii_case("id") {
1066            id = row.try_get::<String, _>(name).unwrap_or_default();
1067            continue;
1068        }
1069        if let Ok(v) = row.try_get::<Value, _>(name) {
1070            fields.insert(name.to_string(), v);
1071        } else if let Ok(s) = row.try_get::<String, _>(name) {
1072            fields.insert(name.to_string(), Value::String(s));
1073        } else if let Ok(i) = row.try_get::<i64, _>(name) {
1074            fields.insert(name.to_string(), Value::Number(i.into()));
1075        } else if let Ok(b) = row.try_get::<bool, _>(name) {
1076            fields.insert(name.to_string(), Value::Bool(b));
1077        } else if let Ok(f) = row.try_get::<f64, _>(name) {
1078            if let Some(n) = serde_json::Number::from_f64(f) {
1079                fields.insert(name.to_string(), Value::Number(n));
1080            }
1081        }
1082    }
1083    if id.is_empty() {
1084        return Value::Object(fields);
1085    }
1086    row_from_columns(table, &id, fields)
1087}
1088
1089// silence unused import in some cfgs
1090#[allow(dead_code)]
1091fn _decode(storage: FieldStorage, raw: Option<&str>, i: Option<i64>) -> Value {
1092    decode_sql_cell(storage, raw, i)
1093}