Skip to main content

agentstategraph_storage/
postgres.rs

1//! PostgreSQL storage backend — multi-tenant, connection-pooled.
2//!
3//! Uses tokio-postgres + deadpool for connection pooling.
4//! Each tenant's data is isolated via a `tenant_id` column on every table.
5//!
6//! Usage:
7//!   let storage = PostgresStorage::connect("postgres://localhost/agentstategraph").await?;
8//!   let storage = PostgresStorage::connect_tenant("postgres://...", "tenant-123").await?;
9//!   // From sync code, with or without an async runtime:
10//!   let storage = PostgresStorage::connect_tenant_blocking("postgres://...", "tenant-123", 8)?;
11//!   // Another tenant on the same pool, for a host serving many:
12//!   let other = storage.for_tenant("tenant-456")?;
13//!
14//! The storage traits are synchronous. Every connection is driven by one
15//! process-wide runtime owned by this module, so callers can use the storage
16//! from plain threads, from a multi-threaded tokio runtime, or from a
17//! single-threaded one, and the runtime that connected needn't outlive it.
18//!
19//! TLS (the `postgres-tls` feature) is set in the connection string, the way
20//! libpq does it:
21//!   postgres://user@db.example.com/app?sslmode=verify-full&sslrootcert=/etc/ca.pem
22//! - `sslmode=require`, `verify-ca` and `verify-full` all mean verified TLS: the
23//!   server's certificate must chain to Mozilla's roots or to a certificate in
24//!   `sslrootcert` (or `PGSSLROOTCERT`), and must match the host name. There is
25//!   no unverified TLS. `verify-ca` is stricter here than in libpq, because the
26//!   host name is checked too.
27//! - `sslmode=prefer` uses TLS when the server offers it, and still verifies it.
28//! - No `sslmode`, or `sslmode=disable`, is plaintext, as before. Use
29//!   `sslmode=require` or `verify-full` for any host that isn't local.
30//! Without the `postgres-tls` feature, asking for TLS is an error that names
31//! the feature, never a silent plaintext connection.
32//!
33//! As of 0.6.75-beta.1 this backend persists epochs and sessions too,
34//! with the same schema shape as SQLite (JSON-as-TEXT). Multi-tenant
35//! isolation continues via a `tenant_id` column on every table.
36//!
37//! History walks, the project-history rollups and value search work as they
38//! do on SQLite. Search uses a trigram index when the `pg_trgm` extension can
39//! be installed, and scans the ref's rows when it can't. Requires PostgreSQL
40//! 13 or later.
41//!
42//! GC sweeps one tenant at a time and never makes writers wait: a sweep
43//! spares objects written within a grace period (see
44//! [`PostgresStorage::with_gc_grace`]) instead of locking writers out.
45//!
46//! Postgres row-level security can back up every query's tenant filter. Each
47//! table holding tenants' rows has a policy that lets a statement see and
48//! write only the rows of its connection's tenant, and every connection the
49//! storage checks out sets that tenant first ([`TENANT_SETTING`]). The
50//! policies do nothing until [`PostgresStorage::set_row_security`] switches
51//! them on, per database. A superuser or a role with `BYPASSRLS` skips them.
52
53use deadpool_postgres::{
54    Config, ManagerConfig, PoolConfig, RecyclingMethod, Runtime as PoolRuntime,
55};
56// The pool, its clients and their transactions record each query as a
57// database span and `db.client.operation.duration`, and the pool its
58// `db.client.connection.*` metrics.
59use oxtel_sql::postgres::{GenericClient, Pool};
60
61/// A pooled client, instrumented.
62pub type PgClient = oxtel_sql::postgres::Client<deadpool_postgres::Object>;
63/// A transaction on one, instrumented.
64type PgTransaction<'a> = oxtel_sql::postgres::Transaction<'a>;
65use std::sync::OnceLock;
66use tokio::runtime::RuntimeFlavor;
67#[cfg(not(feature = "postgres-tls"))]
68use tokio_postgres::NoTls;
69
70/// Default Postgres pool cap (v2-M1). Overridable via constructor or
71/// the binary's `--pg-pool-size` flag.
72pub const DEFAULT_POOL_SIZE: usize = 32;
73
74use agentstategraph_core::{
75    Commit, Epoch, EpochScope, EpochStatus, IntentCategory, Namespace, Node, Object, ObjectId,
76    Session, SessionStatus, TAG_GIT_REVISION, TAG_PIN_STATE,
77};
78use chrono::{DateTime, Utc};
79
80use crate::traits::{
81    CommitStore, CommitTrace, CommitWalk, DEFAULT_GC_GRACE, EpochStore, GcReachability, GcSweep,
82    HISTORY_NAMESPACE_UNKNOWN, HistoryMilestoneRow, HistoryRollupRow, ObjectStore, RefStore,
83    SessionStore, StorageError, StoreShape, StoreTotals, TableBytes, TaintStore,
84};
85
86/// TLS settings read from a connection string, and the string tokio-postgres
87/// is given in its place: it knows only `sslmode=disable|prefer|require` and
88/// rejects `sslrootcert`, so `verify-ca`/`verify-full` become `require` (TLS
89/// here is always verified) and `sslrootcert` is taken out. A string with no
90/// `sslmode` gets `sslmode=disable`, so it stays plaintext as it always was;
91/// tokio-postgres alone would default to `prefer`.
92#[derive(Debug, PartialEq)]
93struct ConnectionTls {
94    url: String,
95    requires_tls: bool,
96    root_cert: Option<std::path::PathBuf>,
97}
98
99impl ConnectionTls {
100    /// `env_root_cert` is `PGSSLROOTCERT`, used when the string names none.
101    fn parse(database_url: &str, env_root_cert: Option<String>) -> Result<Self, StorageError> {
102        let is_url =
103            database_url.starts_with("postgres://") || database_url.starts_with("postgresql://");
104        // URL form keeps options in the query string; key/value form is
105        // space-separated `key=value` pairs (quoted values aren't supported
106        // for these two keys).
107        let (head, options, sep) = if is_url {
108            match database_url.split_once('?') {
109                Some((head, query)) => (head, query, '&'),
110                None => (database_url, "", '&'),
111            }
112        } else {
113            ("", database_url, ' ')
114        };
115
116        let mut kept: Vec<String> = Vec::new();
117        let mut mode: Option<String> = None;
118        let mut root_cert = None;
119        for part in options.split(sep).filter(|p| !p.is_empty()) {
120            let (key, value) = part.split_once('=').unwrap_or((part, ""));
121            match key {
122                "sslmode" => {
123                    let mapped = match value {
124                        "verify-ca" | "verify-full" => "require",
125                        other => other,
126                    };
127                    mode = Some(value.to_string());
128                    kept.push(format!("sslmode={mapped}"));
129                }
130                "sslrootcert" => {
131                    let path = if is_url {
132                        percent_decode(value)?
133                    } else {
134                        value.to_string()
135                    };
136                    root_cert = Some(std::path::PathBuf::from(path));
137                }
138                _ => kept.push(part.to_string()),
139            }
140        }
141        if mode.is_none() {
142            kept.push("sslmode=disable".to_string());
143        }
144        let options = kept.join(&sep.to_string());
145        let url = if is_url {
146            format!("{head}?{options}")
147        } else {
148            options
149        };
150        Ok(Self {
151            url,
152            requires_tls: matches!(
153                mode.as_deref(),
154                Some("require") | Some("verify-ca") | Some("verify-full")
155            ),
156            root_cert: root_cert.or_else(|| env_root_cert.map(std::path::PathBuf::from)),
157        })
158    }
159}
160
161/// Decode `%XX` escapes in a URL query value (a file path may contain them).
162fn percent_decode(value: &str) -> Result<String, StorageError> {
163    let bytes = value.as_bytes();
164    let mut out = Vec::with_capacity(bytes.len());
165    let mut i = 0;
166    while i < bytes.len() {
167        if bytes[i] == b'%' {
168            let hex = value
169                .get(i + 1..i + 3)
170                .and_then(|h| u8::from_str_radix(h, 16).ok())
171                .ok_or_else(|| {
172                    StorageError::Backend(format!(
173                        "bad percent-escape in connection string: {value}"
174                    ))
175                })?;
176            out.push(hex);
177            i += 3;
178        } else {
179            out.push(bytes[i]);
180            i += 1;
181        }
182    }
183    String::from_utf8(out)
184        .map_err(|_| StorageError::Backend(format!("connection string value isn't UTF-8: {value}")))
185}
186
187/// rustls for tokio-postgres: always-verified TLS against Mozilla's roots
188/// plus any `sslrootcert`. Channel binding (SCRAM-SHA-256-PLUS) isn't
189/// offered; ordinary SCRAM authentication works over the TLS connection.
190#[cfg(feature = "postgres-tls")]
191mod tls {
192    use std::future::Future;
193    use std::io;
194    use std::path::Path;
195    use std::pin::Pin;
196    use std::sync::Arc;
197    use std::task::{Context, Poll};
198
199    use rustls::pki_types::pem::PemObject;
200    use rustls::pki_types::{CertificateDer, ServerName};
201    use rustls::{ClientConfig, RootCertStore};
202    use tokio::io::{AsyncRead, AsyncWrite, ReadBuf};
203    use tokio_postgres::tls::{ChannelBinding, MakeTlsConnect, TlsConnect, TlsStream};
204
205    use crate::traits::StorageError;
206
207    pub(super) fn connector(root_cert: Option<&Path>) -> Result<MakeRustlsConnect, StorageError> {
208        let mut roots = RootCertStore {
209            roots: webpki_roots::TLS_SERVER_ROOTS.to_vec(),
210        };
211        if let Some(path) = root_cert {
212            let bad = |e: &dyn std::fmt::Display| {
213                StorageError::Backend(format!("sslrootcert {}: {e}", path.display()))
214            };
215            let certs = CertificateDer::pem_file_iter(path)
216                .map_err(|e| bad(&e))?
217                .collect::<Result<Vec<_>, _>>()
218                .map_err(|e| bad(&e))?;
219            if certs.is_empty() {
220                return Err(bad(&"no PEM certificates in the file"));
221            }
222            for cert in certs {
223                roots.add(cert).map_err(|e| bad(&e))?;
224            }
225        }
226        let config =
227            ClientConfig::builder_with_provider(Arc::new(rustls::crypto::ring::default_provider()))
228                .with_safe_default_protocol_versions()
229                .map_err(|e| StorageError::Backend(format!("postgres tls: {e}")))?
230                .with_root_certificates(roots)
231                .with_no_client_auth();
232        Ok(MakeRustlsConnect(Arc::new(config)))
233    }
234
235    #[derive(Clone)]
236    pub(super) struct MakeRustlsConnect(Arc<ClientConfig>);
237
238    impl<S> MakeTlsConnect<S> for MakeRustlsConnect
239    where
240        S: AsyncRead + AsyncWrite + Unpin + Send + 'static,
241    {
242        type Stream = RustlsStream<S>;
243        type TlsConnect = RustlsConnect;
244        type Error = io::Error;
245
246        fn make_tls_connect(&mut self, host: &str) -> io::Result<RustlsConnect> {
247            let name = ServerName::try_from(host.to_string())
248                .map_err(|e| io::Error::new(io::ErrorKind::InvalidInput, e))?;
249            Ok(RustlsConnect {
250                config: Arc::clone(&self.0),
251                name,
252            })
253        }
254    }
255
256    pub(super) struct RustlsConnect {
257        config: Arc<ClientConfig>,
258        name: ServerName<'static>,
259    }
260
261    impl<S> TlsConnect<S> for RustlsConnect
262    where
263        S: AsyncRead + AsyncWrite + Unpin + Send + 'static,
264    {
265        type Stream = RustlsStream<S>;
266        type Error = io::Error;
267        type Future = Pin<Box<dyn Future<Output = io::Result<RustlsStream<S>>> + Send>>;
268
269        fn connect(self, stream: S) -> Self::Future {
270            Box::pin(async move {
271                tokio_rustls::TlsConnector::from(self.config)
272                    .connect(self.name, stream)
273                    .await
274                    .map(RustlsStream)
275            })
276        }
277    }
278
279    pub(super) struct RustlsStream<S>(tokio_rustls::client::TlsStream<S>);
280
281    impl<S: AsyncRead + AsyncWrite + Unpin> TlsStream for RustlsStream<S> {
282        fn channel_binding(&self) -> ChannelBinding {
283            ChannelBinding::none()
284        }
285    }
286
287    impl<S: AsyncRead + AsyncWrite + Unpin> AsyncRead for RustlsStream<S> {
288        fn poll_read(
289            self: Pin<&mut Self>,
290            cx: &mut Context<'_>,
291            buf: &mut ReadBuf<'_>,
292        ) -> Poll<io::Result<()>> {
293            Pin::new(&mut self.get_mut().0).poll_read(cx, buf)
294        }
295    }
296
297    impl<S: AsyncRead + AsyncWrite + Unpin> AsyncWrite for RustlsStream<S> {
298        fn poll_write(
299            self: Pin<&mut Self>,
300            cx: &mut Context<'_>,
301            buf: &[u8],
302        ) -> Poll<io::Result<usize>> {
303            Pin::new(&mut self.get_mut().0).poll_write(cx, buf)
304        }
305
306        fn poll_flush(self: Pin<&mut Self>, cx: &mut Context<'_>) -> Poll<io::Result<()>> {
307            Pin::new(&mut self.get_mut().0).poll_flush(cx)
308        }
309
310        fn poll_shutdown(self: Pin<&mut Self>, cx: &mut Context<'_>) -> Poll<io::Result<()>> {
311            Pin::new(&mut self.get_mut().0).poll_shutdown(cx)
312        }
313    }
314}
315
316/// The schema this build expects. Bump it whenever `SCHEMA_DDL` changes:
317/// databases stamped with an older version re-run the (idempotent) DDL once.
318///
319/// 2: history walks in one query, the history rollup tables, and the leaf
320/// search index (Plan D t-008).
321/// 3: GC: object write times and speculation pins (Plan D t-009).
322/// 4: the trace each commit was made in (Plan H t-009).
323/// 5: the tenant policy on every tenant table, for row-level security (Plan F
324/// t-003). It's added after `SCHEMA_DDL`, from [`TENANT_TABLES`].
325/// 6: `asg_schema.compatible_from` (Plan F t-009).
326/// 7: `objects.exact` and `commits.exact`, which hold a row's JSON exactly
327/// when it has a U+0000 that JSONB can't (see [`row_json`]).
328///
329/// Several builds can share one database during a rolling deploy, so
330/// migrations expand, then contract. An expand migration only adds (a table,
331/// a nullable or defaulted column, an index), so an older build still works
332/// on the result, and it leaves [`SCHEMA_COMPATIBLE_FROM`] alone. A contract
333/// migration removes or changes what an older build uses, and ships only once
334/// no build that needs it is still running; it raises
335/// [`SCHEMA_COMPATIBLE_FROM`] to its own version.
336const SCHEMA_VERSION: i32 = 7;
337
338/// The oldest schema version a build must understand to use a database this
339/// build has set up, stamped as `asg_schema.compatible_from`. A build refuses
340/// a database whose stamp is above its own [`SCHEMA_VERSION`], and uses one
341/// stamped by a newer build otherwise, without migrating it. Version 7's
342/// `exact` columns are what an older build can't use: it would read a row
343/// holding U+0000 from `data`, with U+FFFD in its place, and hand back
344/// content that doesn't match its id. (Before that, version 5's tenant
345/// policy: with row-level security on, a build that doesn't set the tenant on
346/// its connections sees no rows.)
347const SCHEMA_COMPATIBLE_FROM: i32 = 7;
348
349/// Every table, column and index, each `IF NOT EXISTS` so it's safe to re-run
350/// over any earlier schema, plus the version stamp table.
351const SCHEMA_DDL: &str = "
352                CREATE TABLE IF NOT EXISTS objects (
353                    tenant_id TEXT NOT NULL,
354                    id        BYTEA NOT NULL,
355                    data      JSONB NOT NULL,
356                    PRIMARY KEY (tenant_id, id)
357                );
358
359                CREATE TABLE IF NOT EXISTS commits (
360                    tenant_id TEXT NOT NULL,
361                    id        BYTEA NOT NULL,
362                    data      JSONB NOT NULL,
363                    timestamp TIMESTAMPTZ NOT NULL,
364                    PRIMARY KEY (tenant_id, id)
365                );
366
367                CREATE TABLE IF NOT EXISTS namespaces (
368                    tenant_id  TEXT NOT NULL,
369                    name       TEXT NOT NULL,
370                    created_at TEXT NOT NULL,
371                    PRIMARY KEY (tenant_id, name)
372                );
373
374                CREATE TABLE IF NOT EXISTS refs (
375                    tenant_id TEXT NOT NULL,
376                    namespace TEXT NOT NULL,
377                    name      TEXT NOT NULL,
378                    target    BYTEA NOT NULL,
379                    PRIMARY KEY (tenant_id, namespace, name)
380                );
381
382                CREATE INDEX IF NOT EXISTS idx_commits_tenant_ts
383                    ON commits(tenant_id, timestamp DESC);
384
385                CREATE TABLE IF NOT EXISTS epochs (
386                    tenant_id       TEXT NOT NULL,
387                    id              TEXT NOT NULL,
388                    description     TEXT NOT NULL DEFAULT '',
389                    status          TEXT NOT NULL DEFAULT 'Active',
390                    created_at      TEXT NOT NULL,
391                    sealed_at       TEXT,
392                    summary         TEXT,
393                    root_intents    TEXT NOT NULL DEFAULT '[]',
394                    agents          TEXT NOT NULL DEFAULT '[]',
395                    tags            TEXT NOT NULL DEFAULT '[]',
396                    commit_count    INTEGER NOT NULL DEFAULT 0,
397                    sealed_commits  TEXT NOT NULL DEFAULT '[]',
398                    PRIMARY KEY (tenant_id, id)
399                );
400
401                CREATE TABLE IF NOT EXISTS sessions (
402                    tenant_id       TEXT NOT NULL,
403                    id              TEXT NOT NULL,
404                    agent_id        TEXT NOT NULL,
405                    parent_id       TEXT,
406                    scope_path      TEXT,
407                    scope_branch    TEXT,
408                    scope_namespace TEXT,
409                    status          TEXT NOT NULL DEFAULT 'Active',
410                    created_at      TEXT NOT NULL,
411                    ended_at        TEXT,
412                    metadata        TEXT NOT NULL DEFAULT '{}',
413                    commit_count    INTEGER NOT NULL DEFAULT 0,
414                    PRIMARY KEY (tenant_id, id)
415                );
416
417                CREATE TABLE IF NOT EXISTS taints (
418                    tenant_id       TEXT NOT NULL,
419                    id              TEXT NOT NULL,
420                    path            TEXT NOT NULL,
421                    name            TEXT NOT NULL,
422                    kind            TEXT NOT NULL,
423                    effect          TEXT NOT NULL,
424                    severity        TEXT NOT NULL DEFAULT 'medium',
425                    reason          TEXT NOT NULL,
426                    agent_id        TEXT NOT NULL,
427                    commit_id       TEXT NOT NULL DEFAULT '',
428                    created_at      TEXT NOT NULL,
429                    expires_at      TEXT,
430                    resolved_at     TEXT,
431                    resolved_by     TEXT,
432                    resolved_reason TEXT,
433                    resolved_proof  TEXT,
434                    propagate       BOOLEAN NOT NULL DEFAULT true,
435                    metadata        TEXT NOT NULL DEFAULT '{}',
436                    PRIMARY KEY (tenant_id, id)
437                );
438
439                CREATE INDEX IF NOT EXISTS idx_epochs_tenant_status
440                    ON epochs(tenant_id, status);
441                CREATE INDEX IF NOT EXISTS idx_sessions_tenant_agent
442                    ON sessions(tenant_id, agent_id);
443                CREATE TABLE IF NOT EXISTS reminders (
444                    tenant_id       TEXT NOT NULL,
445                    id              TEXT NOT NULL,
446                    title           TEXT NOT NULL,
447                    instructions    TEXT NOT NULL,
448                    commands        TEXT NOT NULL DEFAULT '[]',
449                    refs            TEXT NOT NULL DEFAULT '[]',
450                    priority        INTEGER NOT NULL DEFAULT 3,
451                    due_at          TEXT NOT NULL,
452                    schedule        TEXT,
453                    autonomous      BOOLEAN NOT NULL DEFAULT true,
454                    created_by      TEXT NOT NULL,
455                    created_at      TEXT NOT NULL,
456                    status          TEXT NOT NULL DEFAULT 'pending',
457                    snoozed_until   TEXT,
458                    executions      TEXT NOT NULL DEFAULT '[]',
459                    tags            TEXT NOT NULL DEFAULT '[]',
460                    PRIMARY KEY (tenant_id, id)
461                );
462
463                CREATE INDEX IF NOT EXISTS idx_taints_tenant_path
464                    ON taints(tenant_id, path);
465                CREATE INDEX IF NOT EXISTS idx_taints_tenant_kind
466                    ON taints(tenant_id, kind);
467                CREATE UNIQUE INDEX IF NOT EXISTS idx_taints_unique_active
468                    ON taints(tenant_id, path, name, kind) WHERE resolved_at IS NULL;
469                CREATE INDEX IF NOT EXISTS idx_reminders_tenant_status
470                    ON reminders(tenant_id, status);
471                CREATE INDEX IF NOT EXISTS idx_reminders_tenant_due
472                    ON reminders(tenant_id, due_at);
473                CREATE INDEX IF NOT EXISTS idx_reminders_tenant_priority
474                    ON reminders(tenant_id, priority);
475                CREATE INDEX IF NOT EXISTS idx_reminders_tenant_created_by
476                    ON reminders(tenant_id, created_by);
477
478                ALTER TABLE commits ADD COLUMN IF NOT EXISTS epoch_id TEXT;
479                ALTER TABLE commits ADD COLUMN IF NOT EXISTS session_id TEXT;
480                ALTER TABLE sessions ADD COLUMN IF NOT EXISTS scope_namespace TEXT;
481
482                -- Epoch fidelity and scoping, as in the SQLite backend: the
483                -- seal digest, the owning namespace, and what the seal covers.
484                -- Nullable or defaulted, so existing rows read back as before
485                -- (global, scope = all).
486                ALTER TABLE epochs ADD COLUMN IF NOT EXISTS seal_hash BYTEA;
487                ALTER TABLE epochs ADD COLUMN IF NOT EXISTS namespace TEXT;
488                ALTER TABLE epochs ADD COLUMN IF NOT EXISTS scope TEXT NOT NULL DEFAULT 'all';
489                CREATE INDEX IF NOT EXISTS idx_epochs_tenant_namespace
490                    ON epochs(tenant_id, namespace);
491
492                CREATE INDEX IF NOT EXISTS idx_commits_tenant_epoch
493                    ON commits(tenant_id, epoch_id);
494                CREATE INDEX IF NOT EXISTS idx_commits_tenant_session
495                    ON commits(tenant_id, session_id);
496
497                -- History walks (version 2). A walk follows parent ids from
498                -- the commit JSON to the commits holding them, in one
499                -- recursive query; this index makes each step a lookup.
500                -- Reading the JSON rather than a separate edges table means a
501                -- commit written by an older binary is walkable too.
502                CREATE INDEX IF NOT EXISTS idx_commits_data_id
503                    ON commits USING hash ((data -> 'id'));
504
505                -- The id of the transaction that wrote each commit (version
506                -- 2), which orders commits for the history extractor. Rows
507                -- from before this column read 0, so they come first.
508                ALTER TABLE commits ADD COLUMN IF NOT EXISTS xid BIGINT NOT NULL DEFAULT 0;
509                ALTER TABLE commits
510                    ALTER COLUMN xid SET DEFAULT (pg_current_xact_id()::text::bigint);
511                CREATE INDEX IF NOT EXISTS idx_commits_tenant_xid
512                    ON commits(tenant_id, xid, id);
513
514                -- Distilled project history (version 2), as in the SQLite
515                -- backend. Derived and rebuildable, never authoritative. Text
516                -- columns compare bytewise (COLLATE \"C\"), as SQLite's do, so
517                -- RFC 3339 timestamps order correctly and output order
518                -- matches SQLite's.
519                CREATE TABLE IF NOT EXISTS asg_history_cursor (
520                    tenant_id TEXT PRIMARY KEY,
521                    xid       BIGINT NOT NULL,
522                    commit_id BYTEA NOT NULL,
523                    folded    BIGINT NOT NULL
524                );
525                CREATE TABLE IF NOT EXISTS asg_history_commit_rollup (
526                    tenant_id       TEXT NOT NULL,
527                    day             TEXT COLLATE \"C\" NOT NULL,
528                    namespace       TEXT COLLATE \"C\" NOT NULL,
529                    agent_id        TEXT COLLATE \"C\" NOT NULL,
530                    intent_category TEXT COLLATE \"C\" NOT NULL,
531                    commit_count    BIGINT NOT NULL DEFAULT 0,
532                    first_ts        TEXT COLLATE \"C\" NOT NULL,
533                    last_ts         TEXT COLLATE \"C\" NOT NULL,
534                    PRIMARY KEY (tenant_id, day, namespace, agent_id, intent_category)
535                );
536                CREATE TABLE IF NOT EXISTS asg_history_milestone (
537                    tenant_id   TEXT NOT NULL,
538                    commit_id   BYTEA NOT NULL,
539                    kind        TEXT NOT NULL,
540                    timestamp   TEXT COLLATE \"C\" NOT NULL,
541                    day         TEXT NOT NULL,
542                    namespace   TEXT NOT NULL,
543                    agent_id    TEXT NOT NULL,
544                    description TEXT NOT NULL,
545                    state_root  BYTEA,
546                    git_sha     TEXT,
547                    PRIMARY KEY (tenant_id, commit_id, kind)
548                );
549                CREATE INDEX IF NOT EXISTS idx_asg_history_milestone_ts
550                    ON asg_history_milestone(tenant_id, timestamp);
551
552                -- Leaf-value search index (version 2), as in the SQLite
553                -- backend: the string leaves of a (namespace, ref), kept up to
554                -- date as the ref moves. A path holds one value. `root` is the
555                -- state root the set reflects (NULL when unknown), so a diff
556                -- applies only to the tree it was computed from.
557                CREATE TABLE IF NOT EXISTS leaf_rows (
558                    tenant_id TEXT NOT NULL,
559                    namespace TEXT NOT NULL,
560                    ref       TEXT NOT NULL,
561                    path      TEXT NOT NULL,
562                    value     TEXT NOT NULL,
563                    PRIMARY KEY (tenant_id, namespace, ref, path)
564                );
565                CREATE TABLE IF NOT EXISTS leaf_index_refs (
566                    tenant_id TEXT NOT NULL,
567                    namespace TEXT NOT NULL,
568                    ref       TEXT NOT NULL,
569                    root      BYTEA,
570                    PRIMARY KEY (tenant_id, namespace, ref)
571                );
572                -- Substring search uses a trigram index when pg_trgm can be
573                -- installed (it is a trusted extension, so a role with CREATE
574                -- on the database can). Without it, search still works by
575                -- scanning the ref's rows.
576                DO $$
577                BEGIN
578                    CREATE EXTENSION IF NOT EXISTS pg_trgm;
579                    CREATE INDEX IF NOT EXISTS idx_leaf_rows_value_trgm
580                        ON leaf_rows USING gin (value gin_trgm_ops);
581                EXCEPTION WHEN OTHERS THEN
582                    RAISE NOTICE 'leaf search runs without a trigram index: %', SQLERRM;
583                END
584                $$;
585
586                -- GC (version 3). When each object was last written: a sweep
587                -- spares unreachable objects written within its grace period,
588                -- which is what keeps a write in flight safe (it stores its
589                -- objects before committing them). Rows from before read
590                -- -infinity, so they can go as soon as nothing reaches them.
591                ALTER TABLE objects
592                    ADD COLUMN IF NOT EXISTS written_at TIMESTAMPTZ NOT NULL DEFAULT '-infinity';
593                ALTER TABLE objects ALTER COLUMN written_at SET DEFAULT now();
594                -- Roots of uncommitted work (open speculations) that a sweep
595                -- must keep, from any process. See gc_pin_root.
596                CREATE TABLE IF NOT EXISTS gc_pending_roots (
597                    tenant_id  TEXT NOT NULL,
598                    key        TEXT NOT NULL,
599                    root       BYTEA NOT NULL,
600                    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
601                    PRIMARY KEY (tenant_id, key)
602                );
603
604                -- The trace each commit was made in (version 4): beside the
605                -- commit, not in it, so commit ids don't depend on telemetry.
606                CREATE TABLE IF NOT EXISTS commit_traces (
607                    tenant_id TEXT NOT NULL,
608                    commit_id BYTEA NOT NULL,
609                    trace_id  TEXT NOT NULL,
610                    span_id   TEXT NOT NULL,
611                    PRIMARY KEY (tenant_id, commit_id)
612                );
613
614                CREATE TABLE IF NOT EXISTS asg_schema (
615                    component TEXT PRIMARY KEY,
616                    version   INTEGER NOT NULL
617                );
618                -- The oldest schema version a build must understand to use
619                -- this database (version 6). See SCHEMA_COMPATIBLE_FROM.
620                ALTER TABLE asg_schema
621                    ADD COLUMN IF NOT EXISTS compatible_from INTEGER NOT NULL DEFAULT 1;
622
623                -- A row's exact JSON, when it has a U+0000, which JSONB can't
624                -- hold (version 7). NULL otherwise. See row_json.
625                ALTER TABLE objects ADD COLUMN IF NOT EXISTS exact BYTEA;
626                ALTER TABLE commits ADD COLUMN IF NOT EXISTS exact BYTEA;
627";
628
629/// The session setting that names the tenant a connection works for. Every
630/// connection the storage checks out has it set to the storage's tenant
631/// first, as does every connection a [`TenantPool`] hands out. With
632/// row-level security on (see [`PostgresStorage::set_row_security`]), each
633/// tenant table's policy compares a row's `tenant_id` with it, so a statement
634/// sees and writes only that tenant's rows, and a connection with no tenant
635/// set sees none.
636pub const TENANT_SETTING: &str = "asg.tenant";
637
638/// The name of the row-level security policy on each tenant table.
639/// [`PostgresStorage::set_row_security`] switches row-level security for
640/// every table in the schema that has it, a host's own included (see
641/// [`add_tenant_policy`]).
642pub const TENANT_POLICY: &str = "asg_tenant";
643
644/// AgentStateGraph's tables that hold tenants' rows. Each has a `tenant_id`
645/// column and the tenant policy. (`asg_schema` holds no tenant's rows.)
646pub const TENANT_TABLES: &[&str] = &[
647    "objects",
648    "commits",
649    "commit_traces",
650    "namespaces",
651    "refs",
652    "epochs",
653    "sessions",
654    "taints",
655    "reminders",
656    "leaf_rows",
657    "leaf_index_refs",
658    "gc_pending_roots",
659    "asg_history_cursor",
660    "asg_history_commit_rollup",
661    "asg_history_milestone",
662];
663
664/// Add the tenant policy ([`TENANT_POLICY`]) to `table` in the current schema,
665/// unless it has it: a statement may see or write a row only when the row's
666/// `tenant_id` is the connection's tenant ([`TENANT_SETTING`]). The policy
667/// does nothing until row-level security is switched on.
668///
669/// For a host that keeps tables of its own in the database, each with a
670/// `tenant_id` column: add the policy where the table is created (as its
671/// owner, under the same advisory lock), and switching row-level security on
672/// covers that table too.
673pub async fn add_tenant_policy(
674    client: &impl GenericClient,
675    table: &str,
676) -> Result<(), tokio_postgres::Error> {
677    let present: bool = client
678        .query_one(
679            "SELECT EXISTS (SELECT 1 FROM pg_policies
680                            WHERE schemaname = current_schema()
681                              AND tablename = $1 AND policyname = $2)",
682            &[&table, &TENANT_POLICY],
683        )
684        .await?
685        .get(0);
686    if !present {
687        let tenant = format!("current_setting('{TENANT_SETTING}', true)");
688        client
689            .batch_execute(&format!(
690                "CREATE POLICY {TENANT_POLICY} ON {}
691                     USING (tenant_id = {tenant}) WITH CHECK (tenant_id = {tenant})",
692                quote_ident(table)
693            ))
694            .await?;
695    }
696    Ok(())
697}
698
699/// Whether row-level security is on, table by table, in the storage's schema.
700/// See [`PostgresStorage::row_security`].
701#[derive(Debug, Clone, PartialEq, Eq)]
702pub struct RowSecurity {
703    /// Tables with the tenant policy where row-level security is on. It's
704    /// forced too, so it binds the tables' owner.
705    pub on: Vec<String>,
706    /// Tables with the tenant policy where it's off.
707    pub off: Vec<String>,
708    /// Tables with a `tenant_id` column but no tenant policy. Switching
709    /// row-level security leaves them alone: nothing backs up their queries'
710    /// tenant filters.
711    pub unguarded: Vec<String>,
712    /// Whether this connection's role skips row-level security whatever the
713    /// tables say: a superuser, or a role with `BYPASSRLS`.
714    pub role_bypasses: bool,
715}
716
717impl RowSecurity {
718    /// On for every table with the tenant policy, and binding this role.
719    pub fn enforced(&self) -> bool {
720        self.off.is_empty() && !self.on.is_empty() && !self.role_bypasses
721    }
722}
723
724/// The schema's tables with a `tenant_id` column: each one's name, whether
725/// row-level security is on (enabled and forced), and whether it has the
726/// tenant policy.
727const TENANT_ID_TABLES: &str = "
728    SELECT c.relname::text,
729           c.relrowsecurity AND c.relforcerowsecurity,
730           EXISTS (SELECT 1 FROM pg_policy p
731                   WHERE p.polrelid = c.oid AND p.polname = $1)
732    FROM pg_class c
733    JOIN pg_namespace n ON n.oid = c.relnamespace
734    WHERE n.nspname = current_schema()
735      AND c.relkind IN ('r', 'p')
736      AND EXISTS (SELECT 1 FROM pg_attribute a
737                  WHERE a.attrelid = c.oid AND a.attname = 'tenant_id'
738                    AND NOT a.attisdropped)
739    ORDER BY 1";
740
741async fn row_security_of(
742    client: &impl GenericClient,
743) -> Result<RowSecurity, tokio_postgres::Error> {
744    let mut status = RowSecurity {
745        on: Vec::new(),
746        off: Vec::new(),
747        unguarded: Vec::new(),
748        role_bypasses: client
749            .query_one(
750                "SELECT rolsuper OR rolbypassrls FROM pg_roles WHERE rolname = current_user",
751                &[],
752            )
753            .await?
754            .get(0),
755    };
756    for row in client.query(TENANT_ID_TABLES, &[&TENANT_POLICY]).await? {
757        let (table, on, guarded): (String, bool, bool) = (row.get(0), row.get(1), row.get(2));
758        match (guarded, on) {
759            (false, _) => status.unguarded.push(table),
760            (true, true) => status.on.push(table),
761            (true, false) => status.off.push(table),
762        }
763    }
764    Ok(status)
765}
766
767/// See [`PostgresStorage::set_row_security`]. Switching on touches only the
768/// tables where it's off; switching off touches every table with the policy,
769/// so a table left enabled but not forced goes off too.
770async fn try_set_row_security(
771    client: &mut PgClient,
772    on: bool,
773) -> Result<RowSecurity, tokio_postgres::Error> {
774    let tx = client.transaction().await?;
775    // A waiting ALTER TABLE queues every later query on its table behind it,
776    // so wait for a busy table only so long.
777    tx.batch_execute("SET LOCAL lock_timeout = '10s'").await?;
778    tx.execute("SELECT pg_advisory_xact_lock($1)", &[&SCHEMA_LOCK_KEY])
779        .await?;
780    let before = row_security_of(&tx).await?;
781    let (tables, change): (Vec<&String>, &str) = if on {
782        (
783            before.off.iter().collect(),
784            "ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY",
785        )
786    } else {
787        (
788            before.on.iter().chain(&before.off).collect(),
789            "DISABLE ROW LEVEL SECURITY, NO FORCE ROW LEVEL SECURITY",
790        )
791    };
792    for table in tables {
793        tx.batch_execute(&format!("ALTER TABLE {} {change}", quote_ident(table)))
794            .await?;
795    }
796    let after = row_security_of(&tx).await?;
797    tx.commit().await?;
798    Ok(after)
799}
800
801/// `name` as an SQL identifier, quoted.
802fn quote_ident(name: &str) -> String {
803    format!("\"{}\"", name.replace('"', "\"\""))
804}
805
806/// `value` as an SQL string literal, for a statement sent without parameters.
807/// The `E'...'` form reads backslashes the same way whatever
808/// `standard_conforming_strings` says.
809fn quote_literal(value: &str) -> String {
810    format!("E'{}'", value.replace('\\', "\\\\").replace('\'', "\\'"))
811}
812
813/// Check out a connection from `pool` that works for `tenant`: it sets
814/// [`TENANT_SETTING`] first. The setting lasts for the connection's session,
815/// so it goes back to the pool with it, and the next checkout through here
816/// replaces it. It's sent without parameters because that takes one round
817/// trip, where a statement with parameters takes two.
818async fn checkout(pool: &Pool, tenant: &str) -> Result<PgClient, StorageError> {
819    let client = pool.get().await.map_err(conn_error)?;
820    client
821        .batch_execute(&format!(
822            "SELECT set_config('{TENANT_SETTING}', {}, false)",
823            quote_literal(tenant)
824        ))
825        .await
826        .map_err(|e| StorageError::Backend(with_causes("set the tenant", &e)))?;
827    Ok(client)
828}
829
830/// A storage's connection pool, for one tenant: every connection it hands out
831/// has the tenant set ([`TENANT_SETTING`]), so with row-level security on its
832/// statements see only that tenant's rows. For a host that keeps tables of its
833/// own in the same database; see [`PostgresStorage::tenant_pool`].
834#[derive(Clone)]
835pub struct TenantPool {
836    pool: Pool,
837    tenant_id: String,
838}
839
840impl TenantPool {
841    /// A pooled connection with the tenant set. Use it inside
842    /// [`PostgresStorage::run`]: a connection belongs to the storage's
843    /// runtime.
844    pub async fn get(&self) -> Result<PgClient, StorageError> {
845        checkout(&self.pool, &self.tenant_id).await
846    }
847
848    /// The tenant its connections work for.
849    pub fn tenant_id(&self) -> &str {
850        &self.tenant_id
851    }
852
853    /// The same pool, for another tenant.
854    pub fn for_tenant(&self, tenant_id: &str) -> TenantPool {
855        TenantPool {
856            pool: self.pool.clone(),
857            tenant_id: tenant_id.to_string(),
858        }
859    }
860}
861
862impl std::fmt::Debug for TenantPool {
863    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
864        f.debug_struct("TenantPool")
865            .field("tenant_id", &self.tenant_id)
866            .finish_non_exhaustive()
867    }
868}
869
870/// A database error with its whole cause chain. tokio-postgres's Display for
871/// a server error is just "db error"; the server's message is the source.
872fn with_causes(context: &str, e: &dyn std::error::Error) -> String {
873    let mut msg = format!("{context}: {e}");
874    let mut source = e.source();
875    while let Some(cause) = source {
876        msg.push_str(&format!(": {cause}"));
877        source = cause.source();
878    }
879    msg
880}
881
882/// A failure to get a pooled connection, with its whole cause chain: the pool
883/// and tokio-postgres errors alone say only "error performing TLS handshake",
884/// and the reason (an untrusted issuer, a name mismatch, a refused login) is
885/// further down the chain.
886fn conn_error(e: impl std::error::Error) -> StorageError {
887    let mut msg = format!("get conn: {e}");
888    let mut source = e.source();
889    while let Some(cause) = source {
890        msg.push_str(&format!(": {cause}"));
891        source = cause.source();
892    }
893    StorageError::Backend(msg)
894}
895
896/// What storing an object that already exists does: refresh its write time
897/// if it is over a minute old. A sweep spares recently written objects, so an
898/// object a writer has just stored (or stored again, since content addressing
899/// dedups) can't be taken before the writer commits it. The conflicting row is
900/// locked whether or not it is updated, and the sweep re-checks the write
901/// time when it deletes, so the two can't interleave.
902const FRESHEN_ON_CONFLICT: &str = "ON CONFLICT (tenant_id, id) DO UPDATE SET written_at = now()
903                         WHERE objects.written_at < now() - interval '1 minute'";
904
905/// Advisory-lock key that serializes schema setup across every process using
906/// a database ("asg-sch" as bytes).
907const SCHEMA_LOCK_KEY: i64 = 0x6173_672d_7363_68;
908
909/// Store a tenant's `default` namespace, which every tenant has; a no-op once
910/// it exists.
911const SEED_DEFAULT_NAMESPACE: &str = "INSERT INTO namespaces (tenant_id, name, created_at)
912     VALUES ($1, 'default', NOW()::TEXT)
913     ON CONFLICT DO NOTHING";
914
915/// The runtime that drives every `PostgresStorage` connection in the process.
916///
917/// A pooled connection lives on the runtime it was opened in. Owning that
918/// runtime here means no caller's runtime has to exist, be multi-threaded, or
919/// outlive the pool, and it is never dropped inside an async context.
920fn runtime() -> &'static tokio::runtime::Runtime {
921    static RUNTIME: OnceLock<tokio::runtime::Runtime> = OnceLock::new();
922    RUNTIME.get_or_init(|| {
923        let workers = std::thread::available_parallelism().map_or(2, |n| n.get().clamp(2, 8));
924        tokio::runtime::Builder::new_multi_thread()
925            .worker_threads(workers)
926            .thread_name("asg-postgres")
927            .enable_all()
928            .build()
929            .expect("start the Postgres storage runtime")
930    })
931}
932
933/// PostgreSQL-backed storage with connection pooling and optional multi-tenancy.
934pub struct PostgresStorage {
935    pool: Pool,
936    tenant_id: String,
937    /// How recently written an unreachable object must be for a GC sweep to
938    /// spare it. See [`PostgresStorage::with_gc_grace`].
939    gc_grace: std::time::Duration,
940    /// The connection holding this tenant's sweep lock, between
941    /// `history_gc_lock_writers` and `history_gc_unlock_writers`.
942    gc_conn: std::sync::Mutex<Option<PgClient>>,
943}
944
945impl PostgresStorage {
946    /// Connect to Postgres with the default tenant.
947    pub async fn connect(database_url: &str) -> Result<Self, StorageError> {
948        Self::connect_tenant(database_url, "default").await
949    }
950
951    /// Connect to Postgres with a specific tenant ID for data isolation.
952    ///
953    /// Applies the default pool size cap (`DEFAULT_POOL_SIZE`).
954    pub async fn connect_tenant(database_url: &str, tenant_id: &str) -> Result<Self, StorageError> {
955        Self::connect_tenant_with_pool_size(database_url, tenant_id, DEFAULT_POOL_SIZE).await
956    }
957
958    /// Connect to Postgres with a specific tenant ID and an explicit pool
959    /// size cap (v2-M1). `max_size` is clamped to at least 1.
960    pub async fn connect_tenant_with_pool_size(
961        database_url: &str,
962        tenant_id: &str,
963        max_size: usize,
964    ) -> Result<Self, StorageError> {
965        let storage = Self::with_pool(database_url, tenant_id, max_size)?;
966        // Schema setup runs on the storage runtime, so the connection it opens
967        // (which then goes back into the pool) is driven there rather than by
968        // the caller's runtime, which may be gone when that connection is reused.
969        let setup = Self {
970            pool: storage.pool.clone(),
971            tenant_id: storage.tenant_id.clone(),
972            gc_grace: storage.gc_grace,
973            gc_conn: std::sync::Mutex::new(None),
974        };
975        runtime()
976            .spawn(async move { setup.init_tables().await })
977            .await
978            .map_err(|e| StorageError::Backend(format!("postgres setup task: {e}")))??;
979        Ok(storage)
980    }
981
982    /// Connect from synchronous code. Needs no async runtime, and also works
983    /// when called from inside one.
984    pub fn connect_tenant_blocking(
985        database_url: &str,
986        tenant_id: &str,
987        max_size: usize,
988    ) -> Result<Self, StorageError> {
989        let storage = Self::with_pool(database_url, tenant_id, max_size)?;
990        storage.block_on(storage.init_tables())?;
991        Ok(storage)
992    }
993
994    /// A storage for another tenant on this storage's connection pool, for a
995    /// host that serves many tenants from one process. Every tenant opened
996    /// this way shares the one pool, so the pool's size caps the connections
997    /// the process holds however many tenants it opens, and they share its
998    /// TLS setup. Tenants stay apart as they do across processes: every row
999    /// carries its tenant, and every query filters on it.
1000    ///
1001    /// Cheap enough for a tenant's first request: the schema was brought up to
1002    /// date when this storage connected, so opening a tenant only stores its
1003    /// `default` namespace if it has none. The new storage has its own sweep
1004    /// lock and this storage's GC grace period. Works from any thread, like the
1005    /// rest of the storage API.
1006    pub fn for_tenant(&self, tenant_id: &str) -> Result<Self, StorageError> {
1007        let storage = Self {
1008            pool: self.pool.clone(),
1009            tenant_id: tenant_id.to_string(),
1010            gc_grace: self.gc_grace,
1011            gc_conn: std::sync::Mutex::new(None),
1012        };
1013        storage.block_on(async {
1014            let client = storage.client().await?;
1015            client
1016                .execute(SEED_DEFAULT_NAMESPACE, &[&storage.tenant_id])
1017                .await
1018                .map_err(|e| StorageError::Backend(format!("open tenant: {e}")))?;
1019            Ok(())
1020        })?;
1021        Ok(storage)
1022    }
1023
1024    fn with_pool(
1025        database_url: &str,
1026        tenant_id: &str,
1027        max_size: usize,
1028    ) -> Result<Self, StorageError> {
1029        let tls = ConnectionTls::parse(
1030            database_url,
1031            std::env::var("PGSSLROOTCERT")
1032                .ok()
1033                .filter(|v| !v.is_empty()),
1034        )?;
1035        let mut cfg = Config::new();
1036        cfg.url = Some(tls.url.clone());
1037        // What the pool's spans and metrics say about the database.
1038        let pg_config = cfg
1039            .get_pg_config()
1040            .map_err(|e| StorageError::Backend(format!("postgres config: {e}")))?;
1041        cfg.manager = Some(ManagerConfig {
1042            recycling_method: RecyclingMethod::Fast,
1043        });
1044        cfg.pool = Some(PoolConfig {
1045            max_size: max_size.max(1),
1046            ..Default::default()
1047        });
1048
1049        #[cfg(feature = "postgres-tls")]
1050        let pool = cfg.create_pool(
1051            Some(PoolRuntime::Tokio1),
1052            tls::connector(tls.root_cert.as_deref())?,
1053        );
1054        #[cfg(not(feature = "postgres-tls"))]
1055        let pool = {
1056            if tls.requires_tls || tls.root_cert.is_some() {
1057                return Err(StorageError::Backend(
1058                    "the connection string asks for TLS (sslmode=require/verify-*, or \
1059                     sslrootcert), but this build has no Postgres TLS support: build with \
1060                     the `postgres-tls` feature"
1061                        .into(),
1062                ));
1063            }
1064            cfg.create_pool(Some(PoolRuntime::Tokio1), NoTls)
1065        };
1066        let pool = pool.map_err(|e| StorageError::Backend(format!("postgres pool: {}", e)))?;
1067        let pool = Pool::new(pool, &pg_config);
1068        Ok(Self {
1069            pool,
1070            tenant_id: tenant_id.to_string(),
1071            gc_grace: DEFAULT_GC_GRACE,
1072            gc_conn: std::sync::Mutex::new(None),
1073        })
1074    }
1075
1076    /// Set how recently written an unreachable object must be for a GC sweep
1077    /// to spare it (default [`DEFAULT_GC_GRACE`]).
1078    ///
1079    /// Writers don't wait for a sweep here; the grace period protects them
1080    /// instead. A write stores its new objects before it commits them, and an
1081    /// object stored again refreshes its write time (at most a minute old
1082    /// after any write), so a sweep never takes an object a write in flight
1083    /// is about to commit. A sweep spares everything written since it started,
1084    /// whatever the grace. A grace under a minute narrows the protection for
1085    /// writes already in flight when a sweep starts; tests use zero to see
1086    /// garbage go at once.
1087    pub fn with_gc_grace(mut self, grace: std::time::Duration) -> Self {
1088        self.gc_grace = grace;
1089        self
1090    }
1091
1092    /// Return the configured pool's maximum size. Useful for tests.
1093    pub fn pool_max_size(&self) -> usize {
1094        self.pool.status().max_size
1095    }
1096
1097    /// The storage's connection pool, for its tenant: configured as for the
1098    /// storage itself (TLS, pool size), with every query recorded as a
1099    /// database span, and every connection it hands out has the tenant set
1100    /// ([`TENANT_SETTING`]). For a host that keeps tables of its own in the
1101    /// same database: scope their rows by the tenant all the same, add the
1102    /// tenant policy to each ([`add_tenant_policy`]) so row-level security
1103    /// covers them, and use the pool inside [`PostgresStorage::run`], so a
1104    /// connection it opens is driven by the storage's runtime like the
1105    /// storage's own, not by a caller's runtime that may be gone when the
1106    /// connection is reused. Take the pool before handing the storage to a
1107    /// `Repository`. Create the tables in a transaction holding an advisory
1108    /// lock (`pg_advisory_xact_lock`): `CREATE TABLE IF NOT EXISTS` from two
1109    /// processes at once can still fail on Postgres's catalog index. The
1110    /// client crates are re-exported as
1111    /// `agentstategraph_storage::{oxtel_sql, tokio_postgres}`.
1112    pub fn tenant_pool(&self) -> TenantPool {
1113        TenantPool {
1114            pool: self.pool.clone(),
1115            tenant_id: self.tenant_id.clone(),
1116        }
1117    }
1118
1119    /// The storage's connection pool, raw: its connections don't set a
1120    /// tenant, so one carries whichever tenant used it last, and row-level
1121    /// security can't back up a query on it.
1122    #[deprecated(
1123        since = "1.5.0",
1124        note = "use tenant_pool(): a connection from pool() carries whichever tenant used it \
1125                last, so row-level security can't back up its queries"
1126    )]
1127    pub fn pool(&self) -> Pool {
1128        self.pool.clone()
1129    }
1130
1131    /// The tenant this storage reads and writes.
1132    pub fn tenant_id(&self) -> &str {
1133        &self.tenant_id
1134    }
1135
1136    /// Where row-level security stands in the storage's schema, table by
1137    /// table, and whether this connection's role skips it.
1138    pub fn row_security(&self) -> Result<RowSecurity, StorageError> {
1139        self.block_on(async {
1140            let client = self.client().await?;
1141            row_security_of(&client)
1142                .await
1143                .map_err(|e| StorageError::Backend(with_causes("row-level security", &e)))
1144        })
1145    }
1146
1147    /// Switch row-level security on or off for every table in the storage's
1148    /// schema that has the tenant policy, AgentStateGraph's and a host's
1149    /// alike, and return where it then stands. On means enabled and forced,
1150    /// so it binds the tables' owner too: a statement sees and writes only
1151    /// the rows of its connection's tenant ([`TENANT_SETTING`]), a backstop
1152    /// behind every query's own tenant filter.
1153    ///
1154    /// It's off until switched on, per database. Every connection the storage
1155    /// checks out already has its tenant set, so the processes using the
1156    /// database needn't restart. It doesn't cover a superuser or a role with
1157    /// `BYPASSRLS` (see [`RowSecurity::role_bypasses`]), so connect as a role
1158    /// with neither; nor a connection from the deprecated
1159    /// [`pool`](Self::pool).
1160    ///
1161    /// Run it as the role that owns the tables. It locks each table briefly,
1162    /// under the schema setup lock, and gives up if it waits 10 seconds for
1163    /// one. With it on, `pg_dump` run as the tables' owner fails rather than
1164    /// dump only some rows: dump as a superuser or a role with `BYPASSRLS`.
1165    pub fn set_row_security(&self, on: bool) -> Result<RowSecurity, StorageError> {
1166        let context = if on {
1167            "switch row-level security on"
1168        } else {
1169            "switch row-level security off"
1170        };
1171        let deadlock = Some(&tokio_postgres::error::SqlState::T_R_DEADLOCK_DETECTED);
1172        self.block_on(async {
1173            let mut client = self.client().await?;
1174            let mut attempt = 0;
1175            loop {
1176                attempt += 1;
1177                match try_set_row_security(&mut client, on).await {
1178                    Err(e) if attempt < 5 && e.code() == deadlock => continue,
1179                    Err(e) => return Err(StorageError::Backend(with_causes(context, &e))),
1180                    Ok(status) => return Ok(status),
1181                }
1182            }
1183        })
1184    }
1185
1186    /// A pooled connection for this storage's tenant. Every query the storage
1187    /// runs goes through one.
1188    async fn client(&self) -> Result<PgClient, StorageError> {
1189        checkout(&self.pool, &self.tenant_id).await
1190    }
1191
1192    /// Bring the schema up to date and seed this tenant's default namespace.
1193    ///
1194    /// The DDL runs only when the database's stamped schema version is behind
1195    /// this build's. `ALTER TABLE ... IF NOT EXISTS` takes an exclusive table
1196    /// lock even when it changes nothing, so running it on every connect let a
1197    /// starting process deadlock with writers holding locks on several tables
1198    /// (any multi-statement transaction). Up-to-date databases now take no
1199    /// table locks at startup. An actual upgrade still can collide with
1200    /// writers, so a deadlock victim retries.
1201    ///
1202    /// A database set up by a newer build is used as it is, unless that build
1203    /// stamped it as needing more than this one understands: then this fails
1204    /// with [`StorageError::SchemaTooNew`] and changes nothing.
1205    async fn init_tables(&self) -> Result<(), StorageError> {
1206        let mut client = self.client().await?;
1207        let mut attempt = 0;
1208        loop {
1209            attempt += 1;
1210            match self.try_init_tables(&mut client).await {
1211                Ok(Ok(())) => return Ok(()),
1212                Ok(Err(too_new)) => return Err(too_new),
1213                Err(e)
1214                    if attempt < 5
1215                        && e.code()
1216                            == Some(&tokio_postgres::error::SqlState::T_R_DEADLOCK_DETECTED) =>
1217                {
1218                    continue;
1219                }
1220                Err(e) => return Err(StorageError::Backend(with_causes("init tables", &e))),
1221            }
1222        }
1223    }
1224
1225    /// The inner result is [`StorageError::SchemaTooNew`] when the database
1226    /// is stamped as needing a newer build; the transaction is rolled back.
1227    async fn try_init_tables(
1228        &self,
1229        client: &mut PgClient,
1230    ) -> Result<Result<(), StorageError>, tokio_postgres::Error> {
1231        // Serialized across every process using the database.
1232        let tx = client.transaction().await?;
1233        tx.execute("SELECT pg_advisory_xact_lock($1)", &[&SCHEMA_LOCK_KEY])
1234            .await?;
1235        let stamped: bool = tx
1236            .query_one("SELECT to_regclass('asg_schema') IS NOT NULL", &[])
1237            .await?
1238            .get(0);
1239        let version: Option<i32> = if stamped {
1240            tx.query_opt(
1241                "SELECT version FROM asg_schema WHERE component = 'postgres'",
1242                &[],
1243            )
1244            .await?
1245            .map(|row| row.get(0))
1246        } else {
1247            None
1248        };
1249        if let Some(found) = version.filter(|v| *v > SCHEMA_VERSION) {
1250            // Stamped by a newer build, which also added the column.
1251            let compatible_from: i32 = tx
1252                .query_one(
1253                    "SELECT compatible_from FROM asg_schema WHERE component = 'postgres'",
1254                    &[],
1255                )
1256                .await?
1257                .get(0);
1258            if compatible_from > SCHEMA_VERSION {
1259                return Ok(Err(StorageError::SchemaTooNew {
1260                    backend: "postgres",
1261                    found,
1262                    compatible_from,
1263                    supported: SCHEMA_VERSION,
1264                }));
1265            }
1266        }
1267        if version.is_none_or(|v| v < SCHEMA_VERSION) {
1268            tx.batch_execute(SCHEMA_DDL).await?;
1269            for table in TENANT_TABLES {
1270                add_tenant_policy(&tx, table).await?;
1271            }
1272            tx.execute(
1273                "INSERT INTO asg_schema (component, version, compatible_from)
1274                 VALUES ('postgres', $1, $2)
1275                 ON CONFLICT (component) DO UPDATE SET
1276                     version = EXCLUDED.version,
1277                     compatible_from = GREATEST(asg_schema.compatible_from,
1278                                                EXCLUDED.compatible_from)",
1279                &[&SCHEMA_VERSION, &SCHEMA_COMPATIBLE_FROM],
1280            )
1281            .await?;
1282        }
1283        tx.execute(SEED_DEFAULT_NAMESPACE, &[&self.tenant_id])
1284            .await?;
1285        tx.commit().await.map(Ok)
1286    }
1287
1288    /// Run an async operation for the sync storage API, on whatever kind of
1289    /// thread the caller is on. See [`run`](Self::run).
1290    fn block_on<F, T>(&self, f: F) -> Result<T, StorageError>
1291    where
1292        F: std::future::Future<Output = Result<T, StorageError>> + Send,
1293        T: Send,
1294    {
1295        Self::run(f)
1296    }
1297
1298    /// Run `f` to completion on the storage's runtime (one per process), from
1299    /// whatever kind of thread the caller is on: a plain thread, or a thread of
1300    /// either kind of tokio runtime. Every synchronous storage method goes
1301    /// through this; a host uses it with [`pool`](Self::pool) for its own
1302    /// tables. (Before Plan D t-002 this assumed a multi-threaded tokio
1303    /// runtime: plain threads panicked, as did single-threaded runtimes.)
1304    pub fn run<F, T>(f: F) -> T
1305    where
1306        F: std::future::Future<Output = T> + Send,
1307        T: Send,
1308    {
1309        let rt = runtime().handle();
1310        match tokio::runtime::Handle::try_current().map(|h| h.runtime_flavor()) {
1311            // Not on a runtime: drive the operation on this thread.
1312            Err(_) => rt.block_on(f),
1313            // A multi-threaded runtime's thread: hand its other work off first.
1314            Ok(RuntimeFlavor::MultiThread) => tokio::task::block_in_place(|| rt.block_on(f)),
1315            // A single-threaded runtime can neither give up its only thread nor
1316            // start another runtime on it, so wait on a helper thread.
1317            Ok(_) => std::thread::scope(|scope| {
1318                scope
1319                    .spawn(|| rt.block_on(f))
1320                    .join()
1321                    .unwrap_or_else(|panic| std::panic::resume_unwind(panic))
1322            }),
1323        }
1324    }
1325
1326    async fn require_namespace(
1327        &self,
1328        client: &PgClient,
1329        namespace: &Namespace,
1330    ) -> Result<(), StorageError> {
1331        let row = client
1332            .query_opt(
1333                "SELECT 1 FROM namespaces WHERE tenant_id = $1 AND name = $2",
1334                &[&self.tenant_id, &namespace.as_str()],
1335            )
1336            .await
1337            .map_err(|e| StorageError::Backend(format!("check namespace: {}", e)))?;
1338        match row {
1339            Some(_) => Ok(()),
1340            None => Err(StorageError::NamespaceNotFound(
1341                namespace.as_str().to_string(),
1342            )),
1343        }
1344    }
1345}
1346
1347impl PostgresStorage {
1348    /// Replace the `(namespace, ref)` leaf set with `entries`, marking it
1349    /// built at `root` (unknown when `None`). The set's row is written first,
1350    /// so a build and an advance on one set lock in the same order.
1351    fn leaf_set_replace(
1352        &self,
1353        namespace: &str,
1354        ref_name: &str,
1355        root: Option<&ObjectId>,
1356        entries: &[(String, String)],
1357    ) -> Result<(), StorageError> {
1358        let root = root.map(|r| r.as_bytes().as_slice());
1359        self.block_on(async {
1360            let mut client = self.client().await?;
1361            let tx = client
1362                .transaction()
1363                .await
1364                .map_err(|e| leaf_error("build", &e))?;
1365            tx.execute(
1366                "INSERT INTO leaf_index_refs (tenant_id, namespace, ref, root)
1367                 VALUES ($1, $2, $3, $4)
1368                 ON CONFLICT (tenant_id, namespace, ref) DO UPDATE SET root = EXCLUDED.root",
1369                &[&self.tenant_id, &namespace, &ref_name, &root],
1370            )
1371            .await
1372            .map_err(|e| leaf_error("build mark", &e))?;
1373            tx.execute(
1374                "DELETE FROM leaf_rows WHERE tenant_id = $1 AND namespace = $2 AND ref = $3",
1375                &[&self.tenant_id, &namespace, &ref_name],
1376            )
1377            .await
1378            .map_err(|e| leaf_error("build clear", &e))?;
1379            upsert_leaves(&tx, &self.tenant_id, namespace, ref_name, entries).await?;
1380            tx.commit()
1381                .await
1382                .map_err(|e| leaf_error("build commit", &e))
1383        })
1384    }
1385}
1386
1387// ─── ObjectStore ────────────────────────────────────────────
1388
1389impl ObjectStore for PostgresStorage {
1390    fn get_object(&self, id: &ObjectId) -> Result<Option<Object>, StorageError> {
1391        self.block_on(async {
1392            let client = self.client().await?;
1393
1394            let row = client
1395                .query_opt(
1396                    "SELECT data, exact FROM objects WHERE tenant_id = $1 AND id = $2",
1397                    &[&self.tenant_id, &id.as_bytes().as_slice()],
1398                )
1399                .await
1400                .map_err(|e| StorageError::Backend(format!("get object: {}", e)))?;
1401
1402            match row {
1403                Some(row) => Ok(Some(from_row_json(row.get("data"), row.get("exact"))?)),
1404                None => Ok(None),
1405            }
1406        })
1407    }
1408
1409    fn put_object(&self, obj: &Object) -> Result<ObjectId, StorageError> {
1410        let id = obj.id();
1411        let (data, exact) = row_json(obj)?;
1412
1413        self.block_on(async {
1414            let client = self.client().await?;
1415
1416            client
1417                .execute(
1418                    &format!(
1419                        "INSERT INTO objects (tenant_id, id, data, exact) VALUES ($1, $2, $3, $4)
1420                         {FRESHEN_ON_CONFLICT}"
1421                    ),
1422                    &[&self.tenant_id, &id.as_bytes().as_slice(), &data, &exact],
1423                )
1424                .await
1425                .map_err(|e| StorageError::Backend(format!("put object: {}", e)))?;
1426
1427            Ok(id)
1428        })
1429    }
1430
1431    /// One multi-row INSERT per chunk, all in one transaction: callers such as
1432    /// merge write a batch as a unit, and the trait's default made one
1433    /// independent round trip per object.
1434    fn batch_put_objects(&self, objs: &[Object]) -> Result<Vec<ObjectId>, StorageError> {
1435        // Each row is three parameters ($1 is the tenant), well under
1436        // Postgres's 65,535 per statement.
1437        const CHUNK: usize = 1000;
1438        let ids: Vec<ObjectId> = objs.iter().map(Object::id).collect();
1439        // One row per id: an upsert may not touch a row twice in a statement.
1440        let mut seen = std::collections::HashSet::with_capacity(ids.len());
1441        let rows: Vec<(ObjectId, serde_json::Value, Option<Vec<u8>>)> = objs
1442            .iter()
1443            .zip(&ids)
1444            .filter(|(_, id)| seen.insert(**id))
1445            .map(|(obj, id)| row_json(obj).map(|(data, exact)| (*id, data, exact)))
1446            .collect::<Result<_, _>>()?;
1447        if rows.is_empty() {
1448            return Ok(Vec::new());
1449        }
1450        self.block_on(async {
1451            let mut client = self.client().await?;
1452            let tx = client
1453                .transaction()
1454                .await
1455                .map_err(|e| StorageError::Backend(format!("begin batch put: {e}")))?;
1456            for chunk in rows.chunks(CHUNK) {
1457                let ids: Vec<&[u8]> = chunk
1458                    .iter()
1459                    .map(|(id, _, _)| id.as_bytes().as_slice())
1460                    .collect();
1461                let mut params: Vec<&(dyn tokio_postgres::types::ToSql + Sync)> =
1462                    Vec::with_capacity(1 + chunk.len() * 3);
1463                params.push(&self.tenant_id);
1464                let mut values = Vec::with_capacity(chunk.len());
1465                for (i, (_, data, exact)) in chunk.iter().enumerate() {
1466                    values.push(format!(
1467                        "($1, ${}, ${}, ${})",
1468                        2 + i * 3,
1469                        3 + i * 3,
1470                        4 + i * 3
1471                    ));
1472                    params.push(&ids[i]);
1473                    params.push(data);
1474                    params.push(exact);
1475                }
1476                tx.execute(
1477                    &format!(
1478                        "INSERT INTO objects (tenant_id, id, data, exact) VALUES {}
1479                         {FRESHEN_ON_CONFLICT}",
1480                        values.join(", ")
1481                    ),
1482                    &params,
1483                )
1484                .await
1485                .map_err(|e| StorageError::Backend(format!("batch put objects: {e}")))?;
1486            }
1487            tx.commit()
1488                .await
1489                .map_err(|e| StorageError::Backend(format!("commit batch put: {e}")))?;
1490            Ok(())
1491        })?;
1492        Ok(ids)
1493    }
1494
1495    /// One query for the whole batch, results in the order asked.
1496    fn batch_get_objects(&self, ids: &[ObjectId]) -> Result<Vec<Option<Object>>, StorageError> {
1497        if ids.is_empty() {
1498            return Ok(Vec::new());
1499        }
1500        let wanted: Vec<Vec<u8>> = ids.iter().map(|id| id.as_bytes().to_vec()).collect();
1501        let found = self.block_on(async {
1502            let client = self.client().await?;
1503            let rows = client
1504                .query(
1505                    "SELECT id, data, exact FROM objects WHERE tenant_id = $1 AND id = ANY($2)",
1506                    &[&self.tenant_id, &wanted],
1507                )
1508                .await
1509                .map_err(|e| StorageError::Backend(format!("batch get objects: {e}")))?;
1510            let mut found = std::collections::HashMap::with_capacity(rows.len());
1511            for row in rows {
1512                let id: Vec<u8> = row.get("id");
1513                let obj: Object = from_row_json(row.get("data"), row.get("exact"))?;
1514                found.insert(id, obj);
1515            }
1516            Ok(found)
1517        })?;
1518        Ok(wanted.iter().map(|id| found.get(id).cloned()).collect())
1519    }
1520
1521    fn leaf_index_is_built(&self, namespace: &str, ref_name: &str) -> Result<bool, StorageError> {
1522        self.block_on(async {
1523            let client = self.client().await?;
1524            leaf_set_built(&client, &self.tenant_id, namespace, ref_name).await
1525        })
1526    }
1527
1528    fn leaf_index_build(
1529        &self,
1530        namespace: &str,
1531        ref_name: &str,
1532        entries: &[(String, String)],
1533    ) -> Result<(), StorageError> {
1534        self.leaf_set_replace(namespace, ref_name, None, entries)
1535    }
1536
1537    fn leaf_index_apply(
1538        &self,
1539        namespace: &str,
1540        ref_name: &str,
1541        removed_paths: &[String],
1542        added: &[(String, String)],
1543    ) -> Result<(), StorageError> {
1544        self.block_on(async {
1545            let mut client = self.client().await?;
1546            let tx = client
1547                .transaction()
1548                .await
1549                .map_err(|e| leaf_error("apply", &e))?;
1550            // Only a built set is maintained; an unbuilt one is filled from
1551            // the current tree on its first search. The set no longer
1552            // reflects a known tree, so its root is cleared.
1553            let built = tx
1554                .execute(
1555                    "UPDATE leaf_index_refs SET root = NULL
1556                     WHERE tenant_id = $1 AND namespace = $2 AND ref = $3",
1557                    &[&self.tenant_id, &namespace, &ref_name],
1558                )
1559                .await
1560                .map_err(|e| leaf_error("apply check", &e))?
1561                > 0;
1562            if !built {
1563                return Ok(());
1564            }
1565            if !removed_paths.is_empty() {
1566                let removed: Vec<std::borrow::Cow<'_, str>> =
1567                    removed_paths.iter().map(|p| leaf_text(p)).collect();
1568                let removed: Vec<&str> = removed.iter().map(|p| p.as_ref()).collect();
1569                tx.execute(
1570                    "DELETE FROM leaf_rows
1571                     WHERE tenant_id = $1 AND namespace = $2 AND ref = $3 AND path = ANY($4)",
1572                    &[&self.tenant_id, &namespace, &ref_name, &removed],
1573                )
1574                .await
1575                .map_err(|e| leaf_error("apply delete", &e))?;
1576            }
1577            upsert_leaves(&tx, &self.tenant_id, namespace, ref_name, added).await?;
1578            tx.commit()
1579                .await
1580                .map_err(|e| leaf_error("apply commit", &e))
1581        })
1582    }
1583
1584    fn leaf_index_tracks_roots(&self) -> bool {
1585        true
1586    }
1587
1588    fn leaf_index_root(
1589        &self,
1590        namespace: &str,
1591        ref_name: &str,
1592    ) -> Result<Option<ObjectId>, StorageError> {
1593        self.block_on(async {
1594            let client = self.client().await?;
1595            let row = client
1596                .query_opt(
1597                    "SELECT root FROM leaf_index_refs
1598                     WHERE tenant_id = $1 AND namespace = $2 AND ref = $3",
1599                    &[&self.tenant_id, &namespace, &ref_name],
1600                )
1601                .await
1602                .map_err(|e| leaf_error("root", &e))?;
1603            Ok(row.and_then(|row| {
1604                row.get::<_, Option<&[u8]>>(0)
1605                    .and_then(object_id_from_bytes)
1606            }))
1607        })
1608    }
1609
1610    fn leaf_index_build_at(
1611        &self,
1612        namespace: &str,
1613        ref_name: &str,
1614        root: &ObjectId,
1615        entries: &[(String, String)],
1616    ) -> Result<(), StorageError> {
1617        self.leaf_set_replace(namespace, ref_name, Some(root), entries)
1618    }
1619
1620    fn leaf_index_advance(
1621        &self,
1622        namespace: &str,
1623        ref_name: &str,
1624        from: &ObjectId,
1625        to: &ObjectId,
1626        removed_paths: &[String],
1627        added: &[(String, String)],
1628    ) -> Result<bool, StorageError> {
1629        self.block_on(async {
1630            let mut client = self.client().await?;
1631            let tx = client
1632                .transaction()
1633                .await
1634                .map_err(|e| leaf_error("advance", &e))?;
1635            // Moving the root locks the set's row, so racing advances take
1636            // turns, and a loser finds the root already moved.
1637            let moved = tx
1638                .execute(
1639                    "UPDATE leaf_index_refs SET root = $5
1640                     WHERE tenant_id = $1 AND namespace = $2 AND ref = $3 AND root = $4",
1641                    &[
1642                        &self.tenant_id,
1643                        &namespace,
1644                        &ref_name,
1645                        &from.as_bytes().as_slice(),
1646                        &to.as_bytes().as_slice(),
1647                    ],
1648                )
1649                .await
1650                .map_err(|e| leaf_error("advance root", &e))?
1651                > 0;
1652            if !moved {
1653                return Ok(false);
1654            }
1655            if !removed_paths.is_empty() {
1656                let removed: Vec<std::borrow::Cow<'_, str>> =
1657                    removed_paths.iter().map(|p| leaf_text(p)).collect();
1658                let removed: Vec<&str> = removed.iter().map(|p| p.as_ref()).collect();
1659                tx.execute(
1660                    "DELETE FROM leaf_rows
1661                     WHERE tenant_id = $1 AND namespace = $2 AND ref = $3 AND path = ANY($4)",
1662                    &[&self.tenant_id, &namespace, &ref_name, &removed],
1663                )
1664                .await
1665                .map_err(|e| leaf_error("advance delete", &e))?;
1666            }
1667            upsert_leaves(&tx, &self.tenant_id, namespace, ref_name, added).await?;
1668            tx.commit()
1669                .await
1670                .map_err(|e| leaf_error("advance commit", &e))?;
1671            Ok(true)
1672        })
1673    }
1674
1675    /// Case-insensitive substring match. The trigram index serves it when
1676    /// pg_trgm is installed; `%`, `_` and `\` in the query match themselves.
1677    fn leaf_index_search(
1678        &self,
1679        namespace: &str,
1680        ref_name: &str,
1681        query_lower: &str,
1682        limit: usize,
1683    ) -> Result<Option<Vec<(String, String)>>, StorageError> {
1684        self.block_on(async {
1685            let client = self.client().await?;
1686            if !leaf_set_built(&client, &self.tenant_id, namespace, ref_name).await? {
1687                return Ok(None);
1688            }
1689            let rows = client
1690                .query(
1691                    "SELECT path, value FROM leaf_rows
1692                     WHERE tenant_id = $1 AND namespace = $2 AND ref = $3 AND value ILIKE $4
1693                     LIMIT $5",
1694                    &[
1695                        &self.tenant_id,
1696                        &namespace,
1697                        &ref_name,
1698                        &like_contains(query_lower),
1699                        &sql_count(limit),
1700                    ],
1701                )
1702                .await
1703                .map_err(|e| leaf_error("search", &e))?;
1704            Ok(Some(
1705                rows.iter()
1706                    .map(|row| (row.get("path"), row.get("value")))
1707                    .collect(),
1708            ))
1709        })
1710    }
1711
1712    fn has_object(&self, id: &ObjectId) -> Result<bool, StorageError> {
1713        self.block_on(async {
1714            let client = self.client().await?;
1715
1716            let row = client
1717                .query_one(
1718                    "SELECT EXISTS(SELECT 1 FROM objects WHERE tenant_id = $1 AND id = $2) as e",
1719                    &[&self.tenant_id, &id.as_bytes().as_slice()],
1720                )
1721                .await
1722                .map_err(|e| StorageError::Backend(format!("has object: {}", e)))?;
1723
1724            Ok(row.get::<_, bool>("e"))
1725        })
1726    }
1727}
1728
1729// ─── CommitStore ────────────────────────────────────────────
1730
1731impl CommitStore for PostgresStorage {
1732    fn put_commit_traced(
1733        &self,
1734        commit: &Commit,
1735        trace: Option<&CommitTrace>,
1736    ) -> Result<(), StorageError> {
1737        let Some(trace) = trace else {
1738            return self.put_commit(commit);
1739        };
1740        let (data, exact) = row_json(commit)?;
1741        let timestamp = commit.timestamp;
1742        self.block_on(async {
1743            let client = self.client().await?;
1744            // One statement, so one round trip.
1745            client
1746                .execute(
1747                    "WITH stored AS (
1748                         INSERT INTO commits (tenant_id, id, data, timestamp, exact)
1749                         VALUES ($1, $2, $3, $4, $7)
1750                         ON CONFLICT (tenant_id, id) DO NOTHING
1751                     )
1752                     INSERT INTO commit_traces (tenant_id, commit_id, trace_id, span_id)
1753                     VALUES ($1, $2, $5, $6)
1754                     ON CONFLICT (tenant_id, commit_id) DO NOTHING",
1755                    &[
1756                        &self.tenant_id,
1757                        &commit.id.as_bytes().as_slice(),
1758                        &data,
1759                        &timestamp,
1760                        &trace.trace_id,
1761                        &trace.span_id,
1762                        &exact,
1763                    ],
1764                )
1765                .await
1766                .map_err(|e| StorageError::Backend(format!("put commit: {}", e)))?;
1767            Ok(())
1768        })
1769    }
1770
1771    fn set_commit_trace(
1772        &self,
1773        commit_id: &ObjectId,
1774        trace: &CommitTrace,
1775    ) -> Result<(), StorageError> {
1776        self.block_on(async {
1777            let client = self.client().await?;
1778            client
1779                .execute(
1780                    "INSERT INTO commit_traces (tenant_id, commit_id, trace_id, span_id)
1781                     VALUES ($1, $2, $3, $4)
1782                     ON CONFLICT (tenant_id, commit_id) DO NOTHING",
1783                    &[
1784                        &self.tenant_id,
1785                        &commit_id.as_bytes().as_slice(),
1786                        &trace.trace_id,
1787                        &trace.span_id,
1788                    ],
1789                )
1790                .await
1791                .map_err(|e| StorageError::Backend(format!("set commit trace: {}", e)))?;
1792            Ok(())
1793        })
1794    }
1795
1796    fn get_commit_trace(&self, commit_id: &ObjectId) -> Result<Option<CommitTrace>, StorageError> {
1797        self.block_on(async {
1798            let client = self.client().await?;
1799            let row = client
1800                .query_opt(
1801                    "SELECT trace_id, span_id FROM commit_traces
1802                     WHERE tenant_id = $1 AND commit_id = $2",
1803                    &[&self.tenant_id, &commit_id.as_bytes().as_slice()],
1804                )
1805                .await
1806                .map_err(|e| StorageError::Backend(format!("get commit trace: {}", e)))?;
1807            Ok(row.map(|row| CommitTrace {
1808                trace_id: row.get(0),
1809                span_id: row.get(1),
1810            }))
1811        })
1812    }
1813
1814    fn all_commit_ids(&self) -> Result<Vec<ObjectId>, StorageError> {
1815        self.block_on(async {
1816            let client = self.client().await?;
1817            let rows = client
1818                .query(
1819                    "SELECT id FROM commits WHERE tenant_id = $1",
1820                    &[&self.tenant_id],
1821                )
1822                .await
1823                .map_err(|e| StorageError::Backend(format!("all commit ids: {}", e)))?;
1824            let mut ids = Vec::new();
1825            for row in rows {
1826                let bytes: Vec<u8> = row.get("id");
1827                if bytes.len() == 32 {
1828                    let mut arr = [0u8; 32];
1829                    arr.copy_from_slice(&bytes);
1830                    ids.push(ObjectId::from_bytes(arr));
1831                }
1832            }
1833            Ok(ids)
1834        })
1835    }
1836
1837    fn get_commit(&self, id: &ObjectId) -> Result<Option<Commit>, StorageError> {
1838        self.block_on(async {
1839            let client = self.client().await?;
1840
1841            let row = client
1842                .query_opt(
1843                    "SELECT data, exact FROM commits WHERE tenant_id = $1 AND id = $2",
1844                    &[&self.tenant_id, &id.as_bytes().as_slice()],
1845                )
1846                .await
1847                .map_err(|e| StorageError::Backend(format!("get commit: {}", e)))?;
1848
1849            match row {
1850                Some(row) => Ok(Some(commit_from_row(&row)?)),
1851                None => Ok(None),
1852            }
1853        })
1854    }
1855
1856    fn put_commit(&self, commit: &Commit) -> Result<(), StorageError> {
1857        let (data, exact) = row_json(commit)?;
1858        let timestamp = commit.timestamp;
1859
1860        self.block_on(async {
1861            let client = self.client().await?;
1862
1863            client
1864                .execute(
1865                    "INSERT INTO commits (tenant_id, id, data, timestamp, exact)
1866                     VALUES ($1, $2, $3, $4, $5)
1867                     ON CONFLICT (tenant_id, id) DO NOTHING",
1868                    &[
1869                        &self.tenant_id,
1870                        &commit.id.as_bytes().as_slice(),
1871                        &data,
1872                        &timestamp,
1873                        &exact,
1874                    ],
1875                )
1876                .await
1877                .map_err(|e| StorageError::Backend(format!("put commit: {}", e)))?;
1878
1879            Ok(())
1880        })
1881    }
1882
1883    fn has_commit(&self, id: &ObjectId) -> Result<bool, StorageError> {
1884        self.block_on(async {
1885            let client = self.client().await?;
1886
1887            let row = client
1888                .query_one(
1889                    "SELECT EXISTS(SELECT 1 FROM commits WHERE tenant_id = $1 AND id = $2) as e",
1890                    &[&self.tenant_id, &id.as_bytes().as_slice()],
1891                )
1892                .await
1893                .map_err(|e| StorageError::Backend(format!("has commit: {}", e)))?;
1894
1895            Ok(row.get::<_, bool>("e"))
1896        })
1897    }
1898
1899    /// The first-parent line in one recursive query, instead of one round
1900    /// trip per commit.
1901    fn list_commits(&self, from: &ObjectId, limit: usize) -> Result<Vec<Commit>, StorageError> {
1902        if limit == 0 {
1903            return Ok(Vec::new());
1904        }
1905        self.block_on(async {
1906            let client = self.client().await?;
1907            let rows = client
1908                .query(
1909                    "WITH RECURSIVE line(data, exact, n) AS (
1910                         SELECT data, exact, 1::BIGINT FROM commits
1911                         WHERE tenant_id = $1 AND id = $2
1912                       UNION ALL
1913                         SELECT parent.data, parent.exact, line.n + 1
1914                         FROM line
1915                         CROSS JOIN LATERAL (
1916                             SELECT c.data, c.exact FROM commits c
1917                             WHERE c.data -> 'id' = line.data -> 'parents' -> 0
1918                               AND c.tenant_id = $1
1919                             LIMIT 1
1920                         ) parent
1921                         WHERE line.n < $3
1922                     )
1923                     SELECT data, exact FROM line ORDER BY n",
1924                    &[
1925                        &self.tenant_id,
1926                        &from.as_bytes().as_slice(),
1927                        &sql_count(limit),
1928                    ],
1929                )
1930                .await
1931                .map_err(|e| StorageError::Backend(with_causes("list commits", &e)))?;
1932            rows.iter().map(commit_from_row).collect()
1933        })
1934    }
1935
1936    /// Every parent edge in one recursive query. `UNION` keeps each commit
1937    /// once however many paths reach it, so merge-heavy histories stay
1938    /// linear, and the walk stops once it has one commit more than `limit`
1939    /// (which is how truncation is detected). Commits come out breadth
1940    /// first; order within one generation is unspecified. Each parent is
1941    /// looked up through `LATERAL ... LIMIT 1`, which keeps the step an index
1942    /// lookup: given a plain join, the planner may hash the tenant's whole
1943    /// commit table instead.
1944    fn list_commits_dag(&self, from: &ObjectId, limit: usize) -> Result<CommitWalk, StorageError> {
1945        let mut commits = self.block_on(async {
1946            let client = self.client().await?;
1947            let rows = client
1948                .query(
1949                    "WITH RECURSIVE walk(id, parents) AS (
1950                         SELECT id, data -> 'parents' FROM commits
1951                         WHERE tenant_id = $1 AND id = $2
1952                       UNION
1953                         SELECT parent.id, parent.parents
1954                         FROM walk
1955                         CROSS JOIN LATERAL jsonb_array_elements(walk.parents) AS p(id)
1956                         CROSS JOIN LATERAL (
1957                             SELECT c.id, c.data -> 'parents' AS parents FROM commits c
1958                             WHERE c.data -> 'id' = p.id AND c.tenant_id = $1
1959                             LIMIT 1
1960                         ) parent
1961                     ),
1962                     reached AS (
1963                         SELECT id, row_number() OVER () AS n
1964                         FROM (SELECT id FROM walk LIMIT $3) first
1965                     )
1966                     SELECT c.data, c.exact
1967                     FROM reached JOIN commits c ON c.tenant_id = $1 AND c.id = reached.id
1968                     ORDER BY reached.n",
1969                    &[
1970                        &self.tenant_id,
1971                        &from.as_bytes().as_slice(),
1972                        &sql_count(limit).saturating_add(1),
1973                    ],
1974                )
1975                .await
1976                .map_err(|e| StorageError::Backend(with_causes("list commits dag", &e)))?;
1977            rows.iter()
1978                .map(commit_from_row)
1979                .collect::<Result<Vec<_>, _>>()
1980        })?;
1981        let truncated = commits.len() > limit;
1982        commits.truncate(limit);
1983        Ok(CommitWalk { commits, truncated })
1984    }
1985
1986    /// Fold the next commits into the history tables, as the SQLite backend
1987    /// does, but ordered by the id of the transaction that wrote each commit
1988    /// rather than by insertion order. With several writers, a commit can
1989    /// become visible after a later-inserted one has already been folded, and
1990    /// an insertion-order cursor would step past it for good. Only commits
1991    /// whose transaction is older than every transaction still running are
1992    /// read, so no commit can appear behind the cursor later. When commits
1993    /// are waiting on an older transaction, this waits up to `HISTORY_WAIT`
1994    /// for it to finish; a writing transaction that runs longer than that,
1995    /// anywhere on the server, delays history until it ends.
1996    fn history_extract_batch(&self, batch_size: usize) -> Result<usize, StorageError> {
1997        let batch_size = sql_count(batch_size.max(1));
1998        self.block_on(async {
1999            let mut client = self.client().await?;
2000            let tx = client
2001                .transaction()
2002                .await
2003                .map_err(|e| history_error("begin", &e))?;
2004            // The cursor row is the lock: one extractor per tenant at a time,
2005            // so concurrent extractors can't fold a commit twice.
2006            tx.execute(
2007                "INSERT INTO asg_history_cursor (tenant_id, xid, commit_id, folded)
2008                 VALUES ($1, 0, ''::BYTEA, 0)
2009                 ON CONFLICT (tenant_id) DO NOTHING",
2010                &[&self.tenant_id],
2011            )
2012            .await
2013            .map_err(|e| history_error("cursor", &e))?;
2014            let cursor = tx
2015                .query_one(
2016                    "SELECT xid, commit_id FROM asg_history_cursor
2017                     WHERE tenant_id = $1 FOR UPDATE",
2018                    &[&self.tenant_id],
2019                )
2020                .await
2021                .map_err(|e| history_error("cursor", &e))?;
2022            let (after_xid, after_id): (i64, Vec<u8>) = (cursor.get(0), cursor.get(1));
2023
2024            // A commit made outside a scoped session is attributed to
2025            // HISTORY_NAMESPACE_UNKNOWN, as in SQLite.
2026            let deadline = tokio::time::Instant::now() + HISTORY_WAIT;
2027            let rows = loop {
2028                let rows = tx
2029                    .query(
2030                        "SELECT c.xid, c.id, c.data, c.exact,
2031                                COALESCE(s.scope_namespace, $4) AS namespace
2032                         FROM commits c
2033                         LEFT JOIN sessions s
2034                           ON s.tenant_id = c.tenant_id AND s.id = c.session_id
2035                         WHERE c.tenant_id = $1
2036                           AND (c.xid, c.id) > ($2, $3)
2037                           AND c.xid < pg_snapshot_xmin(pg_current_snapshot())::text::BIGINT
2038                         ORDER BY c.xid, c.id
2039                         LIMIT $5",
2040                        &[
2041                            &self.tenant_id,
2042                            &after_xid,
2043                            &after_id,
2044                            &HISTORY_NAMESPACE_UNKNOWN,
2045                            &batch_size,
2046                        ],
2047                    )
2048                    .await
2049                    .map_err(|e| history_error("select", &e))?;
2050                if !rows.is_empty() {
2051                    break rows;
2052                }
2053                // Nothing is old enough. If newer commits are waiting on an
2054                // older transaction, give it a moment to finish.
2055                let waiting: bool = tx
2056                    .query_one(
2057                        "SELECT EXISTS (SELECT 1 FROM commits
2058                                        WHERE tenant_id = $1 AND (xid, id) > ($2, $3))",
2059                        &[&self.tenant_id, &after_xid, &after_id],
2060                    )
2061                    .await
2062                    .map_err(|e| history_error("pending", &e))?
2063                    .get(0);
2064                if !waiting || tokio::time::Instant::now() >= deadline {
2065                    return Ok(0);
2066                }
2067                tokio::time::sleep(std::time::Duration::from_millis(5)).await;
2068            };
2069            let last = rows.last().expect("a non-empty batch");
2070            let (last_xid, last_id): (i64, Vec<u8>) = (last.get("xid"), last.get("id"));
2071
2072            let mut fold = HistoryFold::default();
2073            for row in &rows {
2074                let commit = commit_from_row(row)?;
2075                fold.add(&commit, row.get("namespace"));
2076            }
2077            fold.write(&tx, &self.tenant_id).await?;
2078
2079            // The cursor moves in the same transaction as the rows it covers.
2080            tx.execute(
2081                "UPDATE asg_history_cursor SET xid = $2, commit_id = $3, folded = folded + $4
2082                 WHERE tenant_id = $1",
2083                &[&self.tenant_id, &last_xid, &last_id, &(rows.len() as i64)],
2084            )
2085            .await
2086            .map_err(|e| history_error("cursor write", &e))?;
2087            tx.commit().await.map_err(|e| history_error("commit", &e))?;
2088            Ok(rows.len())
2089        })
2090    }
2091
2092    /// How many commits have been folded. (SQLite reports its last folded
2093    /// rowid; either way it only grows, and 0 means nothing is extracted.)
2094    fn history_cursor(&self) -> Result<i64, StorageError> {
2095        self.block_on(async {
2096            let client = self.client().await?;
2097            let row = client
2098                .query_opt(
2099                    "SELECT folded FROM asg_history_cursor WHERE tenant_id = $1",
2100                    &[&self.tenant_id],
2101                )
2102                .await
2103                .map_err(|e| history_error("cursor read", &e))?;
2104            Ok(row.map_or(0, |row| row.get(0)))
2105        })
2106    }
2107
2108    fn history_undistilled_commit_count(&self) -> Result<i64, StorageError> {
2109        self.block_on(async {
2110            let client = self.client().await?;
2111            let row = client
2112                .query_one(
2113                    "SELECT COUNT(*) FROM commits c
2114                     LEFT JOIN asg_history_cursor k ON k.tenant_id = c.tenant_id
2115                     WHERE c.tenant_id = $1
2116                       AND (c.xid, c.id) > (COALESCE(k.xid, 0), COALESCE(k.commit_id, ''::BYTEA))",
2117                    &[&self.tenant_id],
2118                )
2119                .await
2120                .map_err(|e| history_error("undistilled count", &e))?;
2121            Ok(row.get(0))
2122        })
2123    }
2124
2125    fn history_is_commit_distilled(&self, id: &ObjectId) -> Result<bool, StorageError> {
2126        self.block_on(async {
2127            let client = self.client().await?;
2128            let row = client
2129                .query_one(
2130                    "SELECT EXISTS (
2131                         SELECT 1 FROM commits c
2132                         JOIN asg_history_cursor k ON k.tenant_id = c.tenant_id
2133                         WHERE c.tenant_id = $1 AND c.id = $2
2134                           AND (c.xid, c.id) <= (k.xid, k.commit_id)
2135                     )",
2136                    &[&self.tenant_id, &id.as_bytes().as_slice()],
2137                )
2138                .await
2139                .map_err(|e| history_error("distilled", &e))?;
2140            Ok(row.get(0))
2141        })
2142    }
2143
2144    fn history_rollup(&self) -> Result<Vec<HistoryRollupRow>, StorageError> {
2145        self.block_on(async {
2146            let client = self.client().await?;
2147            let rows = client
2148                .query(
2149                    "SELECT day, namespace, agent_id, intent_category, commit_count,
2150                            first_ts, last_ts
2151                     FROM asg_history_commit_rollup
2152                     WHERE tenant_id = $1
2153                     ORDER BY day, namespace, agent_id, intent_category",
2154                    &[&self.tenant_id],
2155                )
2156                .await
2157                .map_err(|e| history_error("rollup read", &e))?;
2158            Ok(rows
2159                .iter()
2160                .map(|row| HistoryRollupRow {
2161                    day: row.get(0),
2162                    namespace: row.get(1),
2163                    agent_id: row.get(2),
2164                    intent_category: row.get(3),
2165                    commit_count: row.get(4),
2166                    first_ts: row.get(5),
2167                    last_ts: row.get(6),
2168                })
2169                .collect())
2170        })
2171    }
2172
2173    fn history_milestones(&self, limit: usize) -> Result<Vec<HistoryMilestoneRow>, StorageError> {
2174        self.block_on(async {
2175            let client = self.client().await?;
2176            let rows = client
2177                .query(
2178                    "SELECT commit_id, kind, timestamp, day, namespace, agent_id, description,
2179                            state_root, git_sha
2180                     FROM asg_history_milestone
2181                     WHERE tenant_id = $1
2182                     ORDER BY timestamp DESC
2183                     LIMIT $2",
2184                    &[&self.tenant_id, &sql_count(limit)],
2185                )
2186                .await
2187                .map_err(|e| history_error("milestones read", &e))?;
2188            Ok(rows
2189                .iter()
2190                .filter_map(|row| {
2191                    let commit_id = object_id_from_bytes(row.get(0))?;
2192                    let state_root: Option<&[u8]> = row.get(7);
2193                    Some(HistoryMilestoneRow {
2194                        commit_id,
2195                        kind: row.get(1),
2196                        timestamp: row.get(2),
2197                        day: row.get(3),
2198                        namespace: row.get(4),
2199                        agent_id: row.get(5),
2200                        description: row.get(6),
2201                        state_root: state_root.and_then(object_id_from_bytes),
2202                        git_sha: row.get(8),
2203                    })
2204                })
2205                .collect())
2206        })
2207    }
2208
2209    fn history_retained_state_roots(&self) -> Result<Vec<ObjectId>, StorageError> {
2210        self.block_on(async {
2211            let client = self.client().await?;
2212            let rows = client
2213                .query(
2214                    "SELECT DISTINCT state_root FROM asg_history_milestone
2215                     WHERE tenant_id = $1 AND state_root IS NOT NULL",
2216                    &[&self.tenant_id],
2217                )
2218                .await
2219                .map_err(|e| history_error("retained roots", &e))?;
2220            Ok(rows
2221                .iter()
2222                .filter_map(|row| object_id_from_bytes(row.get(0)))
2223                .collect())
2224        })
2225    }
2226
2227    fn history_gc_reachability(
2228        &self,
2229        roots: &[ObjectId],
2230        batch: usize,
2231    ) -> Result<GcReachability, StorageError> {
2232        let batch = sql_count(batch.max(1));
2233        self.block_on(async {
2234            // The mark lives in temp tables, so it runs on a connection of
2235            // its own that is closed afterwards rather than pooled.
2236            let client = self.client().await?;
2237            let result = async {
2238                gc_mark(&client, &self.tenant_id, roots, batch).await?;
2239                let row = client
2240                    .query_one(
2241                        "SELECT (SELECT count(*) FROM gc_mark),
2242                                (SELECT count(*) FROM objects WHERE tenant_id = $1)",
2243                        &[&self.tenant_id],
2244                    )
2245                    .await
2246                    .map_err(|e| gc_error("counts", &e))?;
2247                Ok::<_, StorageError>((row.get::<_, i64>(0), row.get::<_, i64>(1)))
2248            }
2249            .await;
2250            drop(deadpool_postgres::Object::take(client.into_inner()));
2251            let (live, total) = result?;
2252            Ok(GcReachability {
2253                total_objects: total,
2254                live_objects: live,
2255                reclaimable_objects: (total - live).max(0),
2256                roots_walked: roots.len(),
2257            })
2258        })
2259    }
2260
2261    /// Mark everything reachable from `roots`, the current ref tips and the
2262    /// pinned roots, then delete this tenant's other objects in batches, each
2263    /// its own transaction. Writers keep going meanwhile: whatever was written
2264    /// since the sweep started, or within the grace period before it, is
2265    /// spared, and each delete re-checks the write time, so an object stored
2266    /// again mid-sweep stays. `deleted_target` can therefore exceed
2267    /// `objects_deleted`.
2268    fn history_gc_sweep(&self, roots: &[ObjectId], batch: usize) -> Result<GcSweep, StorageError> {
2269        let batch = sql_count(batch.max(1));
2270        let held = self
2271            .gc_conn
2272            .lock()
2273            .map_err(|e| StorageError::Backend(e.to_string()))?
2274            .take();
2275        self.block_on(async {
2276            let (client, own) = match held {
2277                Some(client) => (client, false),
2278                None => (self.gc_lock().await?, true),
2279            };
2280            let result = gc_sweep(
2281                &client,
2282                &self.tenant_id,
2283                roots,
2284                batch,
2285                self.gc_grace.as_secs_f64(),
2286            )
2287            .await;
2288            if own {
2289                gc_release(client).await;
2290            } else {
2291                *self
2292                    .gc_conn
2293                    .lock()
2294                    .map_err(|e| StorageError::Backend(e.to_string()))? = Some(client);
2295            }
2296            result
2297        })
2298    }
2299
2300    /// Take this tenant's sweep lock, so two sweeps can't run at once. Unlike
2301    /// SQLite's, it doesn't hold writers off: the grace period keeps their
2302    /// work safe (see [`PostgresStorage::with_gc_grace`]).
2303    fn history_gc_lock_writers(&self) -> Result<(), StorageError> {
2304        let mut held = self
2305            .gc_conn
2306            .lock()
2307            .map_err(|e| StorageError::Backend(e.to_string()))?;
2308        if held.is_some() {
2309            return Err(StorageError::Backend(
2310                "gc lock writers: a sweep already holds the lock".into(),
2311            ));
2312        }
2313        *held = Some(self.block_on(self.gc_lock())?);
2314        Ok(())
2315    }
2316
2317    /// Release the sweep lock. A sweep's deletes are already committed batch
2318    /// by batch, so `commit` changes nothing here.
2319    fn history_gc_unlock_writers(&self, _commit: bool) -> Result<(), StorageError> {
2320        let held = self
2321            .gc_conn
2322            .lock()
2323            .map_err(|e| StorageError::Backend(e.to_string()))?
2324            .take();
2325        if let Some(client) = held {
2326            self.block_on(async {
2327                gc_release(client).await;
2328                Ok(())
2329            })?;
2330        }
2331        Ok(())
2332    }
2333
2334    fn gc_pin_root(&self, key: &str, root: &ObjectId) -> Result<(), StorageError> {
2335        self.block_on(async {
2336            let client = self.client().await?;
2337            client
2338                .execute(
2339                    "INSERT INTO gc_pending_roots (tenant_id, key, root, updated_at)
2340                     VALUES ($1, $2, $3, now())
2341                     ON CONFLICT (tenant_id, key)
2342                     DO UPDATE SET root = EXCLUDED.root, updated_at = now()",
2343                    &[&self.tenant_id, &key, &root.as_bytes().as_slice()],
2344                )
2345                .await
2346                .map_err(|e| gc_error("pin root", &e))?;
2347            Ok(())
2348        })
2349    }
2350
2351    fn gc_unpin_root(&self, key: &str) -> Result<(), StorageError> {
2352        self.block_on(async {
2353            let client = self.client().await?;
2354            client
2355                .execute(
2356                    "DELETE FROM gc_pending_roots WHERE tenant_id = $1 AND key = $2",
2357                    &[&self.tenant_id, &key],
2358                )
2359                .await
2360                .map_err(|e| gc_error("unpin root", &e))?;
2361            Ok(())
2362        })
2363    }
2364
2365    fn gc_pinned_roots(&self, max_age: std::time::Duration) -> Result<Vec<ObjectId>, StorageError> {
2366        self.block_on(async {
2367            let client = self.client().await?;
2368            gc_pinned(&client, &self.tenant_id, max_age.as_secs_f64()).await
2369        })
2370    }
2371
2372    /// Newest first, by the order the commits' transactions got ids (commits
2373    /// from before that was recorded come last, newest first by timestamp).
2374    fn commit_state_roots_recent_first(&self) -> Result<Vec<ObjectId>, StorageError> {
2375        self.block_on(async {
2376            let client = self.client().await?;
2377            let rows = client
2378                .query(
2379                    "SELECT data -> 'state_root' FROM commits WHERE tenant_id = $1
2380                     ORDER BY xid DESC, timestamp DESC, id DESC",
2381                    &[&self.tenant_id],
2382                )
2383                .await
2384                .map_err(|e| gc_error("commit roots", &e))?;
2385            rows.iter()
2386                .map(|row| {
2387                    serde_json::from_value(row.get(0))
2388                        .map_err(|e| StorageError::Serialization(e.to_string()))
2389                })
2390                .collect()
2391        })
2392    }
2393
2394    /// This tenant's object and commit counts, and the bytes its rows take
2395    /// in each table (before indexes and free space; the tables are shared
2396    /// with other tenants). Reads every row, so it is slow on a large store.
2397    /// This tenant's objects and commits. The tables are shared by tenants,
2398    /// so there is no size of the tenant's own to report.
2399    fn store_totals(&self) -> Result<Option<StoreTotals>, StorageError> {
2400        self.block_on(async {
2401            let client = self.client().await?;
2402            let row = client
2403                .query_one(
2404                    "SELECT (SELECT count(*) FROM objects WHERE tenant_id = $1),
2405                            (SELECT count(*) FROM commits WHERE tenant_id = $1)",
2406                    &[&self.tenant_id],
2407                )
2408                .await
2409                .map_err(|e| gc_error("store totals", &e))?;
2410            Ok(Some(StoreTotals {
2411                objects: row.get(0),
2412                commits: row.get(1),
2413                bytes: None,
2414            }))
2415        })
2416    }
2417
2418    fn history_store_shape(&self) -> Result<StoreShape, StorageError> {
2419        self.block_on(async {
2420            let client = self.client().await?;
2421            let row = client
2422                .query_one(
2423                    "SELECT (SELECT count(*) FROM objects WHERE tenant_id = $1),
2424                            (SELECT count(*) FROM commits WHERE tenant_id = $1),
2425                            (SELECT COALESCE(sum(pg_column_size(o.*)), 0)::BIGINT
2426                               FROM objects o WHERE tenant_id = $1),
2427                            (SELECT COALESCE(sum(pg_column_size(c.*)), 0)::BIGINT
2428                               FROM commits c WHERE tenant_id = $1)",
2429                    &[&self.tenant_id],
2430                )
2431                .await
2432                .map_err(|e| gc_error("store shape", &e))?;
2433            let (objects_bytes, commits_bytes): (i64, i64) = (row.get(2), row.get(3));
2434            let mut tables = vec![
2435                TableBytes {
2436                    name: "objects".into(),
2437                    bytes: objects_bytes,
2438                },
2439                TableBytes {
2440                    name: "commits".into(),
2441                    bytes: commits_bytes,
2442                },
2443            ];
2444            tables.sort_by_key(|t| std::cmp::Reverse(t.bytes));
2445            Ok(StoreShape {
2446                objects: row.get(0),
2447                commits: row.get(1),
2448                total_bytes: objects_bytes + commits_bytes,
2449                tables,
2450                dbstat_available: true,
2451            })
2452        })
2453    }
2454}
2455
2456impl PostgresStorage {
2457    /// A connection holding this tenant's sweep lock (a session advisory
2458    /// lock). Fails at once if another sweep holds it. Closing the connection
2459    /// releases the lock, so it is never returned to the pool.
2460    async fn gc_lock(&self) -> Result<PgClient, StorageError> {
2461        let client = self.client().await?;
2462        let locked: bool = client
2463            .query_one(
2464                "SELECT pg_try_advisory_lock(hashtextextended('asg-gc:' || $1, 0))",
2465                &[&self.tenant_id],
2466            )
2467            .await
2468            .map_err(|e| gc_error("lock", &e))?
2469            .get(0);
2470        if !locked {
2471            return Err(StorageError::Backend(
2472                "gc lock writers: another sweep is running on this store".into(),
2473            ));
2474        }
2475        Ok(client)
2476    }
2477}
2478
2479/// Release a sweep connection: unlock, then close it rather than pool it, so
2480/// its temp tables go too. The explicit unlock matters: the server only
2481/// notices a closed connection a moment later, and a sweep starting in that
2482/// moment would find the lock still held. (If the unlock fails, the
2483/// connection is broken and the server releases the lock with it.)
2484async fn gc_release(client: PgClient) {
2485    let _ = client
2486        .batch_execute("SELECT pg_advisory_unlock_all()")
2487        .await;
2488    drop(deadpool_postgres::Object::take(client.into_inner()));
2489}
2490
2491/// Fill the temp table `gc_mark` with every object of `tenant_id` reachable
2492/// from `roots`, breadth first in batches of `batch`, so the mark set lives in
2493/// the database rather than in memory. Each statement commits on its own: a
2494/// long transaction would hold back every tenant's history extraction.
2495async fn gc_mark(
2496    client: &PgClient,
2497    tenant_id: &str,
2498    roots: &[ObjectId],
2499    batch: i64,
2500) -> Result<(), StorageError> {
2501    client
2502        .batch_execute(
2503            "CREATE TEMP TABLE IF NOT EXISTS gc_mark (id BYTEA PRIMARY KEY);
2504             CREATE TEMP TABLE IF NOT EXISTS gc_frontier (id BYTEA PRIMARY KEY);
2505             TRUNCATE gc_mark, gc_frontier;",
2506        )
2507        .await
2508        .map_err(|e| gc_error("mark setup", &e))?;
2509    // Marks only ids that are stored objects, so the mark is a subset of the
2510    // tenant's objects; each newly marked id joins the frontier.
2511    let mark = "WITH marked AS (
2512                    INSERT INTO gc_mark (id)
2513                    SELECT o.id FROM objects o WHERE o.tenant_id = $1 AND o.id = ANY($2)
2514                    ON CONFLICT DO NOTHING
2515                    RETURNING id
2516                )
2517                INSERT INTO gc_frontier (id) SELECT id FROM marked";
2518    let seeds: Vec<&[u8]> = roots.iter().map(|r| r.as_bytes().as_slice()).collect();
2519    client
2520        .execute(mark, &[&tenant_id, &seeds])
2521        .await
2522        .map_err(|e| gc_error("mark roots", &e))?;
2523    loop {
2524        let rows = client
2525            .query(
2526                "WITH next AS (
2527                     DELETE FROM gc_frontier
2528                     WHERE id IN (SELECT id FROM gc_frontier LIMIT $2)
2529                     RETURNING id
2530                 )
2531                 SELECT o.data, o.exact FROM next
2532                 LEFT JOIN objects o ON o.tenant_id = $1 AND o.id = next.id",
2533                &[&tenant_id, &batch],
2534            )
2535            .await
2536            .map_err(|e| gc_error("mark frontier", &e))?;
2537        if rows.is_empty() {
2538            return Ok(());
2539        }
2540        let mut children = std::collections::HashSet::new();
2541        for row in &rows {
2542            let Some(data) = row.get::<_, Option<serde_json::Value>>(0) else {
2543                continue;
2544            };
2545            // The exact JSON when there is one: with U+FFFD in place of
2546            // U+0000, two of a map's keys can become one in `data`, and the
2547            // mark would miss a child.
2548            let obj: Object = from_row_json(data, row.get(1))?;
2549            children.extend(child_ids(&obj));
2550        }
2551        let children: Vec<&[u8]> = children.iter().map(|c| c.as_bytes().as_slice()).collect();
2552        client
2553            .execute(mark, &[&tenant_id, &children])
2554            .await
2555            .map_err(|e| gc_error("mark children", &e))?;
2556    }
2557}
2558
2559/// The body of [`PostgresStorage::history_gc_sweep`], on a connection that
2560/// holds the tenant's sweep lock.
2561async fn gc_sweep(
2562    client: &PgClient,
2563    tenant_id: &str,
2564    roots: &[ObjectId],
2565    batch: i64,
2566    grace_secs: f64,
2567) -> Result<GcSweep, StorageError> {
2568    // Fixed before anything is read: everything written after this time, or
2569    // within the grace period before it, is spared.
2570    let cutoff: DateTime<Utc> = client
2571        .query_one("SELECT now() - make_interval(secs => $1)", &[&grace_secs])
2572        .await
2573        .map_err(|e| gc_error("cutoff", &e))?
2574        .get(0);
2575    let count = || async {
2576        client
2577            .query_one(
2578                "SELECT count(*) FROM objects WHERE tenant_id = $1",
2579                &[&tenant_id],
2580            )
2581            .await
2582            .map(|row| row.get::<_, i64>(0))
2583            .map_err(|e| gc_error("count", &e))
2584    };
2585    let objects_before = count().await?;
2586
2587    // Whatever the caller's roots, keep every current ref tip and every
2588    // open speculation.
2589    let mut keep = roots.to_vec();
2590    let tips = client
2591        .query(
2592            "SELECT c.data -> 'state_root' FROM refs r
2593             JOIN commits c ON c.tenant_id = r.tenant_id AND c.id = r.target
2594             WHERE r.tenant_id = $1",
2595            &[&tenant_id],
2596        )
2597        .await
2598        .map_err(|e| gc_error("ref tips", &e))?;
2599    for row in &tips {
2600        keep.push(
2601            serde_json::from_value(row.get(0))
2602                .map_err(|e| StorageError::Serialization(e.to_string()))?,
2603        );
2604    }
2605    keep.extend(gc_pinned(client, tenant_id, GC_PIN_MAX_AGE.as_secs_f64()).await?);
2606
2607    gc_mark(client, tenant_id, &keep, batch).await?;
2608
2609    // Walk the tenant's objects in key order, a batch of unmarked ones at a
2610    // time, and delete each batch on its own. Each statement is short, so no
2611    // long write transaction holds back history extraction (every tenant's).
2612    // The delete re-checks the write time: an object stored again since the
2613    // batch was read stays.
2614    let mut after: Vec<u8> = Vec::new();
2615    let (mut deleted_target, mut objects_deleted) = (0, 0);
2616    loop {
2617        let dead: Vec<Vec<u8>> = client
2618            .query(
2619                "SELECT o.id FROM objects o
2620                 WHERE o.tenant_id = $1 AND o.id > $2 AND o.written_at < $3
2621                   AND NOT EXISTS (SELECT 1 FROM gc_mark m WHERE m.id = o.id)
2622                 ORDER BY o.id
2623                 LIMIT $4",
2624                &[&tenant_id, &after, &cutoff, &batch],
2625            )
2626            .await
2627            .map_err(|e| gc_error("dead batch", &e))?
2628            .iter()
2629            .map(|row| row.get(0))
2630            .collect();
2631        let Some(last) = dead.last() else {
2632            break;
2633        };
2634        after = last.clone();
2635        deleted_target += dead.len() as i64;
2636        objects_deleted += client
2637            .execute(
2638                "DELETE FROM objects
2639                 WHERE tenant_id = $1 AND id = ANY($2) AND written_at < $3",
2640                &[&tenant_id, &dead, &cutoff],
2641            )
2642            .await
2643            .map_err(|e| gc_error("delete", &e))? as i64;
2644    }
2645
2646    Ok(GcSweep {
2647        objects_before,
2648        objects_deleted,
2649        objects_after: count().await?,
2650        deleted_target,
2651    })
2652}
2653
2654async fn gc_pinned(
2655    client: &impl GenericClient,
2656    tenant_id: &str,
2657    max_age_secs: f64,
2658) -> Result<Vec<ObjectId>, StorageError> {
2659    let rows = client
2660        .query(
2661            "SELECT root FROM gc_pending_roots
2662             WHERE tenant_id = $1 AND updated_at >= now() - make_interval(secs => $2)",
2663            &[&tenant_id, &max_age_secs],
2664        )
2665        .await
2666        .map_err(|e| gc_error("pinned roots", &e))?;
2667    Ok(rows
2668        .iter()
2669        .filter_map(|row| object_id_from_bytes(row.get(0)))
2670        .collect())
2671}
2672
2673/// The child object ids a node references (none for atoms): the edges the
2674/// GC mark follows.
2675fn child_ids(obj: &Object) -> Vec<ObjectId> {
2676    match obj {
2677        Object::Node(Node::Map(entries)) => entries.values().copied().collect(),
2678        Object::Node(Node::List(items)) | Object::Node(Node::Set(items)) => items.clone(),
2679        Object::Atom(_) => Vec::new(),
2680    }
2681}
2682
2683/// How long an open speculation's pin protects its objects without being
2684/// refreshed, as in SQLite.
2685const GC_PIN_MAX_AGE: std::time::Duration = std::time::Duration::from_secs(24 * 60 * 60);
2686
2687fn gc_error(step: &str, e: &tokio_postgres::Error) -> StorageError {
2688    StorageError::Backend(with_causes(&format!("gc {step}"), e))
2689}
2690
2691async fn leaf_set_built(
2692    client: &impl GenericClient,
2693    tenant_id: &str,
2694    namespace: &str,
2695    ref_name: &str,
2696) -> Result<bool, StorageError> {
2697    let row = client
2698        .query_one(
2699            "SELECT EXISTS (SELECT 1 FROM leaf_index_refs
2700                            WHERE tenant_id = $1 AND namespace = $2 AND ref = $3)",
2701            &[&tenant_id, &namespace, &ref_name],
2702        )
2703        .await
2704        .map_err(|e| leaf_error("built check", &e))?;
2705    Ok(row.get(0))
2706}
2707
2708/// Write `(path, value)` leaves, replacing any value already at a path. A
2709/// path given twice keeps its last value.
2710async fn upsert_leaves(
2711    tx: &PgTransaction<'_>,
2712    tenant_id: &str,
2713    namespace: &str,
2714    ref_name: &str,
2715    leaves: &[(String, String)],
2716) -> Result<(), StorageError> {
2717    const CHUNK: usize = 1000;
2718    let latest: std::collections::HashMap<std::borrow::Cow<'_, str>, std::borrow::Cow<'_, str>> =
2719        leaves
2720            .iter()
2721            .map(|(path, value)| (leaf_text(path), leaf_text(value)))
2722            .collect();
2723    let latest: Vec<(&str, &str)> = latest
2724        .iter()
2725        .map(|(p, v)| (p.as_ref(), v.as_ref()))
2726        .collect();
2727    for chunk in latest.chunks(CHUNK) {
2728        let paths: Vec<&str> = chunk.iter().map(|(p, _)| *p).collect();
2729        let values: Vec<&str> = chunk.iter().map(|(_, v)| *v).collect();
2730        tx.execute(
2731            "INSERT INTO leaf_rows (tenant_id, namespace, ref, path, value)
2732             SELECT $1, $2, $3, l.path, l.value FROM unnest($4::TEXT[], $5::TEXT[]) AS l(path, value)
2733             ON CONFLICT (tenant_id, namespace, ref, path) DO UPDATE SET value = EXCLUDED.value",
2734            &[&tenant_id, &namespace, &ref_name, &paths, &values],
2735        )
2736        .await
2737        .map_err(|e| leaf_error("write", &e))?;
2738    }
2739    Ok(())
2740}
2741
2742/// An `ILIKE` pattern matching values that contain `needle` literally.
2743fn like_contains(needle: &str) -> String {
2744    let mut pattern = String::with_capacity(needle.len() + 2);
2745    pattern.push('%');
2746    for ch in needle.chars() {
2747        if matches!(ch, '%' | '_' | '\\') {
2748            pattern.push('\\');
2749        }
2750        // As the index keeps it (see leaf_text).
2751        pattern.push(if ch == '\0' { '\u{FFFD}' } else { ch });
2752    }
2753    pattern.push('%');
2754    pattern
2755}
2756
2757fn leaf_error(step: &str, e: &tokio_postgres::Error) -> StorageError {
2758    StorageError::Backend(with_causes(&format!("leaf index {step}"), e))
2759}
2760
2761/// How long the history extractor waits for an older transaction that newer
2762/// commits are waiting on. Writes take milliseconds; this only runs out when
2763/// some write transaction on the server stays open.
2764const HISTORY_WAIT: std::time::Duration = std::time::Duration::from_secs(2);
2765
2766/// A `usize` count or limit as the `BIGINT` Postgres compares it with.
2767fn sql_count(n: usize) -> i64 {
2768    i64::try_from(n).unwrap_or(i64::MAX)
2769}
2770
2771fn object_id_from_bytes(bytes: &[u8]) -> Option<ObjectId> {
2772    <[u8; 32]>::try_from(bytes).ok().map(ObjectId::from_bytes)
2773}
2774
2775/// A value as an `objects` or `commits` row holds it: `data`, and `exact`
2776/// when `data` can't hold it exactly.
2777///
2778/// JSONB has no U+0000 (Postgres rejects the `\u0000` escape), though an
2779/// object or commit may hold one in any string, a map key included. Such a
2780/// value goes in `data` with each U+0000 as U+FFFD, which keeps it queryable:
2781/// the queries that read `data`'s structure (`id`, `parents`, `state_root`)
2782/// see ids, which are never strings. Its exact JSON goes in `exact`, which
2783/// every read of the whole value prefers, so what comes back hashes to its id.
2784fn row_json<T: serde::Serialize>(
2785    value: &T,
2786) -> Result<(serde_json::Value, Option<Vec<u8>>), StorageError> {
2787    let mut data =
2788        serde_json::to_value(value).map_err(|e| StorageError::Serialization(e.to_string()))?;
2789    if !has_nul(&data) {
2790        return Ok((data, None));
2791    }
2792    let exact =
2793        serde_json::to_vec(value).map_err(|e| StorageError::Serialization(e.to_string()))?;
2794    replace_nul(&mut data);
2795    Ok((data, Some(exact)))
2796}
2797
2798fn has_nul(value: &serde_json::Value) -> bool {
2799    match value {
2800        serde_json::Value::String(s) => s.contains('\0'),
2801        serde_json::Value::Array(items) => items.iter().any(has_nul),
2802        serde_json::Value::Object(map) => map.iter().any(|(k, v)| k.contains('\0') || has_nul(v)),
2803        _ => false,
2804    }
2805}
2806
2807fn replace_nul(value: &mut serde_json::Value) {
2808    match value {
2809        serde_json::Value::String(s) => {
2810            if s.contains('\0') {
2811                *s = s.replace('\0', "\u{FFFD}");
2812            }
2813        }
2814        serde_json::Value::Array(items) => items.iter_mut().for_each(replace_nul),
2815        serde_json::Value::Object(map) => {
2816            let entries = std::mem::take(map);
2817            for (key, mut v) in entries {
2818                replace_nul(&mut v);
2819                map.insert(key.replace('\0', "\u{FFFD}"), v);
2820            }
2821        }
2822        _ => {}
2823    }
2824}
2825
2826/// The value a row holds: its `exact` JSON if it has one, else `data`.
2827fn from_row_json<T: serde::de::DeserializeOwned>(
2828    data: serde_json::Value,
2829    exact: Option<Vec<u8>>,
2830) -> Result<T, StorageError> {
2831    match exact {
2832        Some(bytes) => serde_json::from_slice(&bytes),
2833        None => serde_json::from_value(data),
2834    }
2835    .map_err(|e| StorageError::Serialization(e.to_string()))
2836}
2837
2838/// A commit from a row that selected its `data` and `exact` columns.
2839fn commit_from_row(row: &tokio_postgres::Row) -> Result<Commit, StorageError> {
2840    from_row_json(row.get("data"), row.get("exact"))
2841}
2842
2843/// Text for the search index. Postgres TEXT can't hold U+0000 either, so the
2844/// index keeps U+FFFD in its place; it's rebuilt from the tree, and no search
2845/// can ask for U+0000.
2846fn leaf_text(s: &str) -> std::borrow::Cow<'_, str> {
2847    if s.contains('\0') {
2848        std::borrow::Cow::Owned(s.replace('\0', "\u{FFFD}"))
2849    } else {
2850        std::borrow::Cow::Borrowed(s)
2851    }
2852}
2853
2854fn history_error(step: &str, e: &tokio_postgres::Error) -> StorageError {
2855    StorageError::Backend(with_causes(&format!("history {step}"), e))
2856}
2857
2858/// A rollup bucket's key: (day, namespace, agent, intent category).
2859type BucketKey = (String, String, String, String);
2860
2861/// A rollup bucket's commit count and first and last timestamps.
2862type Bucket = (i64, String, String);
2863
2864/// One batch of commits folded into rollup buckets and milestones, written
2865/// with one statement each.
2866#[derive(Default)]
2867struct HistoryFold {
2868    rollup: std::collections::BTreeMap<BucketKey, Bucket>,
2869    milestones: Vec<Milestone>,
2870}
2871
2872struct Milestone {
2873    commit_id: Vec<u8>,
2874    timestamp: String,
2875    day: String,
2876    namespace: String,
2877    agent_id: String,
2878    description: String,
2879    state_root: Option<Vec<u8>>,
2880    git_sha: Option<String>,
2881}
2882
2883impl HistoryFold {
2884    /// The same fields, bucketing and milestone rules as SQLite's extractor.
2885    fn add(&mut self, commit: &Commit, namespace: String) {
2886        let day = commit.timestamp.format("%Y-%m-%d").to_string();
2887        let ts = commit.timestamp.to_rfc3339();
2888        let key = (
2889            day.clone(),
2890            namespace.clone(),
2891            commit.agent_id.clone(),
2892            format!("{:?}", commit.intent.category),
2893        );
2894        let bucket = self
2895            .rollup
2896            .entry(key)
2897            .or_insert_with(|| (0, ts.clone(), ts.clone()));
2898        bucket.0 += 1;
2899        if ts < bucket.1 {
2900            bucket.1 = ts.clone();
2901        }
2902        if ts > bucket.2 {
2903            bucket.2 = ts.clone();
2904        }
2905
2906        if commit.intent.category == IntentCategory::Checkpoint {
2907            // Pin the snapshot only when the checkpoint asks to (see SQLite).
2908            let state_root = commit
2909                .intent
2910                .tags
2911                .iter()
2912                .any(|t| t == TAG_PIN_STATE)
2913                .then(|| commit.state_root.as_bytes().to_vec());
2914            let git_sha = commit
2915                .intent
2916                .tags
2917                .iter()
2918                .find_map(|t| t.strip_prefix(TAG_GIT_REVISION))
2919                .filter(|sha| !sha.is_empty())
2920                .map(str::to_string);
2921            self.milestones.push(Milestone {
2922                commit_id: commit.id.as_bytes().to_vec(),
2923                timestamp: ts,
2924                day,
2925                namespace,
2926                agent_id: commit.agent_id.clone(),
2927                description: commit.intent.description.clone(),
2928                state_root,
2929                git_sha,
2930            });
2931        }
2932    }
2933
2934    async fn write(self, tx: &PgTransaction<'_>, tenant_id: &str) -> Result<(), StorageError> {
2935        let mut days = Vec::new();
2936        let mut namespaces = Vec::new();
2937        let mut agents = Vec::new();
2938        let mut categories = Vec::new();
2939        let mut counts = Vec::new();
2940        let mut firsts = Vec::new();
2941        let mut lasts = Vec::new();
2942        for ((day, namespace, agent, category), (count, first, last)) in self.rollup {
2943            days.push(day);
2944            namespaces.push(namespace);
2945            agents.push(agent);
2946            categories.push(category);
2947            counts.push(count);
2948            firsts.push(first);
2949            lasts.push(last);
2950        }
2951        tx.execute(
2952            "INSERT INTO asg_history_commit_rollup AS r
2953                 (tenant_id, day, namespace, agent_id, intent_category, commit_count,
2954                  first_ts, last_ts)
2955             SELECT $1, * FROM unnest($2::TEXT[], $3::TEXT[], $4::TEXT[], $5::TEXT[],
2956                                      $6::BIGINT[], $7::TEXT[], $8::TEXT[])
2957             ON CONFLICT (tenant_id, day, namespace, agent_id, intent_category) DO UPDATE SET
2958                 commit_count = r.commit_count + EXCLUDED.commit_count,
2959                 first_ts = LEAST(r.first_ts, EXCLUDED.first_ts),
2960                 last_ts = GREATEST(r.last_ts, EXCLUDED.last_ts)",
2961            &[
2962                &tenant_id,
2963                &days,
2964                &namespaces,
2965                &agents,
2966                &categories,
2967                &counts,
2968                &firsts,
2969                &lasts,
2970            ],
2971        )
2972        .await
2973        .map_err(|e| history_error("rollup upsert", &e))?;
2974
2975        if self.milestones.is_empty() {
2976            return Ok(());
2977        }
2978        let m = &self.milestones;
2979        let ids: Vec<&[u8]> = m.iter().map(|m| m.commit_id.as_slice()).collect();
2980        let timestamps: Vec<&str> = m.iter().map(|m| m.timestamp.as_str()).collect();
2981        let days: Vec<&str> = m.iter().map(|m| m.day.as_str()).collect();
2982        let namespaces: Vec<&str> = m.iter().map(|m| m.namespace.as_str()).collect();
2983        let agents: Vec<&str> = m.iter().map(|m| m.agent_id.as_str()).collect();
2984        let descriptions: Vec<&str> = m.iter().map(|m| m.description.as_str()).collect();
2985        let roots: Vec<Option<&[u8]>> = m.iter().map(|m| m.state_root.as_deref()).collect();
2986        let shas: Vec<Option<&str>> = m.iter().map(|m| m.git_sha.as_deref()).collect();
2987        tx.execute(
2988            "INSERT INTO asg_history_milestone
2989                 (tenant_id, commit_id, kind, timestamp, day, namespace, agent_id,
2990                  description, state_root, git_sha)
2991             SELECT $1, m.id, 'checkpoint', m.ts, m.day, m.ns, m.agent, m.description,
2992                    m.root, m.sha
2993             FROM unnest($2::BYTEA[], $3::TEXT[], $4::TEXT[], $5::TEXT[], $6::TEXT[],
2994                         $7::TEXT[], $8::BYTEA[], $9::TEXT[])
2995                  AS m(id, ts, day, ns, agent, description, root, sha)
2996             ON CONFLICT DO NOTHING",
2997            &[
2998                &tenant_id,
2999                &ids,
3000                &timestamps,
3001                &days,
3002                &namespaces,
3003                &agents,
3004                &descriptions,
3005                &roots,
3006                &shas,
3007            ],
3008        )
3009        .await
3010        .map_err(|e| history_error("milestones", &e))?;
3011        Ok(())
3012    }
3013}
3014
3015// ─── RefStore ───────────────────────────────────────────────
3016
3017impl RefStore for PostgresStorage {
3018    fn create_namespace(&self, namespace: &Namespace) -> Result<(), StorageError> {
3019        self.block_on(async {
3020            let client = self.client().await?;
3021
3022            let rows = client
3023                .execute(
3024                    "INSERT INTO namespaces (tenant_id, name, created_at)
3025                     VALUES ($1, $2, NOW()::TEXT)
3026                     ON CONFLICT DO NOTHING",
3027                    &[&self.tenant_id, &namespace.as_str()],
3028                )
3029                .await
3030                .map_err(|e| StorageError::Backend(format!("create namespace: {}", e)))?;
3031            if rows == 0 {
3032                return Err(StorageError::NamespaceAlreadyExists(
3033                    namespace.as_str().to_string(),
3034                ));
3035            }
3036            Ok(())
3037        })
3038    }
3039
3040    fn list_namespaces(&self) -> Result<Vec<Namespace>, StorageError> {
3041        self.block_on(async {
3042            let client = self.client().await?;
3043
3044            let rows = client
3045                .query(
3046                    "SELECT name FROM namespaces WHERE tenant_id = $1 ORDER BY name",
3047                    &[&self.tenant_id],
3048                )
3049                .await
3050                .map_err(|e| StorageError::Backend(format!("list namespaces: {}", e)))?;
3051
3052            let mut result = Vec::new();
3053            for row in rows {
3054                let name: String = row.get("name");
3055                let ns = Namespace::new(name).map_err(|e| {
3056                    StorageError::Backend(format!("invalid namespace in db: {}", e))
3057                })?;
3058                result.push(ns);
3059            }
3060            Ok(result)
3061        })
3062    }
3063
3064    fn get_ref(&self, namespace: &Namespace, name: &str) -> Result<Option<ObjectId>, StorageError> {
3065        self.block_on(async {
3066            let client = self.client().await?;
3067
3068            let ns_row = client
3069                .query_opt(
3070                    "SELECT 1 FROM namespaces WHERE tenant_id = $1 AND name = $2",
3071                    &[&self.tenant_id, &namespace.as_str()],
3072                )
3073                .await
3074                .map_err(|e| StorageError::Backend(format!("check namespace: {}", e)))?;
3075            if ns_row.is_none() {
3076                return Err(StorageError::NamespaceNotFound(
3077                    namespace.as_str().to_string(),
3078                ));
3079            }
3080
3081            let row = client
3082                .query_opt(
3083                    "SELECT target FROM refs
3084                     WHERE tenant_id = $1 AND namespace = $2 AND name = $3",
3085                    &[&self.tenant_id, &namespace.as_str(), &name],
3086                )
3087                .await
3088                .map_err(|e| StorageError::Backend(format!("get ref: {}", e)))?;
3089
3090            match row {
3091                Some(row) => {
3092                    let bytes: Vec<u8> = row.get("target");
3093                    let mut arr = [0u8; 32];
3094                    if bytes.len() == 32 {
3095                        arr.copy_from_slice(&bytes);
3096                    }
3097                    Ok(Some(ObjectId::from_bytes(arr)))
3098                }
3099                None => Ok(None),
3100            }
3101        })
3102    }
3103
3104    fn set_ref(
3105        &self,
3106        namespace: &Namespace,
3107        name: &str,
3108        target: ObjectId,
3109    ) -> Result<(), StorageError> {
3110        self.block_on(async {
3111            let client = self.client().await?;
3112
3113            let ns_row = client
3114                .query_opt(
3115                    "SELECT 1 FROM namespaces WHERE tenant_id = $1 AND name = $2",
3116                    &[&self.tenant_id, &namespace.as_str()],
3117                )
3118                .await
3119                .map_err(|e| StorageError::Backend(format!("check namespace: {}", e)))?;
3120            if ns_row.is_none() {
3121                return Err(StorageError::NamespaceNotFound(
3122                    namespace.as_str().to_string(),
3123                ));
3124            }
3125
3126            client
3127                .execute(
3128                    "INSERT INTO refs (tenant_id, namespace, name, target)
3129                     VALUES ($1, $2, $3, $4)
3130                     ON CONFLICT (tenant_id, namespace, name) DO UPDATE SET target = $4",
3131                    &[
3132                        &self.tenant_id,
3133                        &namespace.as_str(),
3134                        &name,
3135                        &target.as_bytes().as_slice(),
3136                    ],
3137                )
3138                .await
3139                .map_err(|e| StorageError::Backend(format!("set ref: {}", e)))?;
3140
3141            Ok(())
3142        })
3143    }
3144
3145    fn cas_ref(
3146        &self,
3147        namespace: &Namespace,
3148        name: &str,
3149        expected: ObjectId,
3150        new: ObjectId,
3151    ) -> Result<bool, StorageError> {
3152        self.block_on(async {
3153            let client = self.client().await?;
3154
3155            let ns_row = client
3156                .query_opt(
3157                    "SELECT 1 FROM namespaces WHERE tenant_id = $1 AND name = $2",
3158                    &[&self.tenant_id, &namespace.as_str()],
3159                )
3160                .await
3161                .map_err(|e| StorageError::Backend(format!("check namespace: {}", e)))?;
3162            if ns_row.is_none() {
3163                return Err(StorageError::NamespaceNotFound(
3164                    namespace.as_str().to_string(),
3165                ));
3166            }
3167
3168            let rows = client
3169                .execute(
3170                    "UPDATE refs SET target = $1
3171                     WHERE tenant_id = $2 AND namespace = $3
3172                       AND name = $4 AND target = $5",
3173                    &[
3174                        &new.as_bytes().as_slice(),
3175                        &self.tenant_id,
3176                        &namespace.as_str(),
3177                        &name,
3178                        &expected.as_bytes().as_slice(),
3179                    ],
3180                )
3181                .await
3182                .map_err(|e| StorageError::Backend(format!("cas ref: {}", e)))?;
3183
3184            Ok(rows > 0)
3185        })
3186    }
3187
3188    fn create_ref(
3189        &self,
3190        namespace: &Namespace,
3191        name: &str,
3192        target: ObjectId,
3193    ) -> Result<bool, StorageError> {
3194        self.block_on(async {
3195            let client = self.client().await?;
3196            self.require_namespace(&client, namespace).await?;
3197            let rows = client
3198                .execute(
3199                    "INSERT INTO refs (tenant_id, namespace, name, target)
3200                     VALUES ($1, $2, $3, $4)
3201                     ON CONFLICT DO NOTHING",
3202                    &[
3203                        &self.tenant_id,
3204                        &namespace.as_str(),
3205                        &name,
3206                        &target.as_bytes().as_slice(),
3207                    ],
3208                )
3209                .await
3210                .map_err(|e| StorageError::Backend(format!("create ref: {}", e)))?;
3211            Ok(rows > 0)
3212        })
3213    }
3214
3215    fn cas_delete_ref(
3216        &self,
3217        namespace: &Namespace,
3218        name: &str,
3219        expected: ObjectId,
3220    ) -> Result<bool, StorageError> {
3221        self.block_on(async {
3222            let client = self.client().await?;
3223            self.require_namespace(&client, namespace).await?;
3224            let rows = client
3225                .execute(
3226                    "DELETE FROM refs
3227                     WHERE tenant_id = $1 AND namespace = $2
3228                       AND name = $3 AND target = $4",
3229                    &[
3230                        &self.tenant_id,
3231                        &namespace.as_str(),
3232                        &name,
3233                        &expected.as_bytes().as_slice(),
3234                    ],
3235                )
3236                .await
3237                .map_err(|e| StorageError::Backend(format!("cas delete ref: {}", e)))?;
3238            Ok(rows > 0)
3239        })
3240    }
3241
3242    fn list_refs(
3243        &self,
3244        namespace: &Namespace,
3245        prefix: &str,
3246    ) -> Result<Vec<(String, ObjectId)>, StorageError> {
3247        self.block_on(async {
3248            let client = self.client().await?;
3249
3250            let ns_row = client
3251                .query_opt(
3252                    "SELECT 1 FROM namespaces WHERE tenant_id = $1 AND name = $2",
3253                    &[&self.tenant_id, &namespace.as_str()],
3254                )
3255                .await
3256                .map_err(|e| StorageError::Backend(format!("check namespace: {}", e)))?;
3257            if ns_row.is_none() {
3258                return Err(StorageError::NamespaceNotFound(
3259                    namespace.as_str().to_string(),
3260                ));
3261            }
3262
3263            let pattern = format!("{}%", prefix);
3264            let rows = client
3265                .query(
3266                    "SELECT name, target FROM refs
3267                     WHERE tenant_id = $1 AND namespace = $2 AND name LIKE $3
3268                     ORDER BY name",
3269                    &[&self.tenant_id, &namespace.as_str(), &pattern],
3270                )
3271                .await
3272                .map_err(|e| StorageError::Backend(format!("list refs: {}", e)))?;
3273
3274            let mut result = Vec::new();
3275            for row in rows {
3276                let name: String = row.get("name");
3277                let bytes: Vec<u8> = row.get("target");
3278                let mut arr = [0u8; 32];
3279                if bytes.len() == 32 {
3280                    arr.copy_from_slice(&bytes);
3281                }
3282                result.push((name, ObjectId::from_bytes(arr)));
3283            }
3284
3285            Ok(result)
3286        })
3287    }
3288
3289    fn delete_ref(&self, namespace: &Namespace, name: &str) -> Result<bool, StorageError> {
3290        self.block_on(async {
3291            let client = self.client().await?;
3292
3293            let ns_row = client
3294                .query_opt(
3295                    "SELECT 1 FROM namespaces WHERE tenant_id = $1 AND name = $2",
3296                    &[&self.tenant_id, &namespace.as_str()],
3297                )
3298                .await
3299                .map_err(|e| StorageError::Backend(format!("check namespace: {}", e)))?;
3300            if ns_row.is_none() {
3301                return Err(StorageError::NamespaceNotFound(
3302                    namespace.as_str().to_string(),
3303                ));
3304            }
3305
3306            let rows = client
3307                .execute(
3308                    "DELETE FROM refs WHERE tenant_id = $1 AND namespace = $2 AND name = $3",
3309                    &[&self.tenant_id, &namespace.as_str(), &name],
3310                )
3311                .await
3312                .map_err(|e| StorageError::Backend(format!("delete ref: {}", e)))?;
3313
3314            Ok(rows > 0)
3315        })
3316    }
3317
3318    fn delete_namespace(&self, namespace: &Namespace) -> Result<bool, StorageError> {
3319        if namespace.as_str() == Namespace::DEFAULT {
3320            return Err(StorageError::InvalidOperation(
3321                "cannot delete the 'default' namespace".to_string(),
3322            ));
3323        }
3324        let ns = namespace.as_str().to_string();
3325        let tid = self.tenant_id.clone();
3326        self.block_on(async {
3327            let mut client = self.client().await?;
3328            // Refs and the namespace row go together or not at all; separately,
3329            // a failure in between left refs deleted under a namespace that
3330            // still existed.
3331            let tx = client
3332                .transaction()
3333                .await
3334                .map_err(|e| StorageError::Backend(format!("begin delete namespace: {e}")))?;
3335            tx.execute(
3336                "DELETE FROM refs WHERE tenant_id = $1 AND namespace = $2",
3337                &[&tid, &ns],
3338            )
3339            .await
3340            .map_err(|e| StorageError::Backend(format!("delete namespace refs: {e}")))?;
3341            let rows = tx
3342                .execute(
3343                    "DELETE FROM namespaces WHERE tenant_id = $1 AND name = $2",
3344                    &[&tid, &ns],
3345                )
3346                .await
3347                .map_err(|e| StorageError::Backend(format!("delete namespace: {e}")))?;
3348            tx.commit()
3349                .await
3350                .map_err(|e| StorageError::Backend(format!("commit delete namespace: {e}")))?;
3351            Ok(rows > 0)
3352        })
3353    }
3354}
3355
3356// ---------------------------------------------------------------------------
3357// Helpers for Epoch / Session row assembly. Parallel to the SQLite backend.
3358// ---------------------------------------------------------------------------
3359
3360fn epoch_status_str(s: &EpochStatus) -> &'static str {
3361    match s {
3362        EpochStatus::Active => "Active",
3363        EpochStatus::Sealed => "Sealed",
3364        EpochStatus::Archived => "Archived",
3365    }
3366}
3367
3368fn parse_epoch_status(s: &str) -> EpochStatus {
3369    match s {
3370        "Sealed" => EpochStatus::Sealed,
3371        "Archived" => EpochStatus::Archived,
3372        _ => EpochStatus::Active,
3373    }
3374}
3375
3376fn parse_rfc3339(s: &str) -> Result<DateTime<Utc>, StorageError> {
3377    DateTime::parse_from_rfc3339(s)
3378        .map(|dt| dt.with_timezone(&Utc))
3379        .map_err(|e| StorageError::Serialization(format!("timestamp parse: {}", e)))
3380}
3381
3382#[allow(clippy::too_many_arguments)]
3383fn row_to_epoch(
3384    id: String,
3385    description: String,
3386    status: String,
3387    created_at: String,
3388    sealed_at: Option<String>,
3389    summary: Option<String>,
3390    root_intents: String,
3391    agents: String,
3392    tags: String,
3393    sealed_commits: String,
3394    seal_hash: Option<Vec<u8>>,
3395    namespace: Option<String>,
3396    scope: String,
3397) -> Result<Epoch, StorageError> {
3398    let root_intents: Vec<String> = serde_json::from_str(&root_intents)
3399        .map_err(|e| StorageError::Serialization(format!("root_intents: {}", e)))?;
3400    let agents: Vec<String> = serde_json::from_str(&agents)
3401        .map_err(|e| StorageError::Serialization(format!("agents: {}", e)))?;
3402    let tags: Vec<String> = serde_json::from_str(&tags)
3403        .map_err(|e| StorageError::Serialization(format!("tags: {}", e)))?;
3404    let sealed_commits: Vec<ObjectId> = serde_json::from_str(&sealed_commits)
3405        .map_err(|e| StorageError::Serialization(format!("sealed_commits: {}", e)))?;
3406
3407    Ok(Epoch {
3408        id,
3409        description,
3410        root_intents,
3411        status: parse_epoch_status(&status),
3412        created_at: parse_rfc3339(&created_at)?,
3413        sealed_at: sealed_at.as_deref().map(parse_rfc3339).transpose()?,
3414        seal_summary: summary,
3415        // A 32-byte digest or nothing; a short or corrupt value reads as
3416        // absent rather than failing the whole read, as in SQLite.
3417        seal_hash: seal_hash.and_then(|b| {
3418            <[u8; 32]>::try_from(b.as_slice())
3419                .ok()
3420                .map(ObjectId::from_bytes)
3421        }),
3422        commits: Vec::new(),
3423        agents,
3424        branches: Vec::new(),
3425        scope: EpochScope::from_storage_str(&scope),
3426        namespace,
3427        tags,
3428        sealed_commits,
3429    })
3430}
3431
3432/// `commit_count` is an `INTEGER` column, so it must be bound as `i32`:
3433/// binding an `i64` fails every statement ("error serializing parameter").
3434fn commit_count_param(n: usize) -> i32 {
3435    i32::try_from(n).unwrap_or(i32::MAX)
3436}
3437
3438/// The columns `row_to_epoch` reads, in the order it takes them.
3439const EPOCH_COLUMNS: &str = "id, description, status, created_at, sealed_at, summary,
3440     root_intents, agents, tags, sealed_commits, seal_hash, namespace, scope";
3441
3442fn epoch_from_row(row: &tokio_postgres::Row) -> Result<Epoch, StorageError> {
3443    row_to_epoch(
3444        row.get("id"),
3445        row.get("description"),
3446        row.get("status"),
3447        row.get("created_at"),
3448        row.get("sealed_at"),
3449        row.get("summary"),
3450        row.get("root_intents"),
3451        row.get("agents"),
3452        row.get("tags"),
3453        row.get("sealed_commits"),
3454        row.get("seal_hash"),
3455        row.get("namespace"),
3456        row.get("scope"),
3457    )
3458}
3459
3460#[allow(clippy::too_many_arguments)]
3461fn row_to_session(
3462    id: String,
3463    agent_id: String,
3464    parent_id: Option<String>,
3465    scope_path: Option<String>,
3466    scope_branch: Option<String>,
3467    scope_namespace: Option<String>,
3468    status: String,
3469    created_at: String,
3470    ended_at: Option<String>,
3471    metadata: String,
3472) -> Result<Session, StorageError> {
3473    #[derive(serde::Deserialize, Default)]
3474    struct Meta {
3475        #[serde(default)]
3476        head: Option<ObjectId>,
3477        #[serde(default)]
3478        delegated_intent: Option<String>,
3479        #[serde(default)]
3480        report_to: Option<String>,
3481        #[serde(default)]
3482        working_branch: Option<String>,
3483        #[serde(default)]
3484        scope_tenant: Option<String>,
3485    }
3486    let meta: Meta = serde_json::from_str(&metadata).unwrap_or_default();
3487    let head = meta.head.unwrap_or_else(|| ObjectId::hash(b""));
3488    let working_branch = meta
3489        .working_branch
3490        .unwrap_or_else(|| scope_branch.clone().unwrap_or_default());
3491    Ok(Session {
3492        id,
3493        agent_id,
3494        working_branch,
3495        head,
3496        parent_session: parent_id,
3497        delegated_intent: meta.delegated_intent,
3498        report_to: meta.report_to,
3499        path_scope: scope_path,
3500        scope_tenant: meta.scope_tenant,
3501        scope_namespace: scope_namespace
3502            .as_deref()
3503            .and_then(|s| Namespace::new(s).ok()),
3504        status: SessionStatus::from_wire(&status),
3505        created_at: parse_rfc3339(&created_at)?,
3506        ended_at: ended_at.as_deref().map(parse_rfc3339).transpose()?,
3507    })
3508}
3509
3510// ---------------------------------------------------------------------------
3511// EpochStore
3512// ---------------------------------------------------------------------------
3513
3514impl EpochStore for PostgresStorage {
3515    fn create_epoch(&self, epoch: &Epoch) -> Result<(), StorageError> {
3516        let root_intents = serde_json::to_string(&epoch.root_intents)
3517            .map_err(|e| StorageError::Serialization(e.to_string()))?;
3518        let agents = serde_json::to_string(&epoch.agents)
3519            .map_err(|e| StorageError::Serialization(e.to_string()))?;
3520        let tags = serde_json::to_string(&epoch.tags)
3521            .map_err(|e| StorageError::Serialization(e.to_string()))?;
3522        let sealed_commits = serde_json::to_string(&epoch.sealed_commits)
3523            .map_err(|e| StorageError::Serialization(e.to_string()))?;
3524
3525        self.block_on(async {
3526            let client = self.client().await?;
3527
3528            client
3529                .execute(
3530                    "INSERT INTO epochs
3531                     (tenant_id, id, description, status, created_at, sealed_at,
3532                      summary, root_intents, agents, tags, commit_count, sealed_commits,
3533                      namespace, scope)
3534                     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)",
3535                    &[
3536                        &self.tenant_id,
3537                        &epoch.id,
3538                        &epoch.description,
3539                        &epoch_status_str(&epoch.status),
3540                        &epoch.created_at.to_rfc3339(),
3541                        &epoch.sealed_at.map(|t| t.to_rfc3339()),
3542                        &epoch.seal_summary,
3543                        &root_intents,
3544                        &agents,
3545                        &tags,
3546                        &commit_count_param(epoch.commits.len()),
3547                        &sealed_commits,
3548                        &epoch.namespace,
3549                        &epoch.scope.as_storage_str(),
3550                    ],
3551                )
3552                .await
3553                .map_err(|e| StorageError::Backend(format!("create epoch: {}", e)))?;
3554            Ok(())
3555        })
3556    }
3557
3558    fn seal_epoch(
3559        &self,
3560        id: &str,
3561        summary: &str,
3562        sealed_at: DateTime<Utc>,
3563        sealed_commits: &[ObjectId],
3564        seal_hash: &ObjectId,
3565    ) -> Result<(), StorageError> {
3566        let sc_json = serde_json::to_string(sealed_commits)
3567            .map_err(|e| StorageError::Serialization(e.to_string()))?;
3568
3569        self.block_on(async {
3570            let client = self.client().await?;
3571
3572            // One conditional UPDATE, so two sealers can't both pass a status
3573            // check and overwrite each other's seal.
3574            let sealed = client
3575                .execute(
3576                    "UPDATE epochs
3577                     SET status = 'Sealed', sealed_at = $1, summary = $2,
3578                         sealed_commits = $3, seal_hash = $4, commit_count = $5
3579                     WHERE tenant_id = $6 AND id = $7
3580                       AND status NOT IN ('Sealed', 'Archived')",
3581                    &[
3582                        &sealed_at.to_rfc3339(),
3583                        &summary,
3584                        &sc_json,
3585                        &seal_hash.as_bytes().as_slice(),
3586                        &commit_count_param(sealed_commits.len()),
3587                        &self.tenant_id,
3588                        &id,
3589                    ],
3590                )
3591                .await
3592                .map_err(|e| StorageError::Backend(format!("seal epoch: {}", e)))?;
3593            if sealed > 0 {
3594                return Ok(());
3595            }
3596            let exists = client
3597                .query_opt(
3598                    "SELECT 1 FROM epochs WHERE tenant_id = $1 AND id = $2",
3599                    &[&self.tenant_id, &id],
3600                )
3601                .await
3602                .map_err(|e| StorageError::Backend(format!("seal lookup: {}", e)))?;
3603            match exists {
3604                Some(_) => Err(StorageError::EpochAlreadySealed { id: id.to_string() }),
3605                None => Err(StorageError::Backend(format!("epoch not found: {}", id))),
3606            }
3607        })
3608    }
3609
3610    fn assign_epoch_namespace(&self, id: &str, namespace: &str) -> Result<bool, StorageError> {
3611        self.block_on(async {
3612            let client = self.client().await?;
3613            // `namespace IS NULL` makes this assign-only — see the trait docs.
3614            let changed = client
3615                .execute(
3616                    "UPDATE epochs SET namespace = $1
3617                     WHERE tenant_id = $2 AND id = $3 AND namespace IS NULL",
3618                    &[&namespace, &self.tenant_id, &id],
3619                )
3620                .await
3621                .map_err(|e| StorageError::Backend(format!("assign epoch namespace: {}", e)))?;
3622            if changed > 0 {
3623                return Ok(true);
3624            }
3625            let row = client
3626                .query_opt(
3627                    "SELECT 1 FROM epochs WHERE tenant_id = $1 AND id = $2",
3628                    &[&self.tenant_id, &id],
3629                )
3630                .await
3631                .map_err(|e| StorageError::Backend(format!("assign epoch lookup: {}", e)))?;
3632            match row {
3633                Some(_) => Ok(false),
3634                None => Err(StorageError::Backend(format!("epoch not found: {}", id))),
3635            }
3636        })
3637    }
3638
3639    fn list_epochs(&self) -> Result<Vec<Epoch>, StorageError> {
3640        self.block_on(async {
3641            let client = self.client().await?;
3642
3643            let rows = client
3644                .query(
3645                    &format!(
3646                        "SELECT {EPOCH_COLUMNS} FROM epochs
3647                         WHERE tenant_id = $1
3648                         ORDER BY created_at DESC"
3649                    ),
3650                    &[&self.tenant_id],
3651                )
3652                .await
3653                .map_err(|e| StorageError::Backend(format!("list epochs: {}", e)))?;
3654            rows.iter().map(epoch_from_row).collect()
3655        })
3656    }
3657
3658    fn get_epoch(&self, id: &str) -> Result<Option<Epoch>, StorageError> {
3659        self.block_on(async {
3660            let client = self.client().await?;
3661
3662            let row = client
3663                .query_opt(
3664                    &format!("SELECT {EPOCH_COLUMNS} FROM epochs WHERE tenant_id = $1 AND id = $2"),
3665                    &[&self.tenant_id, &id],
3666                )
3667                .await
3668                .map_err(|e| StorageError::Backend(format!("get epoch: {}", e)))?;
3669
3670            match row {
3671                None => Ok(None),
3672                Some(row) => {
3673                    let mut epoch = epoch_from_row(&row)?;
3674                    // Rehydrate the commit list in commit order. `commits` has
3675                    // no `created_at`; ordering by it failed every call. The id
3676                    // breaks ties between commits made in the same instant.
3677                    let rows = client
3678                        .query(
3679                            "SELECT id FROM commits
3680                              WHERE tenant_id = $1 AND epoch_id = $2
3681                              ORDER BY timestamp, id",
3682                            &[&self.tenant_id, &epoch.id],
3683                        )
3684                        .await
3685                        .map_err(|e| StorageError::Backend(format!("epoch commits: {}", e)))?;
3686                    epoch.commits = rows
3687                        .iter()
3688                        .filter_map(|r| {
3689                            let b: Vec<u8> = r.get("id");
3690                            if b.len() == 32 {
3691                                let mut arr = [0u8; 32];
3692                                arr.copy_from_slice(&b);
3693                                Some(ObjectId::from_bytes(arr))
3694                            } else {
3695                                None
3696                            }
3697                        })
3698                        .collect();
3699                    Ok(Some(epoch))
3700                }
3701            }
3702        })
3703    }
3704
3705    fn archive_epoch(&self, id: &str) -> Result<(), StorageError> {
3706        self.block_on(async {
3707            let client = self.client().await?;
3708            let n = client
3709                .execute(
3710                    "UPDATE epochs SET status = 'Archived'
3711                      WHERE tenant_id = $1 AND id = $2 AND status = 'Sealed'",
3712                    &[&self.tenant_id, &id],
3713                )
3714                .await
3715                .map_err(|e| StorageError::Backend(format!("archive epoch: {}", e)))?;
3716            if n == 0 {
3717                return Err(StorageError::Backend(format!(
3718                    "epoch '{}' not found or not sealed",
3719                    id
3720                )));
3721            }
3722            Ok(())
3723        })
3724    }
3725
3726    fn set_commit_epoch(&self, commit_id: &ObjectId, epoch_id: &str) -> Result<(), StorageError> {
3727        self.block_on(async {
3728            let mut client = self.client().await?;
3729            // One transaction with the epoch row locked: a seal waits for it,
3730            // so a commit can't join an epoch that is being sealed, and the
3731            // status check can't go stale before the write.
3732            let tx = client
3733                .transaction()
3734                .await
3735                .map_err(|e| StorageError::Backend(format!("begin assoc: {e}")))?;
3736            let row = tx
3737                .query_opt(
3738                    "SELECT status FROM epochs WHERE tenant_id = $1 AND id = $2 FOR UPDATE",
3739                    &[&self.tenant_id, &epoch_id],
3740                )
3741                .await
3742                .map_err(|e| StorageError::Backend(format!("assoc lookup: {}", e)))?;
3743            match row.as_ref().map(|r| r.get::<_, String>("status")) {
3744                None => {
3745                    return Err(StorageError::Backend(format!(
3746                        "epoch not found: {}",
3747                        epoch_id
3748                    )));
3749                }
3750                Some(s) if s == "Sealed" || s == "Archived" => {
3751                    return Err(StorageError::EpochAlreadySealed {
3752                        id: epoch_id.to_string(),
3753                    });
3754                }
3755                _ => {}
3756            }
3757            let joined = tx
3758                .execute(
3759                    "UPDATE commits SET epoch_id = $1
3760                     WHERE tenant_id = $2 AND id = $3 AND epoch_id IS DISTINCT FROM $1",
3761                    &[&epoch_id, &self.tenant_id, &commit_id.as_bytes().as_slice()],
3762                )
3763                .await
3764                .map_err(|e| StorageError::Backend(format!("set commit epoch: {}", e)))?;
3765            // A running count, bumped only when the commit actually joins (the
3766            // old full COUNT(*) per commit grew with the epoch). Sealing writes
3767            // the exact count.
3768            if joined > 0 {
3769                tx.execute(
3770                    "UPDATE epochs SET commit_count = commit_count + 1
3771                     WHERE tenant_id = $1 AND id = $2",
3772                    &[&self.tenant_id, &epoch_id],
3773                )
3774                .await
3775                .map_err(|e| StorageError::Backend(format!("update commit_count: {}", e)))?;
3776            }
3777            tx.commit()
3778                .await
3779                .map_err(|e| StorageError::Backend(format!("commit assoc: {e}")))?;
3780            Ok(())
3781        })
3782    }
3783}
3784
3785// ---------------------------------------------------------------------------
3786// SessionStore
3787// ---------------------------------------------------------------------------
3788
3789impl SessionStore for PostgresStorage {
3790    fn create_session(&self, session: &Session) -> Result<(), StorageError> {
3791        let metadata = serde_json::json!({
3792            "head": session.head,
3793            "delegated_intent": session.delegated_intent,
3794            "report_to": session.report_to,
3795            "working_branch": session.working_branch,
3796            "scope_tenant": session.scope_tenant,
3797        })
3798        .to_string();
3799
3800        self.block_on(async {
3801            let client = self.client().await?;
3802
3803            client
3804                .execute(
3805                    "INSERT INTO sessions
3806                     (tenant_id, id, agent_id, parent_id, scope_path, scope_branch,
3807                      scope_namespace, status, created_at, ended_at, metadata, commit_count)
3808                     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11,
3809                             COALESCE(
3810                                 (SELECT commit_count FROM sessions
3811                                  WHERE tenant_id = $1 AND id = $2),
3812                                 0))
3813                     ON CONFLICT (tenant_id, id) DO UPDATE
3814                       SET agent_id = EXCLUDED.agent_id,
3815                           parent_id = EXCLUDED.parent_id,
3816                           scope_path = EXCLUDED.scope_path,
3817                           scope_branch = EXCLUDED.scope_branch,
3818                           scope_namespace = EXCLUDED.scope_namespace,
3819                           status = EXCLUDED.status,
3820                           created_at = EXCLUDED.created_at,
3821                           ended_at = EXCLUDED.ended_at,
3822                           metadata = EXCLUDED.metadata",
3823                    &[
3824                        &self.tenant_id,
3825                        &session.id,
3826                        &session.agent_id,
3827                        &session.parent_session,
3828                        &session.path_scope,
3829                        &session.working_branch,
3830                        &session
3831                            .scope_namespace
3832                            .as_ref()
3833                            .map(|n| n.as_str().to_string()),
3834                        &session.status.as_str(),
3835                        &session.created_at.to_rfc3339(),
3836                        &session.ended_at.map(|t| t.to_rfc3339()),
3837                        &metadata,
3838                    ],
3839                )
3840                .await
3841                .map_err(|e| StorageError::Backend(format!("create session: {}", e)))?;
3842            Ok(())
3843        })
3844    }
3845
3846    fn end_session(
3847        &self,
3848        id: &str,
3849        status: SessionStatus,
3850        ended_at: DateTime<Utc>,
3851    ) -> Result<(), StorageError> {
3852        self.block_on(async {
3853            let client = self.client().await?;
3854            // One conditional UPDATE, so two callers ending one session can't
3855            // both pass a status check; exactly one ends it.
3856            let ended = client
3857                .execute(
3858                    "UPDATE sessions SET status = $1, ended_at = $2
3859                     WHERE tenant_id = $3 AND id = $4 AND status = 'Active'",
3860                    &[
3861                        &status.as_str(),
3862                        &ended_at.to_rfc3339(),
3863                        &self.tenant_id,
3864                        &id,
3865                    ],
3866                )
3867                .await
3868                .map_err(|e| StorageError::Backend(format!("end session: {}", e)))?;
3869            if ended > 0 {
3870                return Ok(());
3871            }
3872            let exists = client
3873                .query_opt(
3874                    "SELECT 1 FROM sessions WHERE tenant_id = $1 AND id = $2",
3875                    &[&self.tenant_id, &id],
3876                )
3877                .await
3878                .map_err(|e| StorageError::Backend(format!("end session lookup: {}", e)))?;
3879            match exists {
3880                Some(_) => Err(StorageError::SessionEnded { id: id.to_string() }),
3881                None => Err(StorageError::Backend(format!("session not found: {}", id))),
3882            }
3883        })
3884    }
3885
3886    fn list_sessions(&self, agent_filter: Option<&str>) -> Result<Vec<Session>, StorageError> {
3887        self.block_on(async {
3888            let client = self.client().await?;
3889
3890            let rows = client
3891                .query(
3892                    "SELECT id, agent_id, parent_id, scope_path, scope_branch, scope_namespace,
3893                            status, created_at, ended_at, metadata
3894                     FROM sessions
3895                     WHERE tenant_id = $1 AND ($2::text IS NULL OR agent_id = $2)
3896                     ORDER BY created_at DESC",
3897                    &[&self.tenant_id, &agent_filter],
3898                )
3899                .await
3900                .map_err(|e| StorageError::Backend(format!("list sessions: {}", e)))?;
3901
3902            let mut out = Vec::with_capacity(rows.len());
3903            for row in rows {
3904                out.push(row_to_session(
3905                    row.get("id"),
3906                    row.get("agent_id"),
3907                    row.get("parent_id"),
3908                    row.get("scope_path"),
3909                    row.get("scope_branch"),
3910                    row.get("scope_namespace"),
3911                    row.get("status"),
3912                    row.get("created_at"),
3913                    row.get("ended_at"),
3914                    row.get("metadata"),
3915                )?);
3916            }
3917            Ok(out)
3918        })
3919    }
3920
3921    fn get_session(&self, id: &str) -> Result<Option<Session>, StorageError> {
3922        self.block_on(async {
3923            let client = self.client().await?;
3924
3925            let row = client
3926                .query_opt(
3927                    "SELECT id, agent_id, parent_id, scope_path, scope_branch, scope_namespace,
3928                            status, created_at, ended_at, metadata
3929                     FROM sessions WHERE tenant_id = $1 AND id = $2",
3930                    &[&self.tenant_id, &id],
3931                )
3932                .await
3933                .map_err(|e| StorageError::Backend(format!("get session: {}", e)))?;
3934
3935            match row {
3936                None => Ok(None),
3937                Some(row) => Ok(Some(row_to_session(
3938                    row.get("id"),
3939                    row.get("agent_id"),
3940                    row.get("parent_id"),
3941                    row.get("scope_path"),
3942                    row.get("scope_branch"),
3943                    row.get("scope_namespace"),
3944                    row.get("status"),
3945                    row.get("created_at"),
3946                    row.get("ended_at"),
3947                    row.get("metadata"),
3948                )?)),
3949            }
3950        })
3951    }
3952
3953    fn set_commit_session(
3954        &self,
3955        commit_id: &ObjectId,
3956        session_id: &str,
3957    ) -> Result<(), StorageError> {
3958        self.block_on(async {
3959            let mut client = self.client().await?;
3960            // As set_commit_epoch: one transaction, the session row locked so
3961            // ending the session waits and the status check can't go stale.
3962            let tx = client
3963                .transaction()
3964                .await
3965                .map_err(|e| StorageError::Backend(format!("begin assoc: {e}")))?;
3966            let row = tx
3967                .query_opt(
3968                    "SELECT status FROM sessions WHERE tenant_id = $1 AND id = $2 FOR UPDATE",
3969                    &[&self.tenant_id, &session_id],
3970                )
3971                .await
3972                .map_err(|e| StorageError::Backend(format!("assoc lookup: {}", e)))?;
3973            match row.as_ref().map(|r| r.get::<_, String>("status")) {
3974                None => {
3975                    return Err(StorageError::Backend(format!(
3976                        "session not found: {}",
3977                        session_id
3978                    )));
3979                }
3980                Some(s) if s != "Active" => {
3981                    return Err(StorageError::SessionEnded {
3982                        id: session_id.to_string(),
3983                    });
3984                }
3985                _ => {}
3986            }
3987            let joined = tx
3988                .execute(
3989                    "UPDATE commits SET session_id = $1
3990                     WHERE tenant_id = $2 AND id = $3 AND session_id IS DISTINCT FROM $1",
3991                    &[
3992                        &session_id,
3993                        &self.tenant_id,
3994                        &commit_id.as_bytes().as_slice(),
3995                    ],
3996                )
3997                .await
3998                .map_err(|e| StorageError::Backend(format!("set commit session: {}", e)))?;
3999            if joined > 0 {
4000                tx.execute(
4001                    "UPDATE sessions SET commit_count = commit_count + 1
4002                     WHERE tenant_id = $1 AND id = $2",
4003                    &[&self.tenant_id, &session_id],
4004                )
4005                .await
4006                .map_err(|e| {
4007                    StorageError::Backend(format!("update session commit_count: {}", e))
4008                })?;
4009            }
4010            tx.commit()
4011                .await
4012                .map_err(|e| StorageError::Backend(format!("commit assoc: {e}")))?;
4013            Ok(())
4014        })
4015    }
4016}
4017
4018// ---------------------------------------------------------------------------
4019// TaintStore for PostgresStorage (0.7.75-beta.2)
4020// ---------------------------------------------------------------------------
4021
4022fn pg_kind_to_str(k: agentstategraph_taint::TaintKind) -> &'static str {
4023    match k {
4024        agentstategraph_taint::TaintKind::Taint => "taint",
4025        agentstategraph_taint::TaintKind::Quarantine => "quarantine",
4026        agentstategraph_taint::TaintKind::Watch => "watch",
4027    }
4028}
4029
4030fn pg_kind_from_str(s: &str) -> Result<agentstategraph_taint::TaintKind, StorageError> {
4031    match s {
4032        "taint" => Ok(agentstategraph_taint::TaintKind::Taint),
4033        "quarantine" => Ok(agentstategraph_taint::TaintKind::Quarantine),
4034        "watch" => Ok(agentstategraph_taint::TaintKind::Watch),
4035        other => Err(StorageError::Backend(format!(
4036            "unknown taint kind: {other}"
4037        ))),
4038    }
4039}
4040
4041fn pg_effect_to_str(e: agentstategraph_taint::TaintEffect) -> &'static str {
4042    match e {
4043        agentstategraph_taint::TaintEffect::Warn => "warn",
4044        agentstategraph_taint::TaintEffect::Block => "block",
4045        agentstategraph_taint::TaintEffect::Review => "review",
4046        agentstategraph_taint::TaintEffect::Isolate => "isolate",
4047        agentstategraph_taint::TaintEffect::Advisory => "advisory",
4048    }
4049}
4050
4051fn pg_effect_from_str(s: &str) -> Result<agentstategraph_taint::TaintEffect, StorageError> {
4052    match s {
4053        "warn" => Ok(agentstategraph_taint::TaintEffect::Warn),
4054        "block" => Ok(agentstategraph_taint::TaintEffect::Block),
4055        "review" => Ok(agentstategraph_taint::TaintEffect::Review),
4056        "isolate" => Ok(agentstategraph_taint::TaintEffect::Isolate),
4057        "advisory" => Ok(agentstategraph_taint::TaintEffect::Advisory),
4058        other => Err(StorageError::Backend(format!(
4059            "unknown taint effect: {other}"
4060        ))),
4061    }
4062}
4063
4064fn pg_severity_to_str(s: agentstategraph_taint::TaintSeverity) -> &'static str {
4065    match s {
4066        agentstategraph_taint::TaintSeverity::Low => "low",
4067        agentstategraph_taint::TaintSeverity::Medium => "medium",
4068        agentstategraph_taint::TaintSeverity::High => "high",
4069        agentstategraph_taint::TaintSeverity::Critical => "critical",
4070    }
4071}
4072
4073fn pg_severity_from_str(s: &str) -> Result<agentstategraph_taint::TaintSeverity, StorageError> {
4074    match s {
4075        "low" => Ok(agentstategraph_taint::TaintSeverity::Low),
4076        "medium" => Ok(agentstategraph_taint::TaintSeverity::Medium),
4077        "high" => Ok(agentstategraph_taint::TaintSeverity::High),
4078        "critical" => Ok(agentstategraph_taint::TaintSeverity::Critical),
4079        other => Err(StorageError::Backend(format!(
4080            "unknown taint severity: {other}"
4081        ))),
4082    }
4083}
4084
4085fn pg_row_to_taint(
4086    row: &tokio_postgres::Row,
4087) -> Result<agentstategraph_taint::Taint, StorageError> {
4088    use agentstategraph_taint::TaintMetadata;
4089    let kind: String = row.get("kind");
4090    let effect: String = row.get("effect");
4091    let severity: String = row.get("severity");
4092    let metadata_raw: String = row.get("metadata");
4093    let created_at_s: String = row.get("created_at");
4094    let expires_at_s: Option<String> = row.get("expires_at");
4095    let resolved_at_s: Option<String> = row.get("resolved_at");
4096
4097    let metadata: TaintMetadata = serde_json::from_str(&metadata_raw)
4098        .map_err(|e| StorageError::Serialization(format!("taint metadata: {e}")))?;
4099    let created_at = DateTime::parse_from_rfc3339(&created_at_s)
4100        .map_err(|e| StorageError::Serialization(format!("created_at: {e}")))?
4101        .with_timezone(&Utc);
4102    let expires_at = match expires_at_s {
4103        Some(s) => Some(
4104            DateTime::parse_from_rfc3339(&s)
4105                .map_err(|e| StorageError::Serialization(format!("expires_at: {e}")))?
4106                .with_timezone(&Utc),
4107        ),
4108        None => None,
4109    };
4110    let resolved_at = match resolved_at_s {
4111        Some(s) => Some(
4112            DateTime::parse_from_rfc3339(&s)
4113                .map_err(|e| StorageError::Serialization(format!("resolved_at: {e}")))?
4114                .with_timezone(&Utc),
4115        ),
4116        None => None,
4117    };
4118
4119    Ok(agentstategraph_taint::Taint {
4120        id: row.get("id"),
4121        path: row.get("path"),
4122        name: row.get("name"),
4123        kind: pg_kind_from_str(&kind)?,
4124        effect: pg_effect_from_str(&effect)?,
4125        severity: pg_severity_from_str(&severity)?,
4126        reason: row.get("reason"),
4127        agent_id: row.get("agent_id"),
4128        commit_id: row.get("commit_id"),
4129        created_at,
4130        expires_at,
4131        resolved_at,
4132        resolved_by: row.get("resolved_by"),
4133        resolved_reason: row.get("resolved_reason"),
4134        resolved_proof: row.get("resolved_proof"),
4135        propagate: row.get("propagate"),
4136        metadata,
4137    })
4138}
4139
4140impl TaintStore for PostgresStorage {
4141    fn create_taint(&self, taint: &agentstategraph_taint::Taint) -> Result<(), StorageError> {
4142        self.block_on(async {
4143            let client = self.client().await?;
4144            let metadata_json = serde_json::to_string(&taint.metadata)
4145                .map_err(|e| StorageError::Serialization(format!("taint metadata: {e}")))?;
4146            let expires = taint.expires_at.map(|t| t.to_rfc3339());
4147            let resolved_at = taint.resolved_at.map(|t| t.to_rfc3339());
4148            client
4149                .execute(
4150                    "INSERT INTO taints (
4151                        tenant_id, id, path, name, kind, effect, severity, reason,
4152                        agent_id, commit_id, created_at, expires_at, resolved_at,
4153                        resolved_by, resolved_reason, resolved_proof, propagate, metadata
4154                    ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18)",
4155                    &[
4156                        &self.tenant_id,
4157                        &taint.id,
4158                        &taint.path,
4159                        &taint.name,
4160                        &pg_kind_to_str(taint.kind),
4161                        &pg_effect_to_str(taint.effect),
4162                        &pg_severity_to_str(taint.severity),
4163                        &taint.reason,
4164                        &taint.agent_id,
4165                        &taint.commit_id,
4166                        &taint.created_at.to_rfc3339(),
4167                        &expires,
4168                        &resolved_at,
4169                        &taint.resolved_by,
4170                        &taint.resolved_reason,
4171                        &taint.resolved_proof,
4172                        &taint.propagate,
4173                        &metadata_json,
4174                    ],
4175                )
4176                .await
4177                .map_err(|e| StorageError::Backend(format!("insert taint: {e}")))?;
4178            Ok(())
4179        })
4180    }
4181
4182    fn resolve_taint(
4183        &self,
4184        id: &str,
4185        resolved_by: &str,
4186        reason: &str,
4187        proof: Option<&str>,
4188        resolved_at: DateTime<Utc>,
4189    ) -> Result<(), StorageError> {
4190        self.block_on(async {
4191            let client = self.client().await?;
4192            // Guard: fail on already-resolved / not-found. Partial
4193            // unique index would force failures on re-activation;
4194            // we want explicit control here so the error is legible.
4195            let row = client
4196                .query_opt(
4197                    "SELECT resolved_at FROM taints WHERE tenant_id = $1 AND id = $2",
4198                    &[&self.tenant_id, &id],
4199                )
4200                .await
4201                .map_err(|e| StorageError::Backend(format!("resolve check: {e}")))?
4202                .ok_or_else(|| StorageError::Backend(format!("taint {id} not found")))?;
4203            let already: Option<String> = row.get(0);
4204            if already.is_some() {
4205                return Err(StorageError::Backend(format!(
4206                    "taint {id} is already resolved"
4207                )));
4208            }
4209            let proof_owned = proof.map(str::to_string);
4210            client
4211                .execute(
4212                    "UPDATE taints
4213                        SET resolved_at = $3, resolved_by = $4,
4214                            resolved_reason = $5, resolved_proof = $6
4215                      WHERE tenant_id = $1 AND id = $2",
4216                    &[
4217                        &self.tenant_id,
4218                        &id,
4219                        &resolved_at.to_rfc3339(),
4220                        &resolved_by,
4221                        &reason,
4222                        &proof_owned,
4223                    ],
4224                )
4225                .await
4226                .map_err(|e| StorageError::Backend(format!("resolve taint: {e}")))?;
4227            Ok(())
4228        })
4229    }
4230
4231    fn list_taints(
4232        &self,
4233        path_prefix: Option<&str>,
4234        kind: Option<agentstategraph_taint::TaintKind>,
4235        include_resolved: bool,
4236    ) -> Result<Vec<agentstategraph_taint::Taint>, StorageError> {
4237        self.block_on(async {
4238            let client = self.client().await?;
4239            // Build dynamic WHERE without sacrificing parameterization:
4240            // we always pass tenant_id + optional prefix/plike/kind.
4241            let prefix = path_prefix.map(|p| p.trim_end_matches('/').to_string());
4242            let plike = prefix.as_ref().map(|p| format!("{p}/%"));
4243            let kind_str = kind.map(|k| pg_kind_to_str(k).to_string());
4244
4245            let rows = match (prefix.as_ref(), kind_str.as_ref(), include_resolved) {
4246                (None, None, true) => {
4247                    client
4248                        .query(
4249                            "SELECT * FROM taints WHERE tenant_id = $1
4250                              ORDER BY created_at DESC",
4251                            &[&self.tenant_id],
4252                        )
4253                        .await
4254                }
4255                (None, None, false) => {
4256                    client
4257                        .query(
4258                            "SELECT * FROM taints
4259                              WHERE tenant_id = $1 AND resolved_at IS NULL
4260                              ORDER BY created_at DESC",
4261                            &[&self.tenant_id],
4262                        )
4263                        .await
4264                }
4265                (Some(p), None, true) => {
4266                    client
4267                        .query(
4268                            "SELECT * FROM taints
4269                              WHERE tenant_id = $1 AND (path = $2 OR path LIKE $3)
4270                              ORDER BY created_at DESC",
4271                            &[&self.tenant_id, p, plike.as_ref().unwrap()],
4272                        )
4273                        .await
4274                }
4275                (Some(p), None, false) => {
4276                    client
4277                        .query(
4278                            "SELECT * FROM taints
4279                              WHERE tenant_id = $1 AND resolved_at IS NULL
4280                                AND (path = $2 OR path LIKE $3)
4281                              ORDER BY created_at DESC",
4282                            &[&self.tenant_id, p, plike.as_ref().unwrap()],
4283                        )
4284                        .await
4285                }
4286                (None, Some(k), true) => {
4287                    client
4288                        .query(
4289                            "SELECT * FROM taints
4290                              WHERE tenant_id = $1 AND kind = $2
4291                              ORDER BY created_at DESC",
4292                            &[&self.tenant_id, k],
4293                        )
4294                        .await
4295                }
4296                (None, Some(k), false) => {
4297                    client
4298                        .query(
4299                            "SELECT * FROM taints
4300                              WHERE tenant_id = $1 AND resolved_at IS NULL AND kind = $2
4301                              ORDER BY created_at DESC",
4302                            &[&self.tenant_id, k],
4303                        )
4304                        .await
4305                }
4306                (Some(p), Some(k), true) => {
4307                    client
4308                        .query(
4309                            "SELECT * FROM taints
4310                              WHERE tenant_id = $1 AND kind = $2
4311                                AND (path = $3 OR path LIKE $4)
4312                              ORDER BY created_at DESC",
4313                            &[&self.tenant_id, k, p, plike.as_ref().unwrap()],
4314                        )
4315                        .await
4316                }
4317                (Some(p), Some(k), false) => {
4318                    client
4319                        .query(
4320                            "SELECT * FROM taints
4321                              WHERE tenant_id = $1 AND resolved_at IS NULL AND kind = $2
4322                                AND (path = $3 OR path LIKE $4)
4323                              ORDER BY created_at DESC",
4324                            &[&self.tenant_id, k, p, plike.as_ref().unwrap()],
4325                        )
4326                        .await
4327                }
4328            }
4329            .map_err(|e| StorageError::Backend(format!("list_taints: {e}")))?;
4330
4331            let mut out = Vec::with_capacity(rows.len());
4332            for row in rows {
4333                out.push(pg_row_to_taint(&row)?);
4334            }
4335            Ok(out)
4336        })
4337    }
4338
4339    fn check_taint(
4340        &self,
4341        request_path: &str,
4342    ) -> Result<Vec<agentstategraph_taint::Taint>, StorageError> {
4343        self.block_on(async {
4344            let client = self.client().await?;
4345            let now = Utc::now().to_rfc3339();
4346            // Ancestor match: exact OR (propagate=true AND request_path
4347            // LIKE path || '/%'). Postgres LIKE is case-sensitive.
4348            let rows = client
4349                .query(
4350                    "SELECT * FROM taints
4351                      WHERE tenant_id = $1
4352                        AND resolved_at IS NULL
4353                        AND (expires_at IS NULL OR expires_at > $2)
4354                        AND (path = $3 OR (propagate AND $3 LIKE path || '/%'))
4355                      ORDER BY created_at DESC",
4356                    &[&self.tenant_id, &now, &request_path],
4357                )
4358                .await
4359                .map_err(|e| StorageError::Backend(format!("check_taint: {e}")))?;
4360            let mut out = Vec::with_capacity(rows.len());
4361            for row in rows {
4362                out.push(pg_row_to_taint(&row)?);
4363            }
4364            Ok(out)
4365        })
4366    }
4367
4368    fn get_taint(&self, id: &str) -> Result<Option<agentstategraph_taint::Taint>, StorageError> {
4369        self.block_on(async {
4370            let client = self.client().await?;
4371            let row = client
4372                .query_opt(
4373                    "SELECT * FROM taints WHERE tenant_id = $1 AND id = $2",
4374                    &[&self.tenant_id, &id],
4375                )
4376                .await
4377                .map_err(|e| StorageError::Backend(format!("get_taint: {e}")))?;
4378            match row {
4379                Some(r) => Ok(Some(pg_row_to_taint(&r)?)),
4380                None => Ok(None),
4381            }
4382        })
4383    }
4384
4385    fn set_taint_commit_id(&self, id: &str, commit_id: &str) -> Result<(), StorageError> {
4386        self.block_on(async {
4387            let client = self.client().await?;
4388            client
4389                .execute(
4390                    "UPDATE taints SET commit_id = $3
4391                      WHERE tenant_id = $1 AND id = $2 AND resolved_at IS NULL",
4392                    &[&self.tenant_id, &id, &commit_id],
4393                )
4394                .await
4395                .map_err(|e| StorageError::Backend(format!("set_taint_commit_id: {e}")))?;
4396            Ok(())
4397        })
4398    }
4399}
4400
4401// ---------------------------------------------------------------------------
4402// ReminderStore — durable Postgres reminder storage
4403// ---------------------------------------------------------------------------
4404
4405fn pg_reminder_status_to_str(s: agentstategraph_reminders::types::ReminderStatus) -> &'static str {
4406    use agentstategraph_reminders::types::ReminderStatus;
4407    match s {
4408        ReminderStatus::Pending => "pending",
4409        ReminderStatus::Due => "due",
4410        ReminderStatus::AwaitingPermission => "awaiting_permission",
4411        ReminderStatus::InProgress => "in_progress",
4412        ReminderStatus::Completed => "completed",
4413        ReminderStatus::Snoozed => "snoozed",
4414        ReminderStatus::Cancelled => "cancelled",
4415    }
4416}
4417
4418fn pg_reminder_status_from_str(
4419    s: &str,
4420) -> Result<agentstategraph_reminders::types::ReminderStatus, StorageError> {
4421    use agentstategraph_reminders::types::ReminderStatus;
4422    match s {
4423        "pending" => Ok(ReminderStatus::Pending),
4424        "due" => Ok(ReminderStatus::Due),
4425        "awaiting_permission" => Ok(ReminderStatus::AwaitingPermission),
4426        "in_progress" => Ok(ReminderStatus::InProgress),
4427        "completed" => Ok(ReminderStatus::Completed),
4428        "snoozed" => Ok(ReminderStatus::Snoozed),
4429        "cancelled" => Ok(ReminderStatus::Cancelled),
4430        other => Err(StorageError::Backend(format!(
4431            "unknown reminder status: {other}"
4432        ))),
4433    }
4434}
4435
4436fn pg_priority_to_i32(p: agentstategraph_reminders::types::Priority) -> i32 {
4437    p.as_u8() as i32
4438}
4439
4440fn pg_priority_from_i32(
4441    n: i32,
4442) -> Result<agentstategraph_reminders::types::Priority, StorageError> {
4443    use agentstategraph_reminders::types::Priority;
4444    match n {
4445        1 => Ok(Priority::Critical),
4446        2 => Ok(Priority::High),
4447        3 => Ok(Priority::Medium),
4448        4 => Ok(Priority::Low),
4449        5 => Ok(Priority::Minimal),
4450        other => Err(StorageError::Backend(format!(
4451            "unknown priority value: {other}"
4452        ))),
4453    }
4454}
4455
4456/// Write every field of `reminder` over its row, on a plain connection or
4457/// inside a transaction. Returns the rows changed: 0 means no such reminder.
4458async fn write_reminder<C: GenericClient>(
4459    client: &C,
4460    tenant_id: &str,
4461    reminder: &agentstategraph_reminders::Reminder,
4462) -> Result<u64, StorageError> {
4463    let json = |what: &str, v: Result<String, serde_json::Error>| {
4464        v.map_err(|e| StorageError::Serialization(format!("{what}: {e}")))
4465    };
4466    let commands_json = json("commands", serde_json::to_string(&reminder.commands))?;
4467    let refs_json = json("refs", serde_json::to_string(&reminder.refs))?;
4468    let schedule_json = reminder
4469        .schedule
4470        .as_ref()
4471        .map(|s| json("schedule", serde_json::to_string(s)))
4472        .transpose()?;
4473    let executions_json = json("executions", serde_json::to_string(&reminder.executions))?;
4474    let tags_json = json("tags", serde_json::to_string(&reminder.tags))?;
4475    let priority = pg_priority_to_i32(reminder.priority);
4476    let status = pg_reminder_status_to_str(reminder.status).to_string();
4477    let due_at = reminder.due_at.to_rfc3339();
4478    let created_at = reminder.created_at.to_rfc3339();
4479    let snoozed_until = reminder.snoozed_until.map(|t| t.to_rfc3339());
4480    client
4481        .execute(
4482            "UPDATE reminders SET
4483                title = $3, instructions = $4, commands = $5, refs = $6,
4484                priority = $7, due_at = $8, schedule = $9, autonomous = $10,
4485                created_by = $11, created_at = $12, status = $13,
4486                snoozed_until = $14, executions = $15, tags = $16
4487             WHERE tenant_id = $1 AND id = $2",
4488            &[
4489                &tenant_id,
4490                &reminder.id,
4491                &reminder.title,
4492                &reminder.instructions,
4493                &commands_json,
4494                &refs_json,
4495                &priority,
4496                &due_at,
4497                &schedule_json,
4498                &reminder.autonomous,
4499                &reminder.created_by,
4500                &created_at,
4501                &status,
4502                &snoozed_until,
4503                &executions_json,
4504                &tags_json,
4505            ],
4506        )
4507        .await
4508        .map_err(|e| StorageError::Backend(format!("update reminder: {e}")))
4509}
4510
4511fn pg_row_to_reminder(
4512    row: &tokio_postgres::Row,
4513) -> Result<agentstategraph_reminders::Reminder, StorageError> {
4514    use agentstategraph_reminders::types::ExecutionRecord;
4515    use agentstategraph_reminders::{Reminder, types::ReminderRef, types::Schedule};
4516
4517    let status_s: String = row.get("status");
4518    let priority_n: i32 = row.get("priority");
4519    let due_at_s: String = row.get("due_at");
4520    let created_at_s: String = row.get("created_at");
4521    let snoozed_until_s: Option<String> = row.get("snoozed_until");
4522    let commands_s: String = row.get("commands");
4523    let refs_s: String = row.get("refs");
4524    let schedule_s: Option<String> = row.get("schedule");
4525    let executions_s: String = row.get("executions");
4526    let tags_s: String = row.get("tags");
4527    let autonomous: bool = row.get("autonomous");
4528
4529    let status = pg_reminder_status_from_str(&status_s)?;
4530    let priority = pg_priority_from_i32(priority_n)?;
4531    let due_at = DateTime::parse_from_rfc3339(&due_at_s)
4532        .map_err(|e| StorageError::Serialization(format!("due_at: {e}")))?
4533        .with_timezone(&Utc);
4534    let created_at = DateTime::parse_from_rfc3339(&created_at_s)
4535        .map_err(|e| StorageError::Serialization(format!("created_at: {e}")))?
4536        .with_timezone(&Utc);
4537    let snoozed_until = match snoozed_until_s {
4538        Some(s) => Some(
4539            DateTime::parse_from_rfc3339(&s)
4540                .map_err(|e| StorageError::Serialization(format!("snoozed_until: {e}")))?
4541                .with_timezone(&Utc),
4542        ),
4543        None => None,
4544    };
4545    let commands: Vec<String> = serde_json::from_str(&commands_s)
4546        .map_err(|e| StorageError::Serialization(format!("commands: {e}")))?;
4547    let refs: Vec<ReminderRef> = serde_json::from_str(&refs_s)
4548        .map_err(|e| StorageError::Serialization(format!("refs: {e}")))?;
4549    let schedule: Option<Schedule> = match schedule_s {
4550        Some(s) => Some(
4551            serde_json::from_str(&s)
4552                .map_err(|e| StorageError::Serialization(format!("schedule: {e}")))?,
4553        ),
4554        None => None,
4555    };
4556    let executions: Vec<ExecutionRecord> = serde_json::from_str(&executions_s)
4557        .map_err(|e| StorageError::Serialization(format!("executions: {e}")))?;
4558    let tags: Vec<String> = serde_json::from_str(&tags_s)
4559        .map_err(|e| StorageError::Serialization(format!("tags: {e}")))?;
4560
4561    Ok(Reminder {
4562        id: row.get("id"),
4563        title: row.get("title"),
4564        instructions: row.get("instructions"),
4565        commands,
4566        refs,
4567        priority,
4568        due_at,
4569        schedule,
4570        autonomous,
4571        created_by: row.get("created_by"),
4572        created_at,
4573        status,
4574        snoozed_until,
4575        executions,
4576        tags,
4577    })
4578}
4579
4580impl agentstategraph_reminders::ReminderStore for PostgresStorage {
4581    fn save(
4582        &self,
4583        reminder: &agentstategraph_reminders::Reminder,
4584    ) -> Result<(), agentstategraph_reminders::ReminderError> {
4585        let commands_json = serde_json::to_string(&reminder.commands).map_err(|e| {
4586            agentstategraph_reminders::ReminderError::Store(format!("commands: {e}"))
4587        })?;
4588        let refs_json = serde_json::to_string(&reminder.refs)
4589            .map_err(|e| agentstategraph_reminders::ReminderError::Store(format!("refs: {e}")))?;
4590        let schedule_json = reminder
4591            .schedule
4592            .as_ref()
4593            .map(|s| serde_json::to_string(s))
4594            .transpose()
4595            .map_err(|e| {
4596                agentstategraph_reminders::ReminderError::Store(format!("schedule: {e}"))
4597            })?;
4598        let executions_json = serde_json::to_string(&reminder.executions).map_err(|e| {
4599            agentstategraph_reminders::ReminderError::Store(format!("executions: {e}"))
4600        })?;
4601        let tags_json = serde_json::to_string(&reminder.tags)
4602            .map_err(|e| agentstategraph_reminders::ReminderError::Store(format!("tags: {e}")))?;
4603        let priority = pg_priority_to_i32(reminder.priority);
4604        let status = pg_reminder_status_to_str(reminder.status).to_string();
4605        let due_at = reminder.due_at.to_rfc3339();
4606        let created_at = reminder.created_at.to_rfc3339();
4607        let snoozed_until = reminder.snoozed_until.map(|t| t.to_rfc3339());
4608
4609        self.block_on(async {
4610            let client = self.client().await?;
4611            client
4612                .execute(
4613                    "INSERT INTO reminders (
4614                        tenant_id, id, title, instructions, commands, refs,
4615                        priority, due_at, schedule, autonomous, created_by,
4616                        created_at, status, snoozed_until, executions, tags
4617                    ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)",
4618                    &[
4619                        &self.tenant_id,
4620                        &reminder.id,
4621                        &reminder.title,
4622                        &reminder.instructions,
4623                        &commands_json,
4624                        &refs_json,
4625                        &priority,
4626                        &due_at,
4627                        &schedule_json,
4628                        &reminder.autonomous,
4629                        &reminder.created_by,
4630                        &created_at,
4631                        &status,
4632                        &snoozed_until,
4633                        &executions_json,
4634                        &tags_json,
4635                    ],
4636                )
4637                .await
4638                .map_err(|e| StorageError::Backend(format!("insert reminder: {e}")))?;
4639            Ok(())
4640        })
4641        .map_err(|e| agentstategraph_reminders::ReminderError::Store(e.to_string()))
4642    }
4643
4644    fn get(
4645        &self,
4646        id: &str,
4647    ) -> Result<Option<agentstategraph_reminders::Reminder>, agentstategraph_reminders::ReminderError>
4648    {
4649        self.block_on(async {
4650            let client = self.client().await?;
4651            let row = client
4652                .query_opt(
4653                    "SELECT * FROM reminders WHERE tenant_id = $1 AND id = $2",
4654                    &[&self.tenant_id, &id],
4655                )
4656                .await
4657                .map_err(|e| StorageError::Backend(format!("get reminder: {e}")))?;
4658            match row {
4659                Some(r) => Ok(Some(pg_row_to_reminder(&r)?)),
4660                None => Ok(None),
4661            }
4662        })
4663        .map_err(|e| agentstategraph_reminders::ReminderError::Store(e.to_string()))
4664    }
4665
4666    fn update(
4667        &self,
4668        reminder: &agentstategraph_reminders::Reminder,
4669    ) -> Result<(), agentstategraph_reminders::ReminderError> {
4670        let written = self
4671            .block_on(async {
4672                let client = self.client().await?;
4673                write_reminder(&client, &self.tenant_id, reminder).await
4674            })
4675            .map_err(|e| agentstategraph_reminders::ReminderError::Store(e.to_string()))?;
4676        if written == 0 {
4677            return Err(agentstategraph_reminders::ReminderError::NotFound(
4678                reminder.id.clone(),
4679            ));
4680        }
4681        Ok(())
4682    }
4683
4684    /// Compare and write in one transaction with the row locked, so no other
4685    /// writer can change the reminder in between. The trait's default compared
4686    /// and wrote separately: six racing updates from one snapshot all "won".
4687    fn update_if_unchanged(
4688        &self,
4689        expected: &agentstategraph_reminders::Reminder,
4690        new: &agentstategraph_reminders::Reminder,
4691    ) -> Result<bool, agentstategraph_reminders::ReminderError> {
4692        enum Outcome {
4693            Missing,
4694            Changed,
4695            Written,
4696        }
4697        let outcome = self
4698            .block_on(async {
4699                let mut client = self.client().await?;
4700                let tx = client
4701                    .transaction()
4702                    .await
4703                    .map_err(|e| StorageError::Backend(format!("begin reminder update: {e}")))?;
4704                let row = tx
4705                    .query_opt(
4706                        "SELECT * FROM reminders WHERE tenant_id = $1 AND id = $2 FOR UPDATE",
4707                        &[&self.tenant_id, &expected.id],
4708                    )
4709                    .await
4710                    .map_err(|e| StorageError::Backend(format!("get reminder: {e}")))?;
4711                let Some(row) = row else {
4712                    return Ok(Outcome::Missing);
4713                };
4714                if !agentstategraph_reminders::same_reminder(&pg_row_to_reminder(&row)?, expected) {
4715                    return Ok(Outcome::Changed); // dropping the transaction rolls it back
4716                }
4717                write_reminder(&tx, &self.tenant_id, new).await?;
4718                tx.commit()
4719                    .await
4720                    .map_err(|e| StorageError::Backend(format!("commit reminder update: {e}")))?;
4721                Ok(Outcome::Written)
4722            })
4723            .map_err(|e| agentstategraph_reminders::ReminderError::Store(e.to_string()))?;
4724        match outcome {
4725            Outcome::Missing => Err(agentstategraph_reminders::ReminderError::NotFound(
4726                expected.id.clone(),
4727            )),
4728            Outcome::Changed => Ok(false),
4729            Outcome::Written => Ok(true),
4730        }
4731    }
4732
4733    fn delete(&self, id: &str) -> Result<bool, agentstategraph_reminders::ReminderError> {
4734        self.block_on(async {
4735            let client = self.client().await?;
4736            let n = client
4737                .execute(
4738                    "DELETE FROM reminders WHERE tenant_id = $1 AND id = $2",
4739                    &[&self.tenant_id, &id],
4740                )
4741                .await
4742                .map_err(|e| StorageError::Backend(format!("delete reminder: {e}")))?;
4743            Ok(n > 0)
4744        })
4745        .map_err(|e| agentstategraph_reminders::ReminderError::Store(e.to_string()))
4746    }
4747
4748    fn list(
4749        &self,
4750        filter: &agentstategraph_reminders::ReminderFilter,
4751    ) -> Result<Vec<agentstategraph_reminders::Reminder>, agentstategraph_reminders::ReminderError>
4752    {
4753        // Pre-compute owned values so they outlive the async block.
4754        let status_str = filter
4755            .status
4756            .map(|s| pg_reminder_status_to_str(s).to_string());
4757        let priority_val: Option<i32> = filter.priority_at_most.map(pg_priority_to_i32);
4758        let created_by = filter.created_by.clone();
4759        let due_before = filter.due_before.map(|d| d.to_rfc3339());
4760        let ref_id = filter.ref_id.clone();
4761        let tags = filter.tags.clone();
4762
4763        self.block_on(async {
4764            let client = self.client().await?;
4765
4766            let mut sql = "SELECT * FROM reminders WHERE tenant_id = $1".to_string();
4767            let mut idx = 2usize;
4768            if status_str.is_some() {
4769                sql.push_str(&format!(" AND status = ${idx}"));
4770                idx += 1;
4771            }
4772            if priority_val.is_some() {
4773                sql.push_str(&format!(" AND priority <= ${idx}"));
4774                idx += 1;
4775            }
4776            if created_by.is_some() {
4777                sql.push_str(&format!(" AND created_by = ${idx}"));
4778                idx += 1;
4779            }
4780            if due_before.is_some() {
4781                sql.push_str(&format!(" AND due_at <= ${idx}"));
4782                // idx not needed after this
4783            }
4784            sql.push_str(" ORDER BY priority ASC, due_at ASC");
4785
4786            let mut params: Vec<&(dyn tokio_postgres::types::ToSql + Sync)> = vec![&self.tenant_id];
4787            if let Some(ref s) = status_str {
4788                params.push(s);
4789            }
4790            if let Some(ref p) = priority_val {
4791                params.push(p);
4792            }
4793            if let Some(ref c) = created_by {
4794                params.push(c);
4795            }
4796            if let Some(ref d) = due_before {
4797                params.push(d);
4798            }
4799
4800            let rows = client
4801                .query(&sql, &params)
4802                .await
4803                .map_err(|e| StorageError::Backend(format!("list reminders: {e}")))?;
4804
4805            let mut out = Vec::with_capacity(rows.len());
4806            for row in rows {
4807                let reminder = pg_row_to_reminder(&row)?;
4808                if let Some(ref rid) = ref_id {
4809                    if !reminder.refs.iter().any(|rf| &rf.id == rid) {
4810                        continue;
4811                    }
4812                }
4813                if tags.iter().any(|tag| !reminder.tags.contains(tag)) {
4814                    continue;
4815                }
4816                out.push(reminder);
4817            }
4818            Ok(out)
4819        })
4820        .map_err(|e| agentstategraph_reminders::ReminderError::Store(e.to_string()))
4821    }
4822}
4823
4824#[cfg(test)]
4825mod tests {
4826    use super::*;
4827
4828    /// Verifies the constructor applies the supplied `max_size` to the
4829    /// underlying deadpool. Does NOT open a Postgres connection —
4830    /// `create_pool` only validates config.
4831    #[test]
4832    fn connect_with_pool_size_applies_cap() {
4833        let mut cfg = Config::new();
4834        cfg.url = Some("postgres://localhost/agentstategraph_unused".to_string());
4835        cfg.manager = Some(ManagerConfig {
4836            recycling_method: RecyclingMethod::Fast,
4837        });
4838        cfg.pool = Some(PoolConfig {
4839            max_size: 7,
4840            ..Default::default()
4841        });
4842        let pool = cfg
4843            .create_pool(Some(PoolRuntime::Tokio1), tokio_postgres::NoTls)
4844            .expect("build pool");
4845        assert_eq!(pool.status().max_size, 7);
4846    }
4847
4848    /// Every connection the storage takes from its pool comes through
4849    /// `checkout`, which sets the tenant row-level security checks. A
4850    /// connection taken any other way carries whichever tenant used it last.
4851    #[test]
4852    fn every_checkout_sets_the_tenant() {
4853        let source = include_str!("postgres.rs");
4854        let raw = concat!("pool", ".get()");
4855        let lines: Vec<&str> = source.lines().filter(|l| l.contains(raw)).collect();
4856        assert_eq!(
4857            lines,
4858            [concat!(
4859                "    let client = pool",
4860                ".get().await.map_err(conn_error)?;"
4861            )],
4862            "take connections through `checkout` (or `self.client()`)"
4863        );
4864    }
4865
4866    #[test]
4867    fn literals_and_identifiers_are_quoted() {
4868        assert_eq!(quote_literal("plain"), "E'plain'");
4869        assert_eq!(quote_literal(r"o'brien\x"), r"E'o\'brien\\x'");
4870        assert_eq!(quote_ident("objects"), "\"objects\"");
4871        assert_eq!(quote_ident("a\"b"), "\"a\"\"b\"");
4872    }
4873
4874    fn tls_of(url: &str) -> ConnectionTls {
4875        ConnectionTls::parse(url, None).unwrap()
4876    }
4877
4878    #[test]
4879    fn no_sslmode_stays_plaintext() {
4880        let t = tls_of("postgres://u@h/db");
4881        assert_eq!(t.url, "postgres://u@h/db?sslmode=disable");
4882        assert!(!t.requires_tls);
4883        assert_eq!(t.root_cert, None);
4884        assert_eq!(
4885            tls_of("postgres://u@h/db?application_name=asg").url,
4886            "postgres://u@h/db?application_name=asg&sslmode=disable"
4887        );
4888    }
4889
4890    #[test]
4891    fn verify_modes_become_require_and_sslrootcert_is_taken_out() {
4892        let t = tls_of(
4893            "postgres://u@h/db?sslmode=verify-full&sslrootcert=/etc/ssl/My%20CA.pem&application_name=asg",
4894        );
4895        assert_eq!(
4896            t.url,
4897            "postgres://u@h/db?sslmode=require&application_name=asg"
4898        );
4899        assert!(t.requires_tls);
4900        assert_eq!(
4901            t.root_cert,
4902            Some(std::path::PathBuf::from("/etc/ssl/My CA.pem"))
4903        );
4904        assert!(tls_of("postgresql://h/db?sslmode=verify-ca").requires_tls);
4905        assert!(tls_of("postgres://h/db?sslmode=require").requires_tls);
4906        let prefer = tls_of("postgres://h/db?sslmode=prefer");
4907        assert!(!prefer.requires_tls);
4908        assert_eq!(prefer.url, "postgres://h/db?sslmode=prefer");
4909    }
4910
4911    #[test]
4912    fn key_value_strings_are_handled_too() {
4913        let t = tls_of("host=db.example.com user=asg sslmode=verify-full sslrootcert=/ca.pem");
4914        assert_eq!(t.url, "host=db.example.com user=asg sslmode=require");
4915        assert!(t.requires_tls);
4916        assert_eq!(t.root_cert, Some(std::path::PathBuf::from("/ca.pem")));
4917    }
4918
4919    #[test]
4920    fn pgsslrootcert_is_the_fallback() {
4921        let env = Some("/env/ca.pem".to_string());
4922        let t = ConnectionTls::parse("postgres://h/db?sslmode=require", env.clone()).unwrap();
4923        assert_eq!(t.root_cert, Some(std::path::PathBuf::from("/env/ca.pem")));
4924        let t = ConnectionTls::parse("postgres://h/db?sslrootcert=/url/ca.pem", env).unwrap();
4925        assert_eq!(
4926            t.root_cert,
4927            Some(std::path::PathBuf::from("/url/ca.pem")),
4928            "the connection string wins"
4929        );
4930    }
4931
4932    #[cfg(not(feature = "postgres-tls"))]
4933    #[test]
4934    fn asking_for_tls_without_the_feature_is_an_error() {
4935        let err = PostgresStorage::with_pool("postgres://h/db?sslmode=require", "t", 1)
4936            .err()
4937            .expect("refused");
4938        assert!(format!("{err:?}").contains("postgres-tls"), "{err:?}");
4939        assert!(
4940            PostgresStorage::with_pool("postgres://h/db?sslmode=prefer", "t", 1).is_ok(),
4941            "prefer may fall back to plaintext"
4942        );
4943    }
4944
4945    #[cfg(feature = "postgres-tls")]
4946    #[test]
4947    fn an_unreadable_sslrootcert_is_a_clear_error() {
4948        let err = PostgresStorage::with_pool(
4949            "postgres://h/db?sslmode=require&sslrootcert=/nonexistent/ca.pem",
4950            "t",
4951            1,
4952        )
4953        .err()
4954        .expect("refused");
4955        assert!(format!("{err:?}").contains("sslrootcert"), "{err:?}");
4956    }
4957
4958    #[test]
4959    fn default_pool_size_is_32() {
4960        assert_eq!(DEFAULT_POOL_SIZE, 32);
4961    }
4962
4963    // -----------------------------------------------------------------------
4964    // ReminderStore helper unit tests (no live Postgres connection needed)
4965    // -----------------------------------------------------------------------
4966
4967    use agentstategraph_reminders::types::{Priority, ReminderStatus};
4968
4969    #[test]
4970    fn pg_reminder_status_round_trip() {
4971        let statuses = [
4972            ReminderStatus::Pending,
4973            ReminderStatus::Due,
4974            ReminderStatus::AwaitingPermission,
4975            ReminderStatus::InProgress,
4976            ReminderStatus::Completed,
4977            ReminderStatus::Snoozed,
4978            ReminderStatus::Cancelled,
4979        ];
4980        for s in statuses {
4981            let encoded = pg_reminder_status_to_str(s);
4982            let decoded = pg_reminder_status_from_str(encoded)
4983                .unwrap_or_else(|e| panic!("decode failed for {s:?}: {e}"));
4984            assert_eq!(decoded, s, "status round-trip failed for {s:?}");
4985        }
4986    }
4987
4988    #[test]
4989    fn pg_reminder_status_from_str_rejects_unknown() {
4990        assert!(pg_reminder_status_from_str("bogus").is_err());
4991    }
4992
4993    #[test]
4994    fn pg_priority_round_trip() {
4995        let priorities = [
4996            Priority::Critical,
4997            Priority::High,
4998            Priority::Medium,
4999            Priority::Low,
5000            Priority::Minimal,
5001        ];
5002        for p in priorities {
5003            let encoded = pg_priority_to_i32(p);
5004            let decoded = pg_priority_from_i32(encoded)
5005                .unwrap_or_else(|e| panic!("decode failed for {p:?}: {e}"));
5006            assert_eq!(decoded, p, "priority round-trip failed for {p:?}");
5007        }
5008    }
5009
5010    #[test]
5011    fn pg_priority_from_i32_rejects_out_of_range() {
5012        assert!(pg_priority_from_i32(0).is_err());
5013        assert!(pg_priority_from_i32(6).is_err());
5014        assert!(pg_priority_from_i32(-1).is_err());
5015    }
5016
5017    #[test]
5018    fn pg_priority_values_match_ordinal() {
5019        assert_eq!(pg_priority_to_i32(Priority::Critical), 1);
5020        assert_eq!(pg_priority_to_i32(Priority::High), 2);
5021        assert_eq!(pg_priority_to_i32(Priority::Medium), 3);
5022        assert_eq!(pg_priority_to_i32(Priority::Low), 4);
5023        assert_eq!(pg_priority_to_i32(Priority::Minimal), 5);
5024    }
5025
5026    #[test]
5027    fn pg_reminder_status_strings_are_snake_case() {
5028        assert_eq!(
5029            pg_reminder_status_to_str(ReminderStatus::AwaitingPermission),
5030            "awaiting_permission"
5031        );
5032        assert_eq!(
5033            pg_reminder_status_to_str(ReminderStatus::InProgress),
5034            "in_progress"
5035        );
5036    }
5037}