runifold_store_postgres/conversation/
schema.rs

1//! Conversation and semantic-memory schema management.
2
3use std::num::NonZeroU32;
4
5use super::{PostgresConversationStore, PostgresConversationStoreError};
6
7impl PostgresConversationStore {
8    /// Explicitly creates conversation, transcript, and semantic-memory tables.
9    ///
10    /// Runtime operations never perform hidden migrations.
11    ///
12    /// # Errors
13    ///
14    /// Propagates `PostgreSQL` DDL failures.
15    pub async fn ensure_schema(&self) -> Result<(), PostgresConversationStoreError> {
16        self.client
17            .batch_execute(&Self::schema_sql(&self.table))
18            .await?;
19        Ok(())
20    }
21
22    /// Adds a nullable pgvector column and cosine HNSW index for semantic memory.
23    ///
24    /// Call this explicitly after [`Self::ensure_schema`] and before enabling
25    /// a vector-configured store in production.
26    ///
27    /// # Errors
28    ///
29    /// Propagates extension and DDL failures.
30    pub async fn ensure_semantic_memory_vector_schema(
31        &self,
32        dimensions: NonZeroU32,
33    ) -> Result<(), PostgresConversationStoreError> {
34        self.client
35            .batch_execute(&format!(
36                "CREATE EXTENSION IF NOT EXISTS vector;\
37                 ALTER TABLE {0}_memory ADD COLUMN IF NOT EXISTS \
38                    embedding vector({1});\
39                 CREATE INDEX IF NOT EXISTS {0}_memory_embedding_hnsw \
40                    ON {0}_memory USING hnsw (embedding vector_cosine_ops);",
41                self.table,
42                dimensions.get()
43            ))
44            .await?;
45        Ok(())
46    }
47
48    pub(in crate::conversation) fn schema_sql(table: &str) -> String {
49        format!(
50            r"
51            CREATE TABLE IF NOT EXISTS {table} (
52                conversation_id UUID PRIMARY KEY,
53                namespace TEXT NOT NULL,
54                version BIGINT NOT NULL DEFAULT 0 CHECK (version >= 0),
55                summary_id UUID,
56                summary_content TEXT,
57                summary_through BIGINT CHECK (summary_through > 0),
58                summary_transcript_version BIGINT CHECK (summary_transcript_version >= 0),
59                summary_created_at TIMESTAMPTZ,
60                created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
61                updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
62                CHECK (
63                    (summary_id IS NULL AND summary_content IS NULL
64                        AND summary_through IS NULL
65                        AND summary_transcript_version IS NULL
66                        AND summary_created_at IS NULL)
67                    OR
68                    (summary_id IS NOT NULL AND summary_content IS NOT NULL
69                        AND summary_through IS NOT NULL
70                        AND summary_transcript_version IS NOT NULL
71                        AND summary_created_at IS NOT NULL)
72                )
73            );
74            CREATE INDEX IF NOT EXISTS {table}_namespace_idx
75                ON {table} (namespace, conversation_id);
76
77            CREATE TABLE IF NOT EXISTS {table}_transcript (
78                conversation_id UUID NOT NULL REFERENCES {table}(conversation_id)
79                    ON DELETE CASCADE,
80                sequence BIGINT NOT NULL CHECK (sequence > 0),
81                message JSONB NOT NULL,
82                created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
83                PRIMARY KEY (conversation_id, sequence)
84            );
85
86            CREATE TABLE IF NOT EXISTS {table}_memory (
87                memory_id UUID PRIMARY KEY,
88                namespace TEXT NOT NULL,
89                content TEXT NOT NULL,
90                sources JSONB NOT NULL,
91                metadata JSONB NOT NULL,
92                revision BIGINT NOT NULL CHECK (revision >= 0),
93                created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
94                updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
95            );
96            CREATE INDEX IF NOT EXISTS {table}_memory_namespace_idx
97                ON {table}_memory (namespace, updated_at DESC, memory_id);
98            CREATE INDEX IF NOT EXISTS {table}_memory_search_idx
99                ON {table}_memory USING GIN (to_tsvector('simple', content));
100
101            CREATE TABLE IF NOT EXISTS {table}_checkpoints (
102                checkpoint_id UUID PRIMARY KEY,
103                revision BIGINT NOT NULL CHECK (revision >= 0),
104                record_json JSONB NOT NULL
105            );
106
107            CREATE TABLE IF NOT EXISTS {table}_effects (
108                effect_id UUID PRIMARY KEY,
109                capability_id UUID NOT NULL,
110                idempotency_key TEXT,
111                revision BIGINT NOT NULL CHECK (revision >= 0),
112                record_json JSONB NOT NULL,
113                UNIQUE (capability_id, idempotency_key)
114            );
115            CREATE INDEX IF NOT EXISTS {table}_effects_capability_key
116                ON {table}_effects (capability_id, idempotency_key);
117
118            CREATE TABLE IF NOT EXISTS {table}_events (
119                event_id UUID PRIMARY KEY,
120                run_id UUID NOT NULL,
121                sequence BIGINT NOT NULL CHECK (sequence >= 0),
122                event_json JSONB NOT NULL,
123                UNIQUE (run_id, sequence)
124            );
125            CREATE INDEX IF NOT EXISTS {table}_events_run_sequence
126                ON {table}_events (run_id, sequence);
127
128            CREATE TABLE IF NOT EXISTS {table}_artifacts (
129                scope TEXT NOT NULL,
130                artifact_id TEXT NOT NULL,
131                media_type TEXT NOT NULL,
132                size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0),
133                sha256 TEXT NOT NULL,
134                name TEXT,
135                bytes BYTEA NOT NULL,
136                created_at_ms BIGINT NOT NULL CHECK (created_at_ms >= 0),
137                expires_at_ms BIGINT CHECK (expires_at_ms >= 0),
138                PRIMARY KEY (scope, artifact_id)
139            );
140            CREATE INDEX IF NOT EXISTS {table}_artifacts_scope_expiry
141                ON {table}_artifacts (scope, expires_at_ms, artifact_id);
142
143            CREATE TABLE IF NOT EXISTS {table}_artifact_idempotency (
144                scope TEXT NOT NULL,
145                idempotency_key TEXT NOT NULL,
146                artifact_id TEXT NOT NULL,
147                PRIMARY KEY (scope, idempotency_key),
148                FOREIGN KEY (scope, artifact_id)
149                    REFERENCES {table}_artifacts(scope, artifact_id) ON DELETE CASCADE
150            );
151            "
152        )
153    }
154}
runifold_store_postgres/conversation/schema.rs

runifold_store_postgres/conversation/
schema.rs
