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}_artifacts (
119                scope TEXT NOT NULL,
120                artifact_id TEXT NOT NULL,
121                media_type TEXT NOT NULL,
122                size_bytes BIGINT NOT NULL CHECK (size_bytes >= 0),
123                sha256 TEXT NOT NULL,
124                name TEXT,
125                bytes BYTEA NOT NULL,
126                created_at_ms BIGINT NOT NULL CHECK (created_at_ms >= 0),
127                expires_at_ms BIGINT CHECK (expires_at_ms >= 0),
128                PRIMARY KEY (scope, artifact_id)
129            );
130            CREATE INDEX IF NOT EXISTS {table}_artifacts_scope_expiry
131                ON {table}_artifacts (scope, expires_at_ms, artifact_id);
132
133            CREATE TABLE IF NOT EXISTS {table}_artifact_idempotency (
134                scope TEXT NOT NULL,
135                idempotency_key TEXT NOT NULL,
136                artifact_id TEXT NOT NULL,
137                PRIMARY KEY (scope, idempotency_key),
138                FOREIGN KEY (scope, artifact_id)
139                    REFERENCES {table}_artifacts(scope, artifact_id) ON DELETE CASCADE
140            );
141            "
142        )
143    }
144}
runifold_store_postgres/conversation/schema.rs

runifold_store_postgres/conversation/
schema.rs
