fix(rust) : lire les colonnes timestamp que la production déclare en timestamptz

Au démarrage, la configuration météo était illisible
(« Rust type Option<NaiveDateTime> (as SQL type TIMESTAMP) is not compatible
with SQL type TIMESTAMPTZ ») et /api/weather/current renvoyait 500 :
weather_settings.created_at est timestamptz en production alors que
shared/schema.ts et init.sql le déclarent timestamp.

Les types réels de la production ne figurent dans aucune migration du dépôt :
plutôt que de deviner colonne par colonne, chaque colonne timestamp des
structures générées est lue par crate::db::AnyTimestamp(Opt), qui accepte
timestamp et timestamptz (rendu en UTC). Le champ reste NaiveDateTime et la
sérialisation (crate::json::ts, « …Z ») ne change pas.

scripts/gen-rust-schema.ts émet le même attribut #[sqlx(try_from)] ;
schema.rs est modifié à l'identique (62 colonnes).

Non compilé : pas de chaîne Rust sur ce poste.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

scripts/gen-rust-schema.ts

@@ -178,6 +178,11 @@ async function main() {
       const attributes: string[] = [];
       const field = RUST_KEYWORDS.has(column.name) ? `${column.name}_` : column.name;
       if (field !== column.name) attributes.push(`#[sqlx(rename = "${column.name}")]`);
+      // timestamp dans shared/schema.ts, mais parfois timestamptz en production
+      // (tables créées hors des migrations du dépôt) : lu dans les deux cas
+      if (base === "NaiveDateTime") {
+        attributes.push(`#[sqlx(try_from = "${nullable ? "crate::db::AnyTimestampOpt" : "crate::db::AnyTimestamp"}")]`);
+      }
       const serde = [`rename = "${column.jsKey}"`];
       if (base === "NaiveDateTime") serde.push(`serialize_with = "${nullable ? "crate::json::ts_opt" : "crate::json::ts"}"`);
       if (base === "Decimal") {

server-rs/src/db.rs

@@ -4,9 +4,11 @@
 // Comme node-pg, chaque valeur part en texte et PostgreSQL la convertit vers le
 // type de la colonne ($1::date, $2::numeric…) : mêmes conversions, mêmes erreurs.
 
+use chrono::{DateTime, NaiveDateTime, Utc};
 use serde_json::Value;
-use sqlx::postgres::{PgArguments, PgRow};
-use sqlx::{Arguments, FromRow, PgPool};
+use sqlx::error::BoxDynError;
+use sqlx::postgres::{PgArguments, PgRow, PgTypeInfo, PgValueRef};
+use sqlx::{Arguments, Decode, FromRow, PgPool, Postgres, Type, ValueRef};
 
 use crate::zod::Parsed;
 
@@ -34,6 +36,85 @@ pub trait Table: for<'r> FromRow<'r, PgRow> + Send + Unpin {
     }
 }
 
+/// Colonne `timestamp` de shared/schema.ts, lue que la base réelle la déclare
+/// `timestamp` ou `timestamptz`. Certaines tables de production ont été créées en
+/// dehors des migrations du dépôt avec `timestamptz` (weather_settings.created_at,
+/// avoirs…), et sqlx refuse de lire un `timestamptz` en NaiveDateTime.
+///
+/// Un `timestamptz` est rendu en heure UTC : la même valeur que l'API sérialise
+/// ensuite avec crate::json::ts (« …Z »). schema.rs l'utilise par
+/// `#[sqlx(try_from = "crate::db::AnyTimestamp")]` : le champ reste NaiveDateTime.
+pub struct AnyTimestamp(pub NaiveDateTime);
+
+/// AnyTimestamp d'une colonne qui peut être NULL
+pub struct AnyTimestampOpt(pub Option<NaiveDateTime>);
+
+fn is_timestamptz(ty: &PgTypeInfo) -> bool {
+    <DateTime<Utc> as Type<Postgres>>::compatible(ty)
+}
+
+fn any_timestamp_compatible(ty: &PgTypeInfo) -> bool {
+    <NaiveDateTime as Type<Postgres>>::compatible(ty) || is_timestamptz(ty)
+}
+
+fn decode_any_timestamp(value: PgValueRef<'_>) -> Result<NaiveDateTime, BoxDynError> {
+    let timestamptz = is_timestamptz(&value.type_info());
+    if timestamptz {
+        Ok(<DateTime<Utc> as Decode<'_, Postgres>>::decode(value)?.naive_utc())
+    } else {
+        <NaiveDateTime as Decode<'_, Postgres>>::decode(value)
+    }
+}
+
+impl Type<Postgres> for AnyTimestamp {
+    fn type_info() -> PgTypeInfo {
+        <NaiveDateTime as Type<Postgres>>::type_info()
+    }
+
+    fn compatible(ty: &PgTypeInfo) -> bool {
+        any_timestamp_compatible(ty)
+    }
+}
+
+impl<'r> Decode<'r, Postgres> for AnyTimestamp {
+    fn decode(value: PgValueRef<'r>) -> Result<Self, BoxDynError> {
+        decode_any_timestamp(value).map(Self)
+    }
+}
+
+impl From<AnyTimestamp> for NaiveDateTime {
+    fn from(value: AnyTimestamp) -> Self {
+        value.0
+    }
+}
+
+impl Type<Postgres> for AnyTimestampOpt {
+    fn type_info() -> PgTypeInfo {
+        <NaiveDateTime as Type<Postgres>>::type_info()
+    }
+
+    fn compatible(ty: &PgTypeInfo) -> bool {
+        any_timestamp_compatible(ty)
+    }
+}
+
+impl<'r> Decode<'r, Postgres> for AnyTimestampOpt {
+    fn decode(value: PgValueRef<'r>) -> Result<Self, BoxDynError> {
+        // Row::try_get ne vérifie pas le type d'une valeur NULL et la passe telle
+        // quelle à decode (comme pour Option<T>)
+        if value.is_null() {
+            return Ok(Self(None));
+        }
+        decode_any_timestamp(value).map(|timestamp| Self(Some(timestamp)))
+    }
+}
+
+impl From<AnyTimestampOpt> for Option<NaiveDateTime> {
+    fn from(value: AnyTimestampOpt) -> Self {
+        value.0
+    }
+}
+
 /// Valeur JavaScript telle que node-pg l'envoie (prepareValue) : texte, ou NULL
 pub fn pg_text(value: &Value) -> Option<String> {
     match value {
@@ -263,6 +344,19 @@ mod tests {
         value.as_object().cloned().unwrap()
     }
 
+    #[test]
+    fn horodatage_lu_en_timestamp_comme_en_timestamptz() {
+        let timestamp = <NaiveDateTime as Type<Postgres>>::type_info();
+        let timestamptz = <DateTime<Utc> as Type<Postgres>>::type_info();
+        let date = <chrono::NaiveDate as Type<Postgres>>::type_info();
+        for ty in [&timestamp, &timestamptz] {
+            assert!(<AnyTimestamp as Type<Postgres>>::compatible(ty));
+            assert!(<AnyTimestampOpt as Type<Postgres>>::compatible(ty));
+        }
+        assert!(!<AnyTimestamp as Type<Postgres>>::compatible(&date));
+        assert!(!<AnyTimestampOpt as Type<Postgres>>::compatible(&date));
+    }
+
     #[test]
     fn valeurs_comme_node_pg() {
         // Valeurs de require('pg/lib/utils').prepareValue dans Node

server-rs/src/schema.rs

@@ -34,14 +34,18 @@ pub struct AvoirsRow {
     pub webhook_sent: Option<bool>,
     #[serde(rename = "nocodbVerified")]
     pub nocodb_verified: Option<bool>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "nocodbVerifiedAt", serialize_with = "crate::json::ts_opt")]
     pub nocodb_verified_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "processedAt", serialize_with = "crate::json::ts_opt")]
     pub processed_at: Option<NaiveDateTime>,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -173,8 +177,10 @@ pub struct ContactsRow {
     pub email: Option<String>,
     #[serde(rename = "notes")]
     pub notes: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -285,6 +291,7 @@ pub struct CustomerOrdersRow {
     pub is_promotional_price: Option<bool>,
     #[serde(rename = "customerNotified")]
     pub customer_notified: Option<bool>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "notifiedAt", serialize_with = "crate::json::ts_opt")]
     pub notified_at: Option<NaiveDateTime>,
     #[serde(rename = "notifiedComment")]
@@ -295,8 +302,10 @@ pub struct CustomerOrdersRow {
     pub group_id: i32,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -458,6 +467,7 @@ pub struct DashboardMessagesRow {
     pub store_id: Option<i32>,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
 }
@@ -531,6 +541,7 @@ pub struct DatabaseBackupsRow {
     pub description: Option<String>,
     #[serde(rename = "size")]
     pub size: Option<i64>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
     #[serde(rename = "createdBy")]
@@ -625,6 +636,7 @@ pub struct DeliveriesRow {
     pub group_id: i32,
     #[serde(rename = "scheduledDate")]
     pub scheduled_date: NaiveDate,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "deliveredDate", serialize_with = "crate::json::ts_opt")]
     pub delivered_date: Option<NaiveDateTime>,
     #[serde(rename = "quantity")]
@@ -648,22 +660,27 @@ pub struct DeliveriesRow {
         serialize_with = "crate::json::numeric_opt::<2, _>"
     )]
     pub invoice_amount_ttc: Option<Decimal>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "dueDate", serialize_with = "crate::json::ts_opt")]
     pub due_date: Option<NaiveDateTime>,
     #[serde(rename = "reconciled")]
     pub reconciled: Option<bool>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "validatedAt", serialize_with = "crate::json::ts_opt")]
     pub validated_at: Option<NaiveDateTime>,
     #[serde(rename = "controlValidated")]
     pub control_validated: Option<bool>,
     #[serde(rename = "controlValidatedBy")]
     pub control_validated_by: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "controlValidatedAt", serialize_with = "crate::json::ts_opt")]
     pub control_validated_at: Option<NaiveDateTime>,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -833,7 +850,7 @@ pub struct DlcProductsRow {
     #[serde(rename = "id")]
     pub id: i32,
     #[serde(rename = "name")]
-    pub name: String,
+    pub name: Option<String>,
     #[serde(rename = "productName")]
     pub product_name: Option<String>,
     #[serde(rename = "gencode")]
@@ -860,8 +877,10 @@ pub struct DlcProductsRow {
     pub stock_epuise: Option<bool>,
     #[serde(rename = "stockEpuiseBy")]
     pub stock_epuise_by: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "stockEpuiseAt", serialize_with = "crate::json::ts_opt")]
     pub stock_epuise_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "processedAt", serialize_with = "crate::json::ts_opt")]
     pub processed_at: Option<NaiveDateTime>,
     #[serde(rename = "processedBy")]
@@ -874,10 +893,13 @@ pub struct DlcProductsRow {
     pub created_by: String,
     #[serde(rename = "validatedBy")]
     pub validated_by: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "validatedAt", serialize_with = "crate::json::ts_opt")]
     pub validated_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1060,10 +1082,13 @@ pub struct ExternalApiKeysRow {
     pub key_hash: String,
     #[serde(rename = "createdBy")]
     pub created_by: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "lastUsedAt", serialize_with = "crate::json::ts_opt")]
     pub last_used_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "revokedAt", serialize_with = "crate::json::ts_opt")]
     pub revoked_at: Option<NaiveDateTime>,
 }
@@ -1182,8 +1207,10 @@ pub struct GroupsRow {
     pub smtp_sender_email: Option<String>,
     #[serde(rename = "smtpSenderName")]
     pub smtp_sender_name: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1384,6 +1411,7 @@ pub struct InvoiceVerificationCacheRow {
         serialize_with = "crate::json::numeric_opt::<2, _>"
     )]
     pub invoice_amount_ttc: Option<Decimal>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "dueDate", serialize_with = "crate::json::ts_opt")]
     pub due_date: Option<NaiveDateTime>,
     #[serde(rename = "exists")]
@@ -1398,10 +1426,13 @@ pub struct InvoiceVerificationCacheRow {
     pub api_call_time: Option<i32>,
     #[serde(rename = "isReconciled")]
     pub is_reconciled: Option<bool>,
+    #[sqlx(try_from = "crate::db::AnyTimestamp")]
     #[serde(rename = "expiresAt", serialize_with = "crate::json::ts")]
     pub expires_at: NaiveDateTime,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1542,8 +1573,10 @@ pub struct NocodbConfigRow {
     pub is_active: Option<bool>,
     #[serde(rename = "createdBy")]
     pub created_by: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1644,8 +1677,10 @@ pub struct OrdersRow {
     pub notes: Option<String>,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1748,8 +1783,10 @@ pub struct PublicitiesRow {
     pub year: i32,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1872,8 +1909,10 @@ pub struct ReconciliationCommentsRow {
     pub type_: String,
     #[serde(rename = "authorId")]
     pub author_id: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -1974,12 +2013,16 @@ pub struct SavTicketsRow {
     pub client_phone: Option<String>,
     #[serde(rename = "createdBy")]
     pub created_by: String,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "resolvedAt", serialize_with = "crate::json::ts_opt")]
     pub resolved_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "closedAt", serialize_with = "crate::json::ts_opt")]
     pub closed_at: Option<NaiveDateTime>,
 }
@@ -2123,6 +2166,7 @@ pub struct SessionsRow {
     pub sid: String,
     #[serde(rename = "sess")]
     pub sess: serde_json::Value,
+    #[sqlx(try_from = "crate::db::AnyTimestamp")]
     #[serde(rename = "expire", serialize_with = "crate::json::ts")]
     pub expire: NaiveDateTime,
 }
@@ -2187,6 +2231,7 @@ pub struct SupplierMailLogsRow {
     pub sent_by: String,
     #[serde(rename = "sentByName")]
     pub sent_by_name: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
 }
@@ -2303,8 +2348,10 @@ pub struct SuppliersRow {
     pub has_dlc: Option<bool>,
     #[serde(rename = "paymentMethod")]
     pub payment_method: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
     #[serde(rename = "automaticReconciliation")]
@@ -2409,6 +2456,7 @@ pub struct TasksRow {
     pub title: String,
     #[serde(rename = "description")]
     pub description: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "startDate", serialize_with = "crate::json::ts_opt")]
     pub start_date: Option<NaiveDateTime>,
     #[serde(rename = "dueDate")]
@@ -2423,12 +2471,15 @@ pub struct TasksRow {
     pub created_by: String,
     #[serde(rename = "groupId")]
     pub group_id: i32,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "completedAt", serialize_with = "crate::json::ts_opt")]
     pub completed_at: Option<NaiveDateTime>,
     #[serde(rename = "completedBy")]
     pub completed_by: Option<String>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -2539,6 +2590,7 @@ pub struct UserGroupsRow {
     pub user_id: String,
     #[serde(rename = "groupId")]
     pub group_id: i32,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
 }
@@ -2599,8 +2651,10 @@ pub struct UsersRow {
     pub role: String,
     #[serde(rename = "passwordChanged")]
     pub password_changed: Option<bool>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -2701,8 +2755,10 @@ pub struct UtilitiesRow {
     pub sales_analysis_url: Option<String>,
     #[serde(rename = "automaticBackupsEnabled")]
     pub automatic_backups_enabled: Option<bool>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -2775,8 +2831,10 @@ pub struct WeatherDataRow {
     pub conditions: String,
     #[serde(rename = "isCurrentYear")]
     pub is_current_year: bool,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -2867,8 +2925,10 @@ pub struct WeatherSettingsRow {
     pub location: String,
     #[serde(rename = "isActive")]
     pub is_active: bool,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }
@@ -2938,8 +2998,10 @@ pub struct WebhookBapConfigRow {
     pub description: Option<String>,
     #[serde(rename = "isActive")]
     pub is_active: bool,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "createdAt", serialize_with = "crate::json::ts_opt")]
     pub created_at: Option<NaiveDateTime>,
+    #[sqlx(try_from = "crate::db::AnyTimestampOpt")]
     #[serde(rename = "updatedAt", serialize_with = "crate::json::ts_opt")]
     pub updated_at: Option<NaiveDateTime>,
 }

server-rs/src/startup.rs

@@ -115,6 +115,9 @@ const SCHEMA_STATEMENTS: &[(&str, &str)] = &[
 
 /// Applique les migrations de schéma, puis chiffre les secrets encore en clair
 pub async fn run_migrations(db: &PgPool, encryption_key: &str) {
+    if let Err(error) = set_aside_legacy_sessions_table(db).await {
+        tracing::error!(%error, "vérification de la table sessions en échec");
+    }
     for (label, statement) in SCHEMA_STATEMENTS {
         if let Err(error) = sqlx::query(statement).execute(db).await {
             tracing::error!(%error, "migration « {label} » en échec");
@@ -128,6 +131,40 @@ pub async fn run_migrations(db: &PgPool, encryption_key: &str) {
     tracing::info!("migrations de démarrage terminées");
 }
 
+/// Une base peut porter une table « sessions » d'une autre forme, jamais utilisée
+/// (l'ancien serveur gardait les sessions en mémoire) : CREATE TABLE IF NOT EXISTS
+/// la laisserait en place et toute connexion échouerait (« column "sid" does not
+/// exist »). Elle est renommée, sans rien supprimer, et la bonne table est créée
+/// ensuite par SCHEMA_STATEMENTS. Les sessions ne sont que des connexions ouvertes.
+async fn set_aside_legacy_sessions_table(db: &PgPool) -> Result<(), sqlx::Error> {
+    let (exists, expected_columns): (bool, i64) = sqlx::query_as(
+        "SELECT to_regclass('sessions') IS NOT NULL,
+                (SELECT count(*) FROM information_schema.columns
+                  WHERE table_schema = current_schema() AND table_name = 'sessions'
+                    AND column_name IN ('sid', 'sess', 'expire'))",
+    )
+    .fetch_one(db)
+    .await?;
+    if !exists || expected_columns == 3 {
+        return Ok(());
+    }
+    let suffix = chrono::Utc::now().format("%Y%m%d%H%M%S");
+    let legacy = format!("sessions_ancienne_{suffix}");
+    let mut transaction = db.begin().await?;
+    sqlx::query(&format!("ALTER TABLE sessions RENAME TO {legacy}"))
+        .execute(&mut *transaction)
+        .await?;
+    // Un index de ce nom sur l'ancienne table empêcherait de le créer sur la nouvelle
+    sqlx::query(&format!(
+        "ALTER INDEX IF EXISTS \"IDX_session_expire\" RENAME TO \"IDX_session_expire_ancienne_{suffix}\""
+    ))
+    .execute(&mut *transaction)
+    .await?;
+    transaction.commit().await?;
+    tracing::warn!(table = %legacy, "table sessions d'une autre forme mise de côté");
+    Ok(())
+}
+
 /// Mots de passe SMTP des magasins et jetons NocoDB encore en clair → enc:v1
 async fn encrypt_plain_secrets(db: &PgPool, key: &str) -> Result<usize, sqlx::Error> {
     let mut count = 0;