Mercurial

comparison mrjunejune/conversation_store.c @ 264:04fee26ecce0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
add authenticated JRPG conversation platform Add reusable auth/session storage, owned conversation recovery, guest quotas, admin workflows, URL-routed conversation UI, mobile frame support, and parallel browser acceptance. Co-authored-by: Copilot <[email protected]>
author MrJuneJune <me@mrjunejune.com>
date Fri, 07 Aug 2026 07:34:12 -0700
parents 1f9877b637e9
children
comparison
equal deleted inserted replaced
263:ee04e4e69fed 264:04fee26ecce0
85 Conversation_Store *p_store, 85 Conversation_Store *p_store,
86 Conversation_Store_Result result) 86 Conversation_Store_Result result)
87 { 87 {
88 Deita_Query_Execute_Update(p_store->p_connection, "ROLLBACK"); 88 Deita_Query_Execute_Update(p_store->p_connection, "ROLLBACK");
89 return result; 89 return result;
90 }
91
92 /* ------------------------------------------------------------------ */
93 /* Schema migration helpers (called from Conversation_Store_Create) */
94 /* ------------------------------------------------------------------ */
95
96 static const char *Conversation_Store_Owner_Kind_String(
97 Conversation_Owner_Kind kind)
98 {
99 switch (kind)
100 {
101 case CONVERSATION_OWNER_KIND_USER: return "user";
102 case CONVERSATION_OWNER_KIND_GUEST: return "guest";
103 case CONVERSATION_OWNER_KIND_LEGACY: return "legacy";
104 default: return "legacy";
105 }
106 }
107
108 /* Returns TRUE if column exists in table (mutex must NOT be held). */
109 static boolean Conversation_Store_Column_Exists(
110 Conversation_Store *p_store,
111 const char *table_name,
112 const char *column_name)
113 {
114 char sql[256];
115 snprintf(sql, sizeof(sql),
116 "SELECT 1 FROM pragma_table_info('%s') WHERE name = '%s'",
117 table_name, column_name);
118 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
119 if (!p_arena)
120 return FALSE;
121 Deita_Result_Set *p_result =
122 Deita_Query_Execute_Prepared(p_store->p_connection, sql, 0, NULL, p_arena);
123 boolean exists = p_result && Deita_Result_Set_Next(p_result);
124 if (p_result)
125 Deita_Result_Set_Free(p_result);
126 Dowa_Arena_Free(p_arena);
127 return exists;
128 }
129
130 /*
131 * Migration 1: add owner_kind / owner_id columns and listing index.
132 * Existing rows become owner_kind='legacy', owner_id=NULL.
133 * Safe to call on a database that was created by new code (idempotent).
134 */
135 static boolean Conversation_Store_Apply_Migration_1(
136 Conversation_Store *p_store)
137 {
138 /* Check migrations ledger */
139 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
140 if (!p_arena)
141 return FALSE;
142 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
143 p_store->p_connection,
144 "SELECT 1 FROM conversation_schema_migrations WHERE version = 1",
145 0, NULL, p_arena);
146 boolean already_done = p_result && Deita_Result_Set_Next(p_result);
147 if (p_result)
148 Deita_Result_Set_Free(p_result);
149 Dowa_Arena_Free(p_arena);
150 if (already_done)
151 return TRUE;
152
153 if (!Conversation_Store_Column_Exists(p_store, "conversations", "owner_kind"))
154 {
155 if (Deita_Query_Execute_Update(
156 p_store->p_connection,
157 "ALTER TABLE conversations ADD COLUMN owner_kind TEXT "
158 "NOT NULL DEFAULT 'legacy'") < 0)
159 return FALSE;
160 }
161 if (!Conversation_Store_Column_Exists(p_store, "conversations", "owner_id"))
162 {
163 if (Deita_Query_Execute_Update(
164 p_store->p_connection,
165 "ALTER TABLE conversations ADD COLUMN owner_id TEXT") < 0)
166 return FALSE;
167 }
168 if (Deita_Query_Execute_Update(
169 p_store->p_connection,
170 "CREATE INDEX IF NOT EXISTS idx_conversations_owner_listing "
171 "ON conversations(owner_kind, owner_id, updated_at DESC, id)") < 0)
172 return FALSE;
173 if (Deita_Query_Execute_Update(
174 p_store->p_connection,
175 "INSERT OR IGNORE INTO conversation_schema_migrations (version) "
176 "VALUES (1)") < 0)
177 return FALSE;
178 return TRUE;
179 }
180
181 /*
182 * Migration 2: create the guest-to-user transfer mapping table.
183 * Records a permanent mapping from guest_id → user_id set at transfer time.
184 * Used by Create_Owned to redirect stale guest creates to the mapped user.
185 */
186 static boolean Conversation_Store_Apply_Migration_2(
187 Conversation_Store *p_store)
188 {
189 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
190 if (!p_arena)
191 return FALSE;
192 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
193 p_store->p_connection,
194 "SELECT 1 FROM conversation_schema_migrations WHERE version = 2",
195 0, NULL, p_arena);
196 boolean already_done = p_result && Deita_Result_Set_Next(p_result);
197 if (p_result)
198 Deita_Result_Set_Free(p_result);
199 Dowa_Arena_Free(p_arena);
200 if (already_done)
201 return TRUE;
202
203 if (Deita_Query_Execute_Update(
204 p_store->p_connection,
205 "CREATE TABLE IF NOT EXISTS conversation_guest_transfers ("
206 "guest_id TEXT PRIMARY KEY,"
207 "user_id TEXT NOT NULL,"
208 "transferred_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))"
209 ")") < 0)
210 return FALSE;
211 if (Deita_Query_Execute_Update(
212 p_store->p_connection,
213 "INSERT OR IGNORE INTO conversation_schema_migrations (version) "
214 "VALUES (2)") < 0)
215 return FALSE;
216 return TRUE;
217 }
218
219 /*
220 * Migration 3: recreate the owner listing index with id DESC so that
221 * keyset pagination is stable when updated_at values collide.
222 * Drops and recreates the index atomically from the migration ledger's
223 * perspective; safe to run on any database that has migration 1 applied.
224 */
225 static boolean Conversation_Store_Apply_Migration_3(
226 Conversation_Store *p_store)
227 {
228 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
229 if (!p_arena)
230 return FALSE;
231 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
232 p_store->p_connection,
233 "SELECT 1 FROM conversation_schema_migrations WHERE version = 3",
234 0, NULL, p_arena);
235 boolean already_done = p_result && Deita_Result_Set_Next(p_result);
236 if (p_result)
237 Deita_Result_Set_Free(p_result);
238 Dowa_Arena_Free(p_arena);
239 if (already_done)
240 return TRUE;
241
242 if (Deita_Query_Execute_Update(
243 p_store->p_connection,
244 "DROP INDEX IF EXISTS idx_conversations_owner_listing") < 0)
245 return FALSE;
246 if (Deita_Query_Execute_Update(
247 p_store->p_connection,
248 "CREATE INDEX IF NOT EXISTS idx_conversations_owner_listing "
249 "ON conversations(owner_kind, owner_id, updated_at DESC, id DESC)") < 0)
250 return FALSE;
251 if (Deita_Query_Execute_Update(
252 p_store->p_connection,
253 "INSERT OR IGNORE INTO conversation_schema_migrations (version) "
254 "VALUES (3)") < 0)
255 return FALSE;
256 return TRUE;
90 } 257 }
91 258
92 Conversation_Store *Conversation_Store_Create(const char *database_path) 259 Conversation_Store *Conversation_Store_Create(const char *database_path)
93 { 260 {
94 if (!database_path) 261 if (!database_path)
148 "ON conversation_turns(conversation_id) " 315 "ON conversation_turns(conversation_id) "
149 "WHERE role = 'assistant' AND status = 'active';" 316 "WHERE role = 'assistant' AND status = 'active';"
150 "CREATE INDEX IF NOT EXISTS idx_conversations_updated " 317 "CREATE INDEX IF NOT EXISTS idx_conversations_updated "
151 "ON conversations(updated_at DESC);" 318 "ON conversations(updated_at DESC);"
152 "CREATE INDEX IF NOT EXISTS idx_turns_conversation_sequence " 319 "CREATE INDEX IF NOT EXISTS idx_turns_conversation_sequence "
153 "ON conversation_turns(conversation_id, sequence);"; 320 "ON conversation_turns(conversation_id, sequence);"
321 "CREATE TABLE IF NOT EXISTS conversation_schema_migrations ("
322 "version INTEGER PRIMARY KEY,"
323 "applied_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))"
324 ");";
154 if (Deita_Query_Execute_Update(p_store->p_connection, schema) < 0) 325 if (Deita_Query_Execute_Update(p_store->p_connection, schema) < 0)
326 {
327 Conversation_Store_Destroy(p_store);
328 return NULL;
329 }
330 if (!Conversation_Store_Apply_Migration_1(p_store))
331 {
332 Conversation_Store_Destroy(p_store);
333 return NULL;
334 }
335 if (!Conversation_Store_Apply_Migration_2(p_store))
336 {
337 Conversation_Store_Destroy(p_store);
338 return NULL;
339 }
340 if (!Conversation_Store_Apply_Migration_3(p_store))
155 { 341 {
156 Conversation_Store_Destroy(p_store); 342 Conversation_Store_Destroy(p_store);
157 return NULL; 343 return NULL;
158 } 344 }
159 if (Deita_Query_Execute_Update( 345 if (Deita_Query_Execute_Update(
167 Conversation_Store_Destroy(p_store); 353 Conversation_Store_Destroy(p_store);
168 return NULL; 354 return NULL;
169 } 355 }
170 return p_store; 356 return p_store;
171 } 357 }
358
359
172 360
173 void Conversation_Store_Destroy(Conversation_Store *p_store) 361 void Conversation_Store_Destroy(Conversation_Store *p_store)
174 { 362 {
175 if (!p_store) 363 if (!p_store)
176 return; 364 return;
492 pthread_mutex_unlock(&p_store->mutex); 680 pthread_mutex_unlock(&p_store->mutex);
493 if (result < 0) 681 if (result < 0)
494 return CONVERSATION_STORE_ERROR; 682 return CONVERSATION_STORE_ERROR;
495 return result == 0 ? CONVERSATION_STORE_NOT_FOUND : CONVERSATION_STORE_OK; 683 return result == 0 ? CONVERSATION_STORE_NOT_FOUND : CONVERSATION_STORE_OK;
496 } 684 }
685
686 /* ------------------------------------------------------------------ */
687 /* Owner-aware APIs */
688 /* ------------------------------------------------------------------ */
689
690 Conversation_Store_Result Conversation_Store_Create_Owned(
691 Conversation_Store *p_store,
692 const char *title,
693 const Conversation_Owner *p_owner,
694 char output_id[37])
695 {
696 if (!p_store || !p_owner || !output_id)
697 return CONVERSATION_STORE_ERROR;
698 if (p_owner->kind == CONVERSATION_OWNER_KIND_LEGACY ||
699 p_owner->id[0] == '\0')
700 return CONVERSATION_STORE_ERROR;
701 if (!Conversation_Store_Generate_UUID(output_id))
702 return CONVERSATION_STORE_ERROR;
703
704 /* Resolved owner fields — may be overridden by transfer mapping below */
705 const char *resolved_kind = Conversation_Store_Owner_Kind_String(p_owner->kind);
706 char resolved_id[37];
707 strncpy(resolved_id, p_owner->id, 36);
708 resolved_id[36] = '\0';
709
710 pthread_mutex_lock(&p_store->mutex);
711 if (Deita_Query_Execute_Update(
712 p_store->p_connection, "BEGIN IMMEDIATE") < 0)
713 {
714 pthread_mutex_unlock(&p_store->mutex);
715 return CONVERSATION_STORE_ERROR;
716 }
717
718 /* For guest owners: check transfer mapping within the same transaction.
719 * If the guest was transferred to a user, assign to that user instead. */
720 if (p_owner->kind == CONVERSATION_OWNER_KIND_GUEST)
721 {
722 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
723 if (!p_arena)
724 {
725 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
726 pthread_mutex_unlock(&p_store->mutex);
727 return CONVERSATION_STORE_ERROR;
728 }
729 const char *check_params[] = {p_owner->id};
730 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
731 p_store->p_connection,
732 "SELECT user_id FROM conversation_guest_transfers WHERE guest_id = ?",
733 1, check_params, p_arena);
734 if (p_result && Deita_Result_Set_Next(p_result))
735 {
736 const char *mapped_user = Deita_Result_Set_Get_Text(p_result, 0);
737 if (mapped_user && mapped_user[0] != '\0')
738 {
739 strncpy(resolved_id, mapped_user, 36);
740 resolved_id[36] = '\0';
741 resolved_kind = "user";
742 }
743 }
744 if (p_result)
745 Deita_Result_Set_Free(p_result);
746 Dowa_Arena_Free(p_arena);
747 }
748
749 const char *parameters[] = {
750 output_id,
751 output_id,
752 title ? title : "",
753 resolved_kind,
754 resolved_id,
755 };
756 int32 result = Deita_Query_Execute_Update_Prepared(
757 p_store->p_connection,
758 "INSERT INTO conversations "
759 "(id, copilot_session_id, title, owner_kind, owner_id) "
760 "VALUES (?, ?, ?, ?, ?)",
761 5,
762 parameters);
763 if (result < 0 ||
764 Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
765 {
766 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
767 pthread_mutex_unlock(&p_store->mutex);
768 return CONVERSATION_STORE_ERROR;
769 }
770 pthread_mutex_unlock(&p_store->mutex);
771 return result < 0 ? CONVERSATION_STORE_ERROR : CONVERSATION_STORE_OK;
772 }
773
774 Conversation_Store_Result Conversation_Store_Get_Owned(
775 Conversation_Store *p_store,
776 const char *conversation_id,
777 const Conversation_Owner *p_owner,
778 Conversation_Record *p_record,
779 Dowa_Arena *p_arena)
780 {
781 if (!p_store || !conversation_id || !p_owner || !p_record || !p_arena)
782 return CONVERSATION_STORE_ERROR;
783 if (p_owner->kind == CONVERSATION_OWNER_KIND_LEGACY ||
784 p_owner->id[0] == '\0')
785 return CONVERSATION_STORE_ERROR;
786 memset(p_record, 0, sizeof(*p_record));
787
788 const char *kind_str = Conversation_Store_Owner_Kind_String(p_owner->kind);
789 const char *parameters[] = {conversation_id, kind_str, p_owner->id};
790 pthread_mutex_lock(&p_store->mutex);
791 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
792 p_store->p_connection,
793 "SELECT id, copilot_session_id, title, status, created_at, updated_at "
794 "FROM conversations "
795 "WHERE id = ? AND status != 'deleted' "
796 "AND owner_kind = ? AND owner_id = ?",
797 3,
798 parameters,
799 p_arena);
800 if (!p_result || !Deita_Result_Set_Next(p_result))
801 {
802 if (p_result)
803 Deita_Result_Set_Free(p_result);
804 pthread_mutex_unlock(&p_store->mutex);
805 return CONVERSATION_STORE_NOT_FOUND;
806 }
807 p_record->id = Conversation_Store_Copy_Text(
808 Deita_Result_Set_Get_Text(p_result, 0), p_arena);
809 p_record->copilot_session_id = Conversation_Store_Copy_Text(
810 Deita_Result_Set_Get_Text(p_result, 1), p_arena);
811 p_record->title = Conversation_Store_Copy_Text(
812 Deita_Result_Set_Get_Text(p_result, 2), p_arena);
813 p_record->status = Conversation_Store_Copy_Text(
814 Deita_Result_Set_Get_Text(p_result, 3), p_arena);
815 p_record->created_at = Deita_Result_Set_Get_Integer(p_result, 4);
816 p_record->updated_at = Deita_Result_Set_Get_Integer(p_result, 5);
817 Deita_Result_Set_Free(p_result);
818
819 const char *turn_params[] = {conversation_id};
820 p_result = Deita_Query_Execute_Prepared(
821 p_store->p_connection,
822 "SELECT id, sequence, role, content, status, request_id, "
823 "error_message, input_tokens, output_tokens, created_at, completed_at "
824 "FROM (SELECT id, sequence, role, content, status, request_id, "
825 "error_message, input_tokens, output_tokens, created_at, completed_at "
826 "FROM conversation_turns WHERE conversation_id = ? "
827 "ORDER BY sequence DESC LIMIT 20) ORDER BY sequence",
828 1,
829 turn_params,
830 p_arena);
831 if (!p_result)
832 {
833 pthread_mutex_unlock(&p_store->mutex);
834 return CONVERSATION_STORE_ERROR;
835 }
836 while (Deita_Result_Set_Next(p_result))
837 {
838 Conversation_Turn turn = {0};
839 turn.id = Deita_Result_Set_Get_Integer(p_result, 0);
840 turn.sequence = Deita_Result_Set_Get_Integer(p_result, 1);
841 turn.role = Conversation_Store_Copy_Text(
842 Deita_Result_Set_Get_Text(p_result, 2), p_arena);
843 turn.content = Conversation_Store_Copy_Text(
844 Deita_Result_Set_Get_Text(p_result, 3), p_arena);
845 turn.status = Conversation_Store_Copy_Text(
846 Deita_Result_Set_Get_Text(p_result, 4), p_arena);
847 turn.request_id = Conversation_Store_Copy_Text(
848 Deita_Result_Set_Get_Text(p_result, 5), p_arena);
849 turn.error_message = Conversation_Store_Copy_Text(
850 Deita_Result_Set_Get_Text(p_result, 6), p_arena);
851 turn.input_tokens = Deita_Result_Set_Get_Integer(p_result, 7);
852 turn.output_tokens = Deita_Result_Set_Get_Integer(p_result, 8);
853 turn.created_at = Deita_Result_Set_Get_Integer(p_result, 9);
854 turn.completed_at = Deita_Result_Set_Get_Integer(p_result, 10);
855 Dowa_Array_Push_Arena(p_record->turns, turn, p_arena);
856 }
857 boolean turns_error = Deita_Result_Set_Has_Error(p_result);
858 Deita_Result_Set_Free(p_result);
859 pthread_mutex_unlock(&p_store->mutex);
860 return turns_error ? CONVERSATION_STORE_ERROR : CONVERSATION_STORE_OK;
861 }
862
863 Conversation_Store_Result Conversation_Store_Update_Title_Owned(
864 Conversation_Store *p_store,
865 const char *conversation_id,
866 const Conversation_Owner *p_owner,
867 const char *title)
868 {
869 if (!p_store || !conversation_id || !p_owner || !title)
870 return CONVERSATION_STORE_ERROR;
871 if (p_owner->kind == CONVERSATION_OWNER_KIND_LEGACY ||
872 p_owner->id[0] == '\0')
873 return CONVERSATION_STORE_ERROR;
874
875 const char *kind_str = Conversation_Store_Owner_Kind_String(p_owner->kind);
876 const char *parameters[] = {title, conversation_id, kind_str, p_owner->id};
877 pthread_mutex_lock(&p_store->mutex);
878 int32 result = Deita_Query_Execute_Update_Prepared(
879 p_store->p_connection,
880 "UPDATE conversations SET title = ?, "
881 "updated_at = strftime('%s','now') "
882 "WHERE id = ? AND status != 'deleted' "
883 "AND owner_kind = ? AND owner_id = ?",
884 4,
885 parameters);
886 pthread_mutex_unlock(&p_store->mutex);
887 if (result < 0)
888 return CONVERSATION_STORE_ERROR;
889 return result == 0 ? CONVERSATION_STORE_NOT_FOUND : CONVERSATION_STORE_OK;
890 }
891
892 Conversation_Store_Result Conversation_Store_Delete_Owned(
893 Conversation_Store *p_store,
894 const char *conversation_id,
895 const Conversation_Owner *p_owner)
896 {
897 if (!p_store || !conversation_id || !p_owner)
898 return CONVERSATION_STORE_ERROR;
899 if (p_owner->kind == CONVERSATION_OWNER_KIND_LEGACY ||
900 p_owner->id[0] == '\0')
901 return CONVERSATION_STORE_ERROR;
902
903 const char *kind_str = Conversation_Store_Owner_Kind_String(p_owner->kind);
904 const char *parameters[] = {conversation_id, kind_str, p_owner->id};
905 pthread_mutex_lock(&p_store->mutex);
906 int32 result = Deita_Query_Execute_Update_Prepared(
907 p_store->p_connection,
908 "DELETE FROM conversations WHERE id = ? "
909 "AND owner_kind = ? AND owner_id = ?",
910 3,
911 parameters);
912 pthread_mutex_unlock(&p_store->mutex);
913 if (result < 0)
914 return CONVERSATION_STORE_ERROR;
915 return result == 0 ? CONVERSATION_STORE_NOT_FOUND : CONVERSATION_STORE_OK;
916 }
917
918 /* Checks conversation ownership without loading turns (called under mutex). */
919 static boolean Conversation_Store_Owns_Locked(
920 Conversation_Store *p_store,
921 const char *conversation_id,
922 const Conversation_Owner *p_owner)
923 {
924 const char *kind_str = Conversation_Store_Owner_Kind_String(p_owner->kind);
925 const char *parameters[] = {conversation_id, kind_str, p_owner->id};
926 Dowa_Arena *p_arena = Dowa_Arena_Create(1024);
927 if (!p_arena)
928 return FALSE;
929 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
930 p_store->p_connection,
931 "SELECT 1 FROM conversations "
932 "WHERE id = ? AND status != 'deleted' "
933 "AND owner_kind = ? AND owner_id = ?",
934 3,
935 parameters,
936 p_arena);
937 boolean owns = p_result && Deita_Result_Set_Next(p_result);
938 if (p_result)
939 Deita_Result_Set_Free(p_result);
940 Dowa_Arena_Free(p_arena);
941 return owns;
942 }
943
944 Conversation_Store_Result Conversation_Store_Begin_Turn_Owned(
945 Conversation_Store *p_store,
946 const char *conversation_id,
947 const Conversation_Owner *p_owner,
948 const char *request_id,
949 const char *prompt)
950 {
951 if (!p_store || !conversation_id || !p_owner || !request_id || !prompt)
952 return CONVERSATION_STORE_ERROR;
953 if (p_owner->kind == CONVERSATION_OWNER_KIND_LEGACY ||
954 p_owner->id[0] == '\0')
955 return CONVERSATION_STORE_ERROR;
956
957 pthread_mutex_lock(&p_store->mutex);
958 if (Deita_Query_Execute_Update(
959 p_store->p_connection, "BEGIN IMMEDIATE") < 0)
960 {
961 pthread_mutex_unlock(&p_store->mutex);
962 return CONVERSATION_STORE_ERROR;
963 }
964 if (!Conversation_Store_Owns_Locked(p_store, conversation_id, p_owner))
965 {
966 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_NOT_FOUND);
967 pthread_mutex_unlock(&p_store->mutex);
968 return CONVERSATION_STORE_NOT_FOUND;
969 }
970
971 Dowa_Arena *p_arena = Dowa_Arena_Create(2048);
972 const char *seq_params[] = {conversation_id};
973 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
974 p_store->p_connection,
975 "SELECT COALESCE(MAX(sequence), 0), "
976 "SUM(CASE WHEN role = 'assistant' AND status = 'active' "
977 "THEN 1 ELSE 0 END) "
978 "FROM conversation_turns WHERE conversation_id = ?",
979 1,
980 seq_params,
981 p_arena);
982 if (!p_result || !Deita_Result_Set_Next(p_result))
983 {
984 if (p_result)
985 Deita_Result_Set_Free(p_result);
986 Dowa_Arena_Free(p_arena);
987 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
988 pthread_mutex_unlock(&p_store->mutex);
989 return CONVERSATION_STORE_ERROR;
990 }
991 int64 next_sequence = Deita_Result_Set_Get_Integer(p_result, 0) + 1;
992 int64 active_count = Deita_Result_Set_Get_Integer(p_result, 1);
993 Deita_Result_Set_Free(p_result);
994 Dowa_Arena_Free(p_arena);
995 if (active_count > 0)
996 {
997 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_CONFLICT);
998 pthread_mutex_unlock(&p_store->mutex);
999 return CONVERSATION_STORE_CONFLICT;
1000 }
1001
1002 char user_sequence[32];
1003 char assistant_sequence[32];
1004 snprintf(user_sequence, sizeof(user_sequence), "%lld",
1005 (long long)next_sequence);
1006 snprintf(assistant_sequence, sizeof(assistant_sequence), "%lld",
1007 (long long)(next_sequence + 1));
1008 const char *user_params[] = {
1009 conversation_id, user_sequence, prompt, request_id,
1010 };
1011 if (Deita_Query_Execute_Update_Prepared(
1012 p_store->p_connection,
1013 "INSERT INTO conversation_turns "
1014 "(conversation_id, sequence, role, content, status, request_id, "
1015 "completed_at) VALUES (?, ?, 'user', ?, 'complete', ?, "
1016 "strftime('%s','now'))",
1017 4,
1018 user_params) < 0)
1019 {
1020 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1021 pthread_mutex_unlock(&p_store->mutex);
1022 return CONVERSATION_STORE_ERROR;
1023 }
1024 const char *asst_params[] = {conversation_id, assistant_sequence, request_id};
1025 const char *conv_params[] = {conversation_id};
1026 if (Deita_Query_Execute_Update_Prepared(
1027 p_store->p_connection,
1028 "INSERT INTO conversation_turns "
1029 "(conversation_id, sequence, role, status, request_id) "
1030 "VALUES (?, ?, 'assistant', 'active', ?)",
1031 3,
1032 asst_params) < 0 ||
1033 Deita_Query_Execute_Update_Prepared(
1034 p_store->p_connection,
1035 "UPDATE conversations SET updated_at = strftime('%s','now') "
1036 "WHERE id = ?",
1037 1,
1038 conv_params) < 0 ||
1039 Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
1040 {
1041 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1042 pthread_mutex_unlock(&p_store->mutex);
1043 return CONVERSATION_STORE_ERROR;
1044 }
1045 pthread_mutex_unlock(&p_store->mutex);
1046 return CONVERSATION_STORE_OK;
1047 }
1048
1049 Conversation_Store_Result Conversation_Store_List(
1050 Conversation_Store *p_store,
1051 const Conversation_Owner *p_owner,
1052 int64 cursor_updated_at,
1053 const char *cursor_id,
1054 int32 limit,
1055 Conversation_Summary **pp_summaries,
1056 int32 *p_count,
1057 Dowa_Arena *p_arena)
1058 {
1059 if (!p_store || !p_owner || !pp_summaries || !p_count || !p_arena)
1060 return CONVERSATION_STORE_ERROR;
1061 *pp_summaries = NULL;
1062 *p_count = 0;
1063
1064 /* Legacy conversations are never listed */
1065 if (p_owner->kind == CONVERSATION_OWNER_KIND_LEGACY ||
1066 p_owner->id[0] == '\0')
1067 return CONVERSATION_STORE_OK;
1068
1069 if (limit < 1) limit = 1;
1070 if (limit > 50) limit = 50;
1071
1072 const char *kind_str = Conversation_Store_Owner_Kind_String(p_owner->kind);
1073 char limit_str[16];
1074 snprintf(limit_str, sizeof(limit_str), "%d", limit);
1075
1076 Deita_Result_Set *p_result;
1077 pthread_mutex_lock(&p_store->mutex);
1078
1079 if (cursor_updated_at > 0 && cursor_id && cursor_id[0] != '\0')
1080 {
1081 char ts_str[32];
1082 snprintf(ts_str, sizeof(ts_str), "%lld", (long long)cursor_updated_at);
1083 const char *params[] = {
1084 kind_str, p_owner->id, ts_str, ts_str, cursor_id, limit_str,
1085 };
1086 p_result = Deita_Query_Execute_Prepared(
1087 p_store->p_connection,
1088 "SELECT id, title, status, created_at, updated_at, "
1089 "(SELECT COUNT(*) FROM conversation_turns "
1090 " WHERE conversation_id = conversations.id) AS turn_count, "
1091 "(SELECT SUBSTR(content, 1, 201) FROM conversation_turns "
1092 " WHERE conversation_id = conversations.id "
1093 " ORDER BY sequence DESC LIMIT 1) AS last_msg "
1094 "FROM conversations "
1095 "WHERE owner_kind = ? AND owner_id = ? AND status != 'deleted' "
1096 "AND (updated_at < ? OR (updated_at = ? AND id < ?)) "
1097 "ORDER BY updated_at DESC, id DESC LIMIT ?",
1098 6, params, p_arena);
1099 }
1100 else
1101 {
1102 const char *params[] = {kind_str, p_owner->id, limit_str};
1103 p_result = Deita_Query_Execute_Prepared(
1104 p_store->p_connection,
1105 "SELECT id, title, status, created_at, updated_at, "
1106 "(SELECT COUNT(*) FROM conversation_turns "
1107 " WHERE conversation_id = conversations.id) AS turn_count, "
1108 "(SELECT SUBSTR(content, 1, 201) FROM conversation_turns "
1109 " WHERE conversation_id = conversations.id "
1110 " ORDER BY sequence DESC LIMIT 1) AS last_msg "
1111 "FROM conversations "
1112 "WHERE owner_kind = ? AND owner_id = ? AND status != 'deleted' "
1113 "ORDER BY updated_at DESC, id DESC LIMIT ?",
1114 3, params, p_arena);
1115 }
1116
1117 if (!p_result)
1118 {
1119 pthread_mutex_unlock(&p_store->mutex);
1120 return CONVERSATION_STORE_ERROR;
1121 }
1122
1123 Conversation_Summary *summaries = NULL;
1124 while (Deita_Result_Set_Next(p_result))
1125 {
1126 Conversation_Summary s = {0};
1127 s.id = Conversation_Store_Copy_Text(Deita_Result_Set_Get_Text(p_result, 0), p_arena);
1128 s.title = Conversation_Store_Copy_Text(Deita_Result_Set_Get_Text(p_result, 1), p_arena);
1129 s.status = Conversation_Store_Copy_Text(Deita_Result_Set_Get_Text(p_result, 2), p_arena);
1130 s.created_at = Deita_Result_Set_Get_Integer(p_result, 3);
1131 s.updated_at = Deita_Result_Set_Get_Integer(p_result, 4);
1132 s.turn_count = Deita_Result_Set_Get_Integer(p_result, 5);
1133 const char *last_msg_raw = Deita_Result_Set_Get_Text(p_result, 6);
1134 if (last_msg_raw && last_msg_raw[0] != '\0')
1135 {
1136 /* Truncate to 200 chars maximum */
1137 size_t msg_len = strlen(last_msg_raw);
1138 if (msg_len > 200) msg_len = 200;
1139 char *preview = Dowa_Arena_Allocate(p_arena, msg_len + 1);
1140 if (preview)
1141 {
1142 memcpy(preview, last_msg_raw, msg_len);
1143 preview[msg_len] = '\0';
1144 }
1145 s.last_message_preview = preview;
1146 }
1147 else
1148 {
1149 s.last_message_preview = Conversation_Store_Copy_Text("", p_arena);
1150 }
1151 Dowa_Array_Push_Arena(summaries, s, p_arena);
1152 }
1153 boolean has_error = Deita_Result_Set_Has_Error(p_result);
1154 Deita_Result_Set_Free(p_result);
1155 pthread_mutex_unlock(&p_store->mutex);
1156
1157 if (has_error)
1158 return CONVERSATION_STORE_ERROR;
1159
1160 *pp_summaries = summaries;
1161 *p_count = (int32)Dowa_Array_Length(summaries);
1162 return CONVERSATION_STORE_OK;
1163 }
1164
1165 Conversation_Store_Result Conversation_Store_Claim_Legacy(
1166 Conversation_Store *p_store,
1167 const char *conversation_id,
1168 const char *user_id)
1169 {
1170 if (!p_store || !conversation_id || !user_id || user_id[0] == '\0')
1171 return CONVERSATION_STORE_ERROR;
1172
1173 pthread_mutex_lock(&p_store->mutex);
1174 if (Deita_Query_Execute_Update(
1175 p_store->p_connection, "BEGIN IMMEDIATE") < 0)
1176 {
1177 pthread_mutex_unlock(&p_store->mutex);
1178 return CONVERSATION_STORE_ERROR;
1179 }
1180
1181 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
1182 if (!p_arena)
1183 {
1184 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1185 pthread_mutex_unlock(&p_store->mutex);
1186 return CONVERSATION_STORE_ERROR;
1187 }
1188 const char *check_params[] = {conversation_id};
1189 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
1190 p_store->p_connection,
1191 "SELECT owner_kind FROM conversations "
1192 "WHERE id = ? AND status != 'deleted'",
1193 1, check_params, p_arena);
1194 boolean found = p_result && Deita_Result_Set_Next(p_result);
1195 const char *existing_kind = found
1196 ? Deita_Result_Set_Get_Text(p_result, 0)
1197 : NULL;
1198 boolean is_claimable =
1199 existing_kind && strcmp(existing_kind, "legacy") == 0;
1200 if (p_result) Deita_Result_Set_Free(p_result);
1201 Dowa_Arena_Free(p_arena);
1202
1203 if (!found)
1204 {
1205 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_NOT_FOUND);
1206 pthread_mutex_unlock(&p_store->mutex);
1207 return CONVERSATION_STORE_NOT_FOUND;
1208 }
1209 if (!is_claimable)
1210 {
1211 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_CONFLICT);
1212 pthread_mutex_unlock(&p_store->mutex);
1213 return CONVERSATION_STORE_CONFLICT;
1214 }
1215
1216 const char *update_params[] = {user_id, conversation_id};
1217 int32 updated = Deita_Query_Execute_Update_Prepared(
1218 p_store->p_connection,
1219 "UPDATE conversations SET owner_kind = 'user', owner_id = ?, "
1220 "updated_at = strftime('%s','now') "
1221 "WHERE id = ? AND owner_kind = 'legacy'",
1222 2, update_params);
1223 if (updated < 0 ||
1224 Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
1225 {
1226 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1227 pthread_mutex_unlock(&p_store->mutex);
1228 return CONVERSATION_STORE_ERROR;
1229 }
1230 pthread_mutex_unlock(&p_store->mutex);
1231 return updated > 0 ? CONVERSATION_STORE_OK : CONVERSATION_STORE_NOT_FOUND;
1232 }
1233
1234 Conversation_Store_Result Conversation_Store_Transfer_Guest_To_User(
1235 Conversation_Store *p_store,
1236 const char *guest_id,
1237 const char *user_id)
1238 {
1239 if (!p_store || !guest_id || !user_id ||
1240 guest_id[0] == '\0' || user_id[0] == '\0')
1241 return CONVERSATION_STORE_ERROR;
1242
1243 pthread_mutex_lock(&p_store->mutex);
1244 if (Deita_Query_Execute_Update(
1245 p_store->p_connection, "BEGIN IMMEDIATE") < 0)
1246 {
1247 pthread_mutex_unlock(&p_store->mutex);
1248 return CONVERSATION_STORE_ERROR;
1249 }
1250
1251 /* Check for an existing mapping for this guest_id */
1252 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
1253 if (!p_arena)
1254 {
1255 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1256 pthread_mutex_unlock(&p_store->mutex);
1257 return CONVERSATION_STORE_ERROR;
1258 }
1259 const char *check_params[] = {guest_id};
1260 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
1261 p_store->p_connection,
1262 "SELECT user_id FROM conversation_guest_transfers WHERE guest_id = ?",
1263 1, check_params, p_arena);
1264 boolean mapping_exists = p_result && Deita_Result_Set_Next(p_result);
1265 const char *existing_user = mapping_exists
1266 ? Deita_Result_Set_Get_Text(p_result, 0) : NULL;
1267 boolean same_user = mapping_exists && existing_user &&
1268 strcmp(existing_user, user_id) == 0;
1269 boolean conflict = mapping_exists && !same_user;
1270 if (p_result)
1271 Deita_Result_Set_Free(p_result);
1272 Dowa_Arena_Free(p_arena);
1273
1274 if (conflict)
1275 {
1276 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_CONFLICT);
1277 pthread_mutex_unlock(&p_store->mutex);
1278 return CONVERSATION_STORE_CONFLICT;
1279 }
1280
1281 if (!mapping_exists)
1282 {
1283 /* Record the mapping so future Create_Owned for this guest uses user */
1284 const char *map_params[] = {guest_id, user_id};
1285 if (Deita_Query_Execute_Update_Prepared(
1286 p_store->p_connection,
1287 "INSERT OR IGNORE INTO conversation_guest_transfers "
1288 "(guest_id, user_id) VALUES (?, ?)",
1289 2, map_params) < 0)
1290 {
1291 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1292 pthread_mutex_unlock(&p_store->mutex);
1293 return CONVERSATION_STORE_ERROR;
1294 }
1295
1296 /* Transfer existing conversations from guest to user */
1297 const char *transfer_params[] = {user_id, guest_id};
1298 if (Deita_Query_Execute_Update_Prepared(
1299 p_store->p_connection,
1300 "UPDATE conversations SET owner_kind = 'user', owner_id = ?, "
1301 "updated_at = strftime('%s','now') "
1302 "WHERE owner_kind = 'guest' AND owner_id = ?",
1303 2, transfer_params) < 0)
1304 {
1305 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1306 pthread_mutex_unlock(&p_store->mutex);
1307 return CONVERSATION_STORE_ERROR;
1308 }
1309 }
1310 /* same_user mapping already exists: idempotent no-op */
1311
1312 if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
1313 {
1314 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1315 pthread_mutex_unlock(&p_store->mutex);
1316 return CONVERSATION_STORE_ERROR;
1317 }
1318 pthread_mutex_unlock(&p_store->mutex);
1319 return CONVERSATION_STORE_OK;
1320 }
1321
1322 Conversation_Store_Result Conversation_Store_Transfer_Guest_To_User_Atomic(
1323 Conversation_Store *p_store,
1324 const char *guest_id,
1325 const char *user_id)
1326 {
1327 if (!p_store || !guest_id || !user_id ||
1328 guest_id[0] == '\0' || user_id[0] == '\0')
1329 return CONVERSATION_STORE_ERROR;
1330
1331 pthread_mutex_lock(&p_store->mutex);
1332 if (Deita_Query_Execute_Update(
1333 p_store->p_connection, "BEGIN IMMEDIATE") < 0)
1334 {
1335 pthread_mutex_unlock(&p_store->mutex);
1336 return CONVERSATION_STORE_ERROR;
1337 }
1338
1339 /* Check for an existing mapping for this guest_id. */
1340 Dowa_Arena *p_arena = Dowa_Arena_Create(512);
1341 if (!p_arena)
1342 {
1343 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1344 pthread_mutex_unlock(&p_store->mutex);
1345 return CONVERSATION_STORE_ERROR;
1346 }
1347 const char *check_params[] = {guest_id};
1348 Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
1349 p_store->p_connection,
1350 "SELECT user_id FROM conversation_guest_transfers WHERE guest_id = ?",
1351 1, check_params, p_arena);
1352 boolean mapping_exists = p_result && Deita_Result_Set_Next(p_result);
1353 const char *existing_user = mapping_exists
1354 ? Deita_Result_Set_Get_Text(p_result, 0) : NULL;
1355 boolean same_user = mapping_exists && existing_user &&
1356 strcmp(existing_user, user_id) == 0;
1357 boolean conflict = mapping_exists && !same_user;
1358 if (p_result)
1359 Deita_Result_Set_Free(p_result);
1360 Dowa_Arena_Free(p_arena);
1361
1362 if (conflict)
1363 {
1364 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_CONFLICT);
1365 pthread_mutex_unlock(&p_store->mutex);
1366 return CONVERSATION_STORE_CONFLICT;
1367 }
1368
1369 if (!same_user)
1370 {
1371 const char *map_params[] = {guest_id, user_id};
1372 if (Deita_Query_Execute_Update_Prepared(
1373 p_store->p_connection,
1374 "INSERT OR IGNORE INTO conversation_guest_transfers "
1375 "(guest_id, user_id) VALUES (?, ?)",
1376 2, map_params) < 0)
1377 {
1378 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1379 pthread_mutex_unlock(&p_store->mutex);
1380 return CONVERSATION_STORE_ERROR;
1381 }
1382
1383 const char *transfer_params[] = {user_id, guest_id};
1384 if (Deita_Query_Execute_Update_Prepared(
1385 p_store->p_connection,
1386 "UPDATE conversations SET owner_kind = 'user', owner_id = ?, "
1387 "updated_at = strftime('%s','now') "
1388 "WHERE owner_kind = 'guest' AND owner_id = ?",
1389 2, transfer_params) < 0)
1390 {
1391 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1392 pthread_mutex_unlock(&p_store->mutex);
1393 return CONVERSATION_STORE_ERROR;
1394 }
1395 }
1396
1397 /*
1398 * Quota cleanup — auth tables reside in the same SQLite file so the write
1399 * lock already held by this transaction covers them too. Decrement
1400 * output_tokens_reserved and delete reservation rows for this guest.
1401 */
1402 const char *quota_upd_params[] = {guest_id, guest_id};
1403 if (Deita_Query_Execute_Update_Prepared(
1404 p_store->p_connection,
1405 "UPDATE guest_usage"
1406 " SET output_tokens_reserved = MAX(0, output_tokens_reserved - ("
1407 " SELECT COALESCE(SUM(r.output_tokens_reserved), 0)"
1408 " FROM guest_usage_reservations r"
1409 " WHERE r.guest_id = ? AND r.window_start = guest_usage.window_start"
1410 " ))"
1411 " WHERE guest_id = ?",
1412 2, quota_upd_params) < 0)
1413 {
1414 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1415 pthread_mutex_unlock(&p_store->mutex);
1416 return CONVERSATION_STORE_ERROR;
1417 }
1418
1419 const char *quota_del_params[] = {guest_id};
1420 if (Deita_Query_Execute_Update_Prepared(
1421 p_store->p_connection,
1422 "DELETE FROM guest_usage_reservations WHERE guest_id = ?",
1423 1, quota_del_params) < 0)
1424 {
1425 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1426 pthread_mutex_unlock(&p_store->mutex);
1427 return CONVERSATION_STORE_ERROR;
1428 }
1429
1430 if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
1431 {
1432 Conversation_Store_Rollback(p_store, CONVERSATION_STORE_ERROR);
1433 pthread_mutex_unlock(&p_store->mutex);
1434 return CONVERSATION_STORE_ERROR;
1435 }
1436 pthread_mutex_unlock(&p_store->mutex);
1437 return CONVERSATION_STORE_OK;
1438 }