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        )
119    }
120}
runifold_store_postgres/conversation/schema.rs

runifold_store_postgres/conversation/
schema.rs
