Voice-to-text dictation app with local (Nvidia Parakeet/Whisper) and cloud models (BYOK). Privacy-first and available cross-platform.
JavaScript aianthropiccross-platformgeminigroqlinuxmacosnvidiaopen-sourceopenaiparakeetspeech-to-texttranscribewhisperwindows
import { isManagedMeetingAudioUri } from '@/lib/transcriptAudio';
import {
eq,
ne,
like,
and,
or,
asc,
desc,
sql,
count,
isNotNull,
isNull,
inArray,
type SQL,
type SQLWrapper,
} from 'drizzle-orm';
import * as FileSystem from 'expo-file-system/legacy';
import { parseRemoteTranscript, serializeSegmentsForSync } from '@/lib/notes/remoteTranscript';
import { randomUUID } from '@/lib/uuid';
import { isFolderAwaitingUpload } from '@/lib/notes/folderUpload';
import { LATIN_WORD_TERM, NUMBER_TERM, WHOLE_WORD_TERM } from '@/lib/chat/chatTerms';
import {
folderDeleteJournal,
folders,
notes,
actions,
syncState,
dictionaryEntries,
snippets,
transcriptSegments,
speakers,
speakerProfiles,
spaces,
} from '@/db/schema';
import { isTranscriptionStatus } from '@/lib/diarization/diarizer';
import {
decodeEmbedding,
encodeEmbedding,
validateEmbedding,
} from '@/lib/diarization/embeddingCodec';
import type { TranscriptionStatus } from '@/lib/diarization/diarizer';
import type {
NotesRepository,
Note,
Folder,
Action,
NoteUpdate,
ActionUpdate,
MeetingNoteUpdate,
MeetingCalendarContextUpdate,
ApplyRemoteOptions,
ApplyRemoteNoteOptions,
RemoteFolder,
RemoteNote,
ConflictedNote,
ChatCandidateNotes,
ChatCandidateOptions,
ChatNoteWindow,
Segment,
NewSegment,
Speaker,
NewSpeaker,
SpeakerProfile,
SpeakerProfileRow,
NewSpeakerProfile,
SpeakerProfileOwnerFlag,
} from '../types';
type NotesDb = typeof import('@/db').db;
type SpeakerProfileDbEmbedding = typeof speakerProfiles.$inferInsert.embedding;
export const SPEAKER_PROFILE_OWNER_ALREADY_EXISTS_CODE =
'SPEAKER_PROFILE_OWNER_ALREADY_EXISTS' as const;
export class SpeakerProfileOwnerAlreadyExistsError extends Error {
readonly code = SPEAKER_PROFILE_OWNER_ALREADY_EXISTS_CODE;
readonly cause?: unknown;
constructor(cause?: unknown) {
super('An owner speaker profile already exists');
this.name = 'SpeakerProfileOwnerAlreadyExistsError';
this.cause = cause;
}
}
export function isSpeakerProfileOwnerConstraintError(error: unknown): boolean {
if (!error || typeof error !== 'object') return false;
const candidate = error as { code?: unknown; message?: unknown };
const message = typeof candidate.message === 'string' ? candidate.message : '';
const code = typeof candidate.code === 'string' ? candidate.code : '';
const isConstraint =
code.startsWith('SQLITE_CONSTRAINT') ||
message.includes('UNIQUE constraint failed') ||
message.includes('constraint failed');
return isConstraint && message.includes('speaker_profiles') && message.includes('is_owner');
}
export function mapSpeakerProfileOwnerConstraint(error: unknown): never {
if (isSpeakerProfileOwnerConstraintError(error)) {
throw new SpeakerProfileOwnerAlreadyExistsError(error);
}
throw error;
}
const parseSpeakerProfileOwnerFlag = (value: number): SpeakerProfileOwnerFlag => {
if (value === 0 || value === 1) return value;
throw new Error('Speaker profile isOwner must be 0 or 1');
};
const validateSpeakerProfileOwnerFlag = (value: number | undefined): void => {
if (value === undefined) return;
parseSpeakerProfileOwnerFlag(value);
};
const encodeSpeakerProfileEmbedding = (values: number[]): SpeakerProfileDbEmbedding =>
encodeEmbedding(values) as unknown as SpeakerProfileDbEmbedding;
const getDefaultDb = (): NotesDb => {
const { db } = require('@/db') as typeof import('@/db');
return db;
};
// The columns pushNotes.ts serializes, plus the ones whose change re-queues the
// row (scope, privacy, a soft delete): a push acknowledgement settles the row
// only while every one of them still reads as it did when the payload was built.
const NOTE_PUSH_ACK_FIELDS: ReadonlyArray<keyof Note> = [
'title',
'content',
'enhancedContent',
'enhancementPrompt',
'noteType',
'sourceFile',
'audioDurationSeconds',
'folderId',
'spaceId',
'isPrivate',
'participants',
'calendarEventId',
'deletedAt',
];
const pushRejectedKey = (localId: number): string => `note.pushRejected.${localId}`;
// What a word starts after: whitespace, punctuation, and the brackets, quotes (an elided "l’équipe"
// too), slashes, dashes, tags and Markdown marks that open one. A LIKE per character skims the
// text, where a GLOB class in front of the term would be tried at every position, several times
// slower on notes holding the term mid-word ("report" for "port").
const WORD_STARTS_AFTER = [
' ',
'\u00a0',
'\n',
'\t',
'(',
'[',
'"',
"'",
'“',
'‘',
'’',
'„',
'‚',
'«',
'¿',
'¡',
'`',
'/',
'-',
'–',
'—',
'*',
'_',
'#',
'@',
',',
'.',
':',
';',
];
// Transcribed speech carries no Markdown, tags or brackets, and a meeting's many segments are each
// searched, so they look for fewer.
const SPOKEN_WORD_STARTS_AFTER = [' ', '-', '"', "'", '’', '(', ',', '.', '—'];
// Chinese, Japanese and Korean put no space before a Latin word ("会議でbudget"), so a term right
// after one of their characters starts a word too: CJK punctuation, kana and ideographs, Hangul,
// and fullwidth forms, as code point ranges.
const CJK_CODE_POINTS: readonly (readonly [number, number])[] = [
[0x3000, 0x9fff],
[0xac00, 0xd7af],
[0xff00, 0xffef],
];
// Only the term's first occurrence is checked: a GLOB class in front of it would be tried at every
// position of every note holding it mid-word. Positions line up because lower() folds ASCII only.
const followsCjk = (column: SQLWrapper, term: string): SQL => {
const at = sql`instr(lower(${column}), ${term})`;
const before = sql`unicode(substr(${column}, ${at} - 1, 1))`;
return sql`(${at} > 1 AND (${sql.join(
CJK_CODE_POINTS.map(([low, high]) => sql`${before} BETWEEN ${low} AND ${high}`),
sql` OR `,
)}))`;
};
// A number matches whole numbers only: "10" never matches "100", "2010", "10:30", "1.10" or
// "10,000", and "1.10" never matches "1.100". A `.`, `:` or `,` between digits joins them, but one
// after a number ends it ("10.").
const numberPatterns = (term: string): string[] => {
const before = ['*[^A-Za-z0-9.:,]', '*[^0-9][.:,]'];
const after = ['[^A-Za-z0-9.:,]*', '[.:,][^0-9]*'];
return before.flatMap((start) => after.map((end) => `${start}${term}${end}`));
};
// GLOB patterns for a term that must match whole, or null for one matched anywhere. Terms are
// letters and digits, joined in a number by `.`, `,` or `:`, none of which GLOB treats specially.
const wholeTermPatterns = (term: string): string[] | null => {
if (NUMBER_TERM.test(term)) return numberPatterns(term);
if (!WHOLE_WORD_TERM.test(term)) return null;
// GLOB folds no case at all.
const letters = Array.from(term, (character) =>
/[a-z]/.test(character) ? `[${character.toUpperCase()}${character}]` : character,
).join('');
return [`*[^A-Za-z0-9]${letters}[^A-Za-z0-9]*`];
};
// Escape LIKE wildcards so a term such as "50%" matches literally.
const escapeLike = (text: string): string => text.replace(/[\\%_]/g, '\\$&');
const likeAny = (column: SQLWrapper, patterns: string[]): SQL =>
sql`(${sql.join(
patterns.map((pattern) => sql`${column} LIKE ${pattern} ESCAPE '\\'`),
sql` OR `,
)})`;
// Search terms arrive lowercased. SQLite's LIKE folds ASCII case only, so a term with other letters
// also looks for its capitalised form ("Übergabe" heading a title).
const chatTermMatcher = (
term: string,
wordStartsAfter: readonly string[] = WORD_STARTS_AFTER,
): ((column: SQLWrapper) => SQL) => {
const globs = wholeTermPatterns(term);
if (globs) {
// The GLOBs run over a padded copy of the column, so a term at its start or end meets a
// boundary too. That copy and the GLOBs cost several times a LIKE, so a LIKE rules out the
// rows that don't hold the term at all first.
return (column) =>
sql`(${likeAny(column, [`%${escapeLike(term)}%`])} AND (${sql.join(
globs.map((pattern) => sql`(' ' || ${column} || ' ') GLOB ${pattern}`),
sql` OR `,
)}))`;
}
const capitalised = `${term.charAt(0).toUpperCase()}${term.slice(1)}`;
const forms = (
/\P{ASCII}/u.test(term) && capitalised !== term ? [term, capitalised] : [term]
).map(escapeLike);
const anywhere = forms.map((form) => `%${form}%`);
if (!LATIN_WORD_TERM.test(term)) return (column) => likeAny(column, anywhere);
// The single LIKE rules out most notes before the word starts are looked for.
const wordStart = forms.flatMap((form) => [
`${form}%`,
...wordStartsAfter.map((before) => `%${escapeLike(before)}${form}%`),
]);
return (column) =>
sql`(${likeAny(column, anywhere)} AND (${likeAny(column, wordStart)} OR ${followsCjk(column, term)}))`;
};
interface ScoredChatNote {
id: number;
recency: string;
/** A digit per term: 2 when it matches the title, 1 elsewhere in the note, 0 nowhere. */
matches: string;
}
// A term counts for less the more of the notes searched hold it, so a word in most of them
// ("answers", "time") weighs little next to the one naming the topic. A note is kept only when it
// scores at least half as well as the best one: one sharing only a common word with the question
// is dropped rather than sent, while notes on equally rare topics ("pricing", "hiring") all stay.
// The weight never reaches zero, so a library where every note holds the term still finds them.
const rankByRarity = (
rows: ScoredChatNote[],
searched: number,
limit: number,
): { id: number; total: number }[] => {
const flags = rows.map((row) => Array.from(row.matches, Number));
const weights = flags[0].map((_, term) => {
const holding = flags.filter((noteFlags) => noteFlags[term] > 0).length;
return holding === 0 ? 0 : Math.log(1 + searched / holding);
});
const scores = flags.map((noteFlags) =>
noteFlags.reduce((score, flag, term) => score + flag * weights[term], 0),
);
const best = Math.max(...scores);
const kept = rows
.map((row, index) => ({ id: row.id, recency: row.recency, score: scores[index] }))
.filter(({ score }) => score >= best / 2)
.sort((a, b) => {
if (b.score !== a.score) return b.score - a.score;
if (a.recency === b.recency) return 0;
return a.recency < b.recency ? 1 : -1;
});
return kept.slice(0, limit).map(({ id }) => ({ id, total: kept.length }));
};
// Notes carry SQLite's "YYYY-MM-DD HH:MM:SS" (UTC) or the server's ISO timestamps, so both sides go
// through datetime() to compare instants rather than strings.
const createdIn = ({ start, end, meetingsOnly }: ChatNoteWindow): SQL | undefined =>
and(
sql`datetime(${notes.createdAt}) >= datetime(${start.toISOString()})`,
sql`datetime(${notes.createdAt}) < datetime(${end.toISOString()})`,
// An uploaded recording is a meeting too, as the note editor treats it.
meetingsOnly ? inArray(notes.noteType, ['meeting', 'upload']) : undefined,
);
export class LocalNotesRepository implements NotesRepository {
private readonly database: NotesDb;
constructor(database?: NotesDb) {
this.database = database ?? getDefaultDb();
}
private mapSpeakerProfile(row: SpeakerProfileRow): SpeakerProfile {
return {
...row,
isOwner: parseSpeakerProfileOwnerFlag(row.isOwner),
embedding: decodeEmbedding(row.embedding),
};
}
private getExpectedEmbeddingDimension(profileId?: number): number | undefined {
// Speaker profiles currently share one FluidAudio embedding space. If a future
// diarizer model changes dimensions, add a model/version key before relaxing
// this cross-profile guard so legacy and new profiles are never matched together.
const rows = this.database
.select({ id: speakerProfiles.id, embedding: speakerProfiles.embedding })
.from(speakerProfiles)
.all();
const reference =
rows.find((row) => row.id !== profileId) ?? rows.find((row) => row.id === profileId);
return reference ? decodeEmbedding(reference.embedding).length : undefined;
}
private validateSpeakerProfileEmbedding(values: number[], profileId?: number): void {
validateEmbedding(values, this.getExpectedEmbeddingDimension(profileId));
}
// New locally-created notes/folders default into the device's private space.
// The migration guarantees exactly one 'private' space exists.
private getPrivateSpaceId(): number {
const row = this.database
.select({ id: spaces.id })
.from(spaces)
.where(eq(spaces.kind, 'private'))
.get();
if (!row) {
throw new Error('Private space not found — the migration should have seeded it');
}
return row.id;
}
getFolders(): Folder[] {
return this.database
.select()
.from(folders)
.where(isNull(folders.deletedAt))
.orderBy(asc(folders.sortOrder), asc(folders.name))
.all();
}
getFoldersIncludingDeleted(): Folder[] {
return this.database
.select()
.from(folders)
.orderBy(asc(folders.sortOrder), asc(folders.name))
.all();
}
getPrivateFolders(): Folder[] {
return this.getFoldersBySpace(this.getPrivateSpaceId());
}
getFoldersBySpace(spaceId: number): Folder[] {
return this.database
.select()
.from(folders)
.where(and(eq(folders.spaceId, spaceId), isNull(folders.deletedAt)))
.orderBy(asc(folders.sortOrder), asc(folders.name))
.all();
}
getFolderCounts(): Record<number, number> {
const rows = this.database
.select({ folderId: notes.folderId, count: count() })
.from(notes)
.where(and(isNotNull(notes.folderId), isNull(notes.deletedAt)))
.groupBy(notes.folderId)
.all();
return Object.fromEntries(rows.map((r) => [r.folderId!, r.count]));
}
createFolder(name: string, spaceId?: number): Folder {
return this.database
.insert(folders)
.values({
name,
spaceId: spaceId ?? this.getPrivateSpaceId(),
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.returning()
.get();
}
renameFolder(id: number, name: string): void {
this.database
.update(folders)
.set({ name, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(folders.id, id))
.run();
}
deleteFolder(id: number): void {
this.database
.update(folders)
.set({
deletedAt: sql`datetime('now')`,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(eq(folders.id, id))
.run();
}
getNotesByFolder(folderId: number): Note[] {
return this.database
.select()
.from(notes)
.where(and(eq(notes.folderId, folderId), isNull(notes.deletedAt)))
.orderBy(desc(notes.updatedAt))
.all();
}
getNotesBySpace(spaceId: number): Note[] {
return this.database
.select()
.from(notes)
.where(and(eq(notes.spaceId, spaceId), isNull(notes.deletedAt)))
.orderBy(desc(notes.updatedAt))
.all();
}
getSpaceNotesWithoutFolder(spaceId: number): Note[] {
return this.database
.select()
.from(notes)
.where(and(eq(notes.spaceId, spaceId), isNull(notes.folderId), isNull(notes.deletedAt)))
.orderBy(desc(notes.updatedAt))
.all();
}
getAllNotes(): Note[] {
return this.database
.select()
.from(notes)
.where(isNull(notes.deletedAt))
.orderBy(desc(notes.updatedAt))
.all();
}
getNoteById(id: number): Note | null {
return this.database.select().from(notes).where(eq(notes.id, id)).get() ?? null;
}
createNote(title: string, content: string, folderId?: number, spaceId?: number): Note {
// Created straight into a space (browsing it with no folder open): the note
// sits in the space itself rather than in any folder.
const targetFolderId = spaceId != null ? null : (folderId ?? 1);
return this.database
.insert(notes)
.values({
title,
content,
folderId: targetFolderId,
// A note always belongs to whatever space holds its folder — filing a
// team folder's note into the private space would strand it.
spaceId:
targetFolderId != null
? (this.getFolderSpaceId(targetFolderId) ?? this.getPrivateSpaceId())
: (spaceId ?? this.getPrivateSpaceId()),
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.returning()
.get();
}
/**
* Deletes a folder the way the server will. `deleteFolder` on the API tombstones every note
* still inside the folder, so the client applies that same cascade instead of re-homing the
* notes into a folder the server knows nothing about.
*
* The folder and each cascaded note are journaled first with their prior state, then held:
* excluded from the note push queue (getPendingNotes) and skipped by every pull
* (isRemoteNoteHeldByFolderDelete), so nothing races the server's verdict. pushFolders then
* calls finalizeFolderDelete or revertFolderDelete once it knows.
*/
deleteFolderCascade(folderId: number): void {
const folder = this.database.select().from(folders).where(eq(folders.id, folderId)).get();
// Already tombstoned means a cascade is in flight (or finished): journaling
// it twice would collide on idx_folder_delete_journal_entity.
if (!folder || folder.deletedAt != null) return;
const children = this.database
.select()
.from(notes)
.where(and(eq(notes.folderId, folderId), isNull(notes.deletedAt)))
.all();
this.database
.insert(folderDeleteJournal)
.values([
{
folderId,
entityType: 'folder' as const,
entityId: folder.id,
originalDeletedAt: folder.deletedAt,
originalPendingSync: folder.pendingSync,
originalUpdatedAt: folder.updatedAt,
},
...children.map((note) => ({
folderId,
entityType: 'note' as const,
entityId: note.id,
originalDeletedAt: note.deletedAt,
originalPendingSync: note.pendingSync,
originalUpdatedAt: note.updatedAt,
})),
])
.run();
if (children.length > 0) {
// pendingSync is cleared as well as the tombstone written: an individual
// push for one of these would 404 against the row the server's cascade
// already tombstoned, and burn its remoteId doing so.
this.database
.update(notes)
.set({
deletedAt: sql`datetime('now')`,
pendingSync: 0,
updatedAt: sql`datetime('now')`,
})
.where(
inArray(
notes.id,
children.map((note) => note.id),
),
)
.run();
}
this.database
.update(folders)
.set({
deletedAt: sql`datetime('now')`,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(eq(folders.id, folderId))
.run();
}
/** True while a pending folder delete owns this note — see deleteFolderCascade. */
isRemoteNoteHeldByFolderDelete(remote: Pick<RemoteNote, 'id' | 'client_note_id'>): boolean {
const local = this.getNoteForRemote(remote);
return local != null && this.isNoteHeldByFolderDelete(local.id);
}
/** The server accepted the delete: drop the folder and its cascaded notes for good. */
finalizeFolderDelete(folderId: number): void {
for (const noteId of this.journaledNoteIds(folderId)) this.hardDeleteNote(noteId);
this.clearFolderDeleteJournal(folderId);
this.hardDeleteFolder(folderId);
}
/** The server refused the delete: put every row this cascade touched back as it was. */
revertFolderDelete(folderId: number): void {
const rows = this.database
.select()
.from(folderDeleteJournal)
.where(eq(folderDeleteJournal.folderId, folderId))
.all();
for (const row of rows) {
const restored = {
deletedAt: row.originalDeletedAt,
pendingSync: row.originalPendingSync,
updatedAt: row.originalUpdatedAt,
};
if (row.entityType === 'folder') {
this.database.update(folders).set(restored).where(eq(folders.id, row.entityId)).run();
} else {
this.database.update(notes).set(restored).where(eq(notes.id, row.entityId)).run();
}
}
this.clearFolderDeleteJournal(folderId);
}
private isNoteHeldByFolderDelete(noteId: number): boolean {
return (
this.database
.select({ id: folderDeleteJournal.id })
.from(folderDeleteJournal)
.where(
and(eq(folderDeleteJournal.entityType, 'note'), eq(folderDeleteJournal.entityId, noteId)),
)
.get() != null
);
}
private journaledNoteIds(folderId: number): number[] {
return this.database
.select({ entityId: folderDeleteJournal.entityId })
.from(folderDeleteJournal)
.where(
and(eq(folderDeleteJournal.folderId, folderId), eq(folderDeleteJournal.entityType, 'note')),
)
.all()
.map((row) => row.entityId);
}
private clearFolderDeleteJournal(folderId: number): void {
this.database
.delete(folderDeleteJournal)
.where(eq(folderDeleteJournal.folderId, folderId))
.run();
}
private getFolderSpaceId(folderId: number): number | null {
return (
this.database
.select({ spaceId: folders.spaceId })
.from(folders)
.where(eq(folders.id, folderId))
.get()?.spaceId ?? null
);
}
updateNote(id: number, updates: NoteUpdate): void {
this.database
.update(notes)
.set({ ...updates, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(notes.id, id))
.run();
}
// FK cascade is inert (no PRAGMA foreign_keys=ON), so child diarization rows and the persisted
// meeting WAV must be removed explicitly on EVERY delete path (soft delete, hard delete,
// remote-delete apply, and full wipe) — otherwise they orphan in the DB and on disk.
private deleteNoteChildrenAndAudio(id: number): void {
const row = this.database
.select({ sourceFile: notes.sourceFile })
.from(notes)
.where(eq(notes.id, id))
.get();
if (row?.sourceFile && isManagedMeetingAudioUri(id, row.sourceFile)) {
FileSystem.deleteAsync(row.sourceFile, { idempotent: true }).catch(() => {});
}
this.database.delete(transcriptSegments).where(eq(transcriptSegments.noteId, id)).run();
this.database.delete(speakers).where(eq(speakers.noteId, id)).run();
}
deleteNote(id: number): void {
this.deleteNoteChildrenAndAudio(id);
// Soft-delete the note row. Explicit user intent (delete) supersedes any
// parked conflict — clear conflictServerNote too, otherwise getPendingNotes
// would keep excluding this row and the delete would never push, silently
// wedging the note in a soft-deleted-but-never-synced local limbo forever.
this.database
.update(notes)
.set({
deletedAt: sql`datetime('now')`,
pendingSync: 1,
conflictServerNote: null,
updatedAt: sql`datetime('now')`,
})
.where(eq(notes.id, id))
.run();
}
moveNoteToFolder(noteId: number, folderId: number): void {
this.database
.update(notes)
.set({ folderId, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(notes.id, noteId))
.run();
}
moveNotesToFolder(fromFolderId: number, toFolderId: number): void {
this.database
.update(notes)
.set({ folderId: toFolderId, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(notes.folderId, fromFolderId))
.run();
}
moveNoteToSpace(noteId: number, spaceId: number): void {
// A folder from the old space is never valid in the new one (including moving back to
// Personal) — see MoveToFolderSheet, which hides the folder picker in this flow entirely.
//
// Deliberately does NOT clear conflict_server_note, unlike deleteNote/setNotePrivacy. Those
// clear it safely because neither one needs a content PATCH against the row's existing
// server identity: delete carries no body to conflict on, and setNotePrivacy severs/reissues
// the identity entirely (a fresh create under a new client_note_id, not a PATCH against the
// old base). A space move is still a PATCH against the SAME identity/base_updated_at — if the
// park were cleared here, the next push would carry the move using the stale pre-conflict
// base, draw another 409 from the server, and get re-parked right back: a futile
// clear-then-re-park cycle. So the row stays excluded from getPendingNotes (the move is
// queued via pendingSync but genuinely un-pushable) until the user resolves the conflict —
// resolveConflictKeepMine rebases onto the server's stored updated_at and the queued move
// rides along on the next push; resolveConflictUseServer discards the local row's pending
// state (including this move) in favor of the server's copy. No separate signal is needed
// here: the conflict banner is already visible on this exact note in the editor.
this.database
.update(notes)
.set({ spaceId, folderId: null, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(notes.id, noteId))
.run();
}
searchNotes(query: string, folderId?: number): Note[] {
const pattern = `%${query}%`;
const textMatch = or(like(notes.title, pattern), like(notes.content, pattern));
const notDeleted = isNull(notes.deletedAt);
const condition = folderId
? and(textMatch, eq(notes.folderId, folderId), notDeleted)
: and(textMatch, notDeleted);
return this.database.select().from(notes).where(condition).orderBy(desc(notes.updatedAt)).all();
}
getChatCandidateNotes(terms: string[], options: ChatCandidateOptions): ChatCandidateNotes {
const { window } = options;
const visible = and(
isNull(notes.deletedAt),
eq(notes.leftTeam, 0),
options.includePrivate ? undefined : eq(notes.isPrivate, 0),
window ? createdIn(window) : undefined,
);
// A period's notes are listed by when they were made, since editing an old meeting doesn't
// make it one of this week's.
const recency = window ? sql`datetime(${notes.createdAt})` : sql`${notes.updatedAt}`;
// Only the ids are ranked and counted, so the heavy columns (content, transcript JSON) are read
// for the notes returned alone. The count runs before the limit, so it counts every match, or
// with no terms every note in the period.
const total = sql<number>`count(*) OVER ()`;
const ranked =
terms.length === 0
? this.database
.select({ id: notes.id, total })
.from(notes)
.where(visible)
.orderBy(desc(recency))
.limit(options.limit)
.all()
: this.rankChatMatches(terms, { visible, recency, limit: options.limit });
if (ranked.length === 0) return { notes: [], total: 0 };
const ids = ranked.map(({ id }) => id);
const rows = new Map(
this.database
.select()
.from(notes)
.where(inArray(notes.id, ids))
.all()
.map((note) => [note.id, note]),
);
const found = { notes: ranked.flatMap(({ id }) => rows.get(id) ?? []), total: ranked[0].total };
if (terms.length === 0) return found;
return { ...found, transcriptMatchIds: this.findTranscriptMatches(ids, terms) };
}
// Scoring stops at a note's first matching field, so whether a note that matched in its title or
// text also matched in its transcript is asked again, for the few notes returned alone.
private findTranscriptMatches(noteIds: number[], terms: string[]): number[] {
return this.database
.selectDistinct({ noteId: transcriptSegments.noteId })
.from(transcriptSegments)
.where(
and(
inArray(transcriptSegments.noteId, noteIds),
isNull(transcriptSegments.deletedAt),
or(
...terms.map((term) =>
chatTermMatcher(term, SPOKEN_WORD_STARTS_AFTER)(transcriptSegments.text),
),
),
),
)
.all()
.map(({ noteId }) => noteId);
}
private rankChatMatches(
terms: string[],
query: { visible: SQL | undefined; recency: SQL; limit: number },
): { id: number; total: number }[] {
const termMatches = terms.map((term) => {
const matches = chatTermMatcher(term);
const spoken = chatTermMatcher(term, SPOKEN_WORD_STARTS_AFTER);
// notes.transcript is JSON, whose keys ("source", "text", "speaker") would match every
// meeting, so a transcript is searched through its segments' text.
const inTranscript = sql`EXISTS (
SELECT 1 FROM ${transcriptSegments}
WHERE ${transcriptSegments.noteId} = ${notes.id}
AND ${transcriptSegments.deletedAt} IS NULL
AND ${spoken(transcriptSegments.text)}
)`;
return sql`(CASE WHEN ${matches(notes.title)} THEN 2
WHEN ${matches(notes.content)} OR ${matches(notes.enhancedContent)} OR ${inTranscript} THEN 1
ELSE 0 END)`;
});
// Each note is scored once, as a digit per term. A LIMIT, even one that keeps every row, stops
// SQLite inlining the subquery or pushing the filter into it, either of which would run every
// LIKE and EXISTS again.
const scored = this.database
.select({
id: notes.id,
recency: sql<string>`${query.recency}`.as('recency'),
matches: sql<string>`CAST(${sql.join(termMatches, sql` || `)} AS TEXT)`.as('matches'),
})
.from(notes)
.where(query.visible)
.limit(Number.MAX_SAFE_INTEGER)
.as('scored');
const rows = this.database
.select()
.from(scored)
.where(sql`${scored.matches} GLOB '*[12]*'`)
.all();
if (rows.length === 0) return [];
// Counted apart: a window over the scored rows makes SQLite score every note again.
const searched =
this.database.select({ searched: count() }).from(notes).where(query.visible).get()
?.searched ?? rows.length;
return rankByRarity(rows, searched, query.limit);
}
getActions(): Action[] {
return this.database
.select()
.from(actions)
.orderBy(asc(actions.sortOrder), asc(actions.name))
.all();
}
getActionById(id: number): Action | null {
return this.database.select().from(actions).where(eq(actions.id, id)).get() ?? null;
}
createAction(name: string, description: string, prompt: string): Action {
return this.database.insert(actions).values({ name, description, prompt }).returning().get();
}
updateAction(id: number, updates: ActionUpdate): void {
this.database
.update(actions)
.set({ ...updates, updatedAt: sql`datetime('now')` })
.where(eq(actions.id, id))
.run();
}
deleteAction(id: number): void {
this.database
.delete(actions)
.where(and(eq(actions.id, id), eq(actions.isDefault, 0)))
.run();
}
// Sync helpers
getPendingFolders(): Folder[] {
return this.database.select().from(folders).where(eq(folders.pendingSync, 1)).all();
}
getPrivateNotesPendingDeletion(): Note[] {
return this.database
.select()
.from(notes)
.where(
and(eq(notes.isPrivate, 1), or(isNotNull(notes.remoteId), isNotNull(notes.clientNoteId))),
)
.all();
}
getPendingNotes(): Note[] {
const hasDirtySegment = sql`EXISTS (
SELECT 1 FROM ${transcriptSegments}
WHERE ${transcriptSegments.noteId} = ${notes.id}
AND ${transcriptSegments.pendingSync} = 1
AND ${transcriptSegments.deletedAt} IS NULL
)`;
const hasDirtySpeaker = sql`EXISTS (
SELECT 1 FROM ${speakers}
WHERE ${speakers.noteId} = ${notes.id}
AND ${speakers.pendingSync} = 1
AND ${speakers.deletedAt} IS NULL
)`;
return (
this.database
.select()
.from(notes)
// A row parked with a conflict (see parkNoteConflict) stays out of the
// push queue entirely until resolveConflictKeepMine/UseServer clears it —
// otherwise pushNotes would retry the same stale base_updated_at forever.
.where(
and(
isNull(notes.conflictServerNote),
// A note cascaded by an in-flight folder delete is owned by that
// operation until the server rules on it (see deleteFolderCascade).
sql`NOT EXISTS (
SELECT 1 FROM ${folderDeleteJournal}
WHERE ${folderDeleteJournal.entityType} = 'note'
AND ${folderDeleteJournal.entityId} = ${notes.id}
)`,
or(
eq(notes.pendingSync, 1),
and(eq(notes.isPrivate, 0), or(hasDirtySegment, hasDirtySpeaker)),
),
),
)
.all()
);
}
applyRemoteFolder(remote: RemoteFolder, options: ApplyRemoteOptions = {}): void {
const matchCondition = remote.client_folder_id
? or(eq(folders.clientFolderId, remote.client_folder_id), eq(folders.remoteId, remote.id))
: eq(folders.remoteId, remote.id);
const local = this.database.select().from(folders).where(matchCondition).get();
// Server-deleted row we've never seen locally → nothing to do.
if (!local && remote.deleted_at) return;
if (!local) {
this.database
.insert(folders)
.values({
name: remote.name,
isDefault: remote.is_default ? 1 : 0,
sortOrder: remote.sort_order,
clientFolderId: remote.client_folder_id,
remoteId: remote.id,
deletedAt: remote.deleted_at,
pendingSync: 0,
// Never leave space_id NULL: the personal pass owns the private
// space, the team pass passes the space it resolved.
spaceId: options.spaceId ?? this.getPrivateSpaceId(),
updatedAt: remote.updated_at,
})
.run();
return;
}
if (local.pendingSync === 1) return;
if (remote.deleted_at) {
this.database.delete(folders).where(eq(folders.id, local.id)).run();
return;
}
this.database
.update(folders)
.set({
name: remote.name,
isDefault: remote.is_default ? 1 : 0,
sortOrder: remote.sort_order,
clientFolderId: remote.client_folder_id ?? local.clientFolderId,
remoteId: remote.id,
deletedAt: null,
pendingSync: 0,
// Only the team pass relocates an existing folder; without an explicit
// space the row keeps whatever space it already sits in.
...(options.spaceId !== undefined ? { spaceId: options.spaceId } : {}),
updatedAt: remote.updated_at,
})
.where(eq(folders.id, local.id))
.run();
}
getNoteForRemote(remote: Pick<RemoteNote, 'id' | 'client_note_id'>): Note | null {
const matchCondition = remote.client_note_id
? or(eq(notes.clientNoteId, remote.client_note_id), eq(notes.remoteId, remote.id))
: eq(notes.remoteId, remote.id);
return this.database.select().from(notes).where(matchCondition).get() ?? null;
}
// Cloud ownership columns are populated only by the team pass (scope=all
// rows carry them). Each field is written only when the remote row actually
// supplies it, so a response that omits one never clears what we already know.
private remoteOwnershipValues(
remote: RemoteNote,
applyOwnership: boolean | undefined,
): { ownerUserId?: string | null; updatedByUserId?: string | null } {
if (!applyOwnership) return {};
return {
...(remote.user_id !== undefined ? { ownerUserId: remote.user_id } : {}),
...(remote.updated_by_user_id !== undefined
? { updatedByUserId: remote.updated_by_user_id }
: {}),
};
}
forkNoteToPrivate(noteId: number): void {
// A brand-new personal identity: the old server row is no longer ours, so
// dropping remote_id/cloud_updated_at plus minting a new client_note_id
// makes the next push re-create this note instead of retrying a write the
// server would reject. left_team/conflict_server_note are cleared because
// both are claims against the identity we just discarded.
this.database
.update(notes)
.set({
spaceId: this.getPrivateSpaceId(),
folderId: null,
clientNoteId: randomUUID(),
remoteId: null,
cloudUpdatedAt: null,
ownerUserId: null,
updatedByUserId: null,
leftTeam: 0,
conflictServerNote: null,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(eq(notes.id, noteId))
.run();
}
applyRemoteNote(
remote: RemoteNote,
resolveFolder: (serverFolderId: string | null) => number | null,
options: ApplyRemoteNoteOptions = {},
): void {
const local = this.getNoteForRemote(remote);
// A parent folder delete owns this note until its server result is known:
// applying a tombstone or a newer remote row here would race finalize/revert.
if (local && this.isNoteHeldByFolderDelete(local.id)) return;
// Server-deleted row we've never seen locally → nothing to do.
if (!local && remote.deleted_at) return;
// Locally-private row: server has no authority over this note. Most
// common case: we just deleted the cloud copy on going-private, and the
// pull is now bringing back the tombstone — which would hard-delete the
// local row if we let it. Skip.
if (local?.isPrivate === 1) {
if (remote.access_removed) return;
if (remote.deleted_at) {
this.database
.update(notes)
.set({ remoteId: null, clientNoteId: null, cloudUpdatedAt: null })
.where(eq(notes.id, local.id))
.run();
return;
}
// A create can reach the server even if its response was lost before the
// privacy toggle. Recover its identity for deletion without importing content.
if (!local.remoteId) {
this.database
.update(notes)
.set({ remoteId: remote.id })
.where(eq(notes.id, local.id))
.run();
}
return;
}
// Row parked in conflict (see parkNoteConflict): never implicitly clear it —
// that's resolveConflictKeepMine/UseServer's job. But if the server has
// moved on since the conflict was recorded, refresh the stashed copy so a
// future banner always offers the latest server version.
if (local?.conflictServerNote != null) {
const stored = this.parseStoredConflictNote(local.conflictServerNote);
if (!stored || stored.updated_at !== remote.updated_at) {
this.database
.update(notes)
.set({ conflictServerNote: JSON.stringify(remote) })
.where(eq(notes.id, local.id))
.run();
}
return;
}
const localFolderId = resolveFolder(remote.folder_id);
if (!local) {
const inserted = this.database
.insert(notes)
.values({
title: remote.title ?? 'Untitled',
content: remote.content,
folderId: localFolderId,
noteType: remote.note_type,
sourceFile: remote.source_file,
audioDurationSeconds: remote.audio_duration_seconds,
calendarEventId: remote.calendar_event_id ?? null,
participants: remote.participants ?? null,
enhancedContent: remote.enhanced_content,
enhancementPrompt: remote.enhancement_prompt,
clientNoteId: remote.client_note_id,
remoteId: remote.id,
deletedAt: remote.deleted_at,
pendingSync: 0,
isPrivate: 0,
// Never leave space_id NULL: the personal pass owns the private
// space, the team pass passes the space it resolved.
spaceId: options.spaceId ?? this.getPrivateSpaceId(),
...this.remoteOwnershipValues(remote, options.applyOwnership),
...(remote.created_at ? { createdAt: remote.created_at } : {}),
cloudUpdatedAt: remote.updated_at,
updatedAt: remote.updated_at,
})
.returning()
.get();
if (remote.transcript != null) {
this.applyRemoteTranscript(inserted.id, remote.transcript);
}
return;
}
// Pending local change → skip apply. Preserves local delete vs remote edit.
if (local.pendingSync === 1) return;
if (remote.deleted_at) {
this.hardDeleteNote(local.id);
return;
}
this.applyRemoteNoteToExisting(local, remote, localFolderId, options);
}
// Shared by applyRemoteNote's normal update path and resolveConflictUseServer
// (which bypasses the pendingSync guard above by calling this directly).
// Always clears conflictServerNote: on the normal path it's already null
// (the guard above returns early otherwise), so this is a no-op there.
private applyRemoteNoteToExisting(
local: Note,
remote: RemoteNote,
localFolderId: number | null,
options: ApplyRemoteNoteOptions & { forceTranscript?: boolean } = {},
): void {
// A note filed in a folder the server has never seen reaches it unfiled, so a null
// coming back keeps the note in that folder. While the folder is still on its way up,
// the note is queued again, and pushNotes sends it once the folder has a cloud id.
// A team pass that moves the note to another space is followed: the folder stays behind.
const folderUnknownToServer =
remote.folder_id == null ? this.getFolderUnknownToServer(local.folderId) : null;
const unsyncedFolder =
options.spaceId === undefined || folderUnknownToServer?.spaceId === options.spaceId
? folderUnknownToServer
: null;
this.database
.update(notes)
.set({
title: remote.title ?? 'Untitled',
content: remote.content,
folderId: unsyncedFolder ? unsyncedFolder.id : localFolderId,
noteType: remote.note_type,
// Device-local recordings push as null, so a null coming back must not erase the
// path this device still plays and reprocesses from.
sourceFile:
remote.source_file ??
(isManagedMeetingAudioUri(local.id, local.sourceFile) ? local.sourceFile : null),
audioDurationSeconds: remote.audio_duration_seconds,
calendarEventId: remote.calendar_event_id ?? null,
participants: remote.participants ?? null,
enhancedContent: remote.enhanced_content,
enhancementPrompt: remote.enhancement_prompt,
clientNoteId: remote.client_note_id ?? local.clientNoteId,
remoteId: remote.id,
deletedAt: null,
pendingSync: isFolderAwaitingUpload(unsyncedFolder) ? 1 : 0,
conflictServerNote: null,
// Only the team pass relocates an existing note; without an explicit
// space the row keeps whatever space it already sits in.
...(options.spaceId !== undefined ? { spaceId: options.spaceId } : {}),
...this.remoteOwnershipValues(remote, options.applyOwnership),
// Also repairs rows pulled before created_at synced, which carry their pull time.
...(remote.created_at ? { createdAt: remote.created_at } : {}),
cloudUpdatedAt: remote.updated_at,
updatedAt: remote.updated_at,
})
.where(eq(notes.id, local.id))
.run();
this.clearSyncState(pushRejectedKey(local.id));
// Rebuild the transcript when the server sent a different one. Normally
// gated on !hasDirtyTranscript (un-pushed local edits are authoritative
// during an ordinary pull) — options.forceTranscript (resolveConflictUseServer
// only) bypasses that: the user explicitly chose the server's copy, so a
// dirty local transcript must not survive to silently re-push over it.
const transcriptChanged = remote.transcript != null && remote.transcript !== local.transcript;
const shouldRebuild =
transcriptChanged && (options.forceTranscript || !this.hasDirtyTranscript(local.id));
if (shouldRebuild) {
this.applyRemoteTranscript(local.id, remote.transcript as string);
} else if (options.forceTranscript) {
// No rebuild happened (remote carries no transcript, or it already
// matches local's), but there may still be locally-dirty segments/speakers
// left over — clear them so they don't resurface in getPendingNotes and
// push stale content over the server copy the user just chose.
this.clearDirtyTranscriptRows(local.id);
}
}
// Marks any pendingSync=1 segments/speakers for a note as clean without
// touching their content — used by resolveConflictUseServer to make sure a
// dirty local transcript doesn't resurface in getPendingNotes after the user
// chose the server's copy (mirrors the pendingSync:0 outcome of a normal
// applyRemoteTranscript rebuild, but without discarding/replacing rows when
// there's no remote transcript to rebuild from).
private clearDirtyTranscriptRows(noteId: number): void {
this.database
.update(transcriptSegments)
.set({ pendingSync: 0 })
.where(and(eq(transcriptSegments.noteId, noteId), eq(transcriptSegments.pendingSync, 1)))
.run();
this.database
.update(speakers)
.set({ pendingSync: 0 })
.where(and(eq(speakers.noteId, noteId), eq(speakers.pendingSync, 1)))
.run();
}
private parseStoredConflictNote(raw: string): RemoteNote | null {
try {
return JSON.parse(raw) as RemoteNote;
} catch {
return null;
}
}
getFolderByRemoteId(remoteId: string): Folder | null {
return this.database.select().from(folders).where(eq(folders.remoteId, remoteId)).get() ?? null;
}
private getFolderUnknownToServer(folderId: number | null): Folder | null {
if (folderId == null) return null;
const folder = this.database
.select()
.from(folders)
.where(and(eq(folders.id, folderId), isNull(folders.deletedAt)))
.get();
return folder && !folder.remoteId ? folder : null;
}
private resolveFolderByRemoteId(serverFolderId: string | null): number | null {
if (!serverFolderId) return null;
return this.getFolderByRemoteId(serverFolderId)?.id ?? null;
}
markFolderPushed(
localId: number,
remoteId: string,
serverUpdatedAt: string,
pushed?: Folder,
): void {
const current = this.database.select().from(folders).where(eq(folders.id, localId)).get();
if (!current || (pushed && current.clientFolderId !== pushed.clientFolderId)) return;
const unchanged =
!pushed ||
(['name', 'sortOrder', 'spaceId', 'deletedAt', 'updatedAt'] as const).every(
(key) => current[key] === pushed[key],
);
this.database
.update(folders)
.set(unchanged ? { remoteId, pendingSync: 0, updatedAt: serverUpdatedAt } : { remoteId })
.where(eq(folders.id, localId))
.run();
}
markFolderTerminal(localId: number): void {
// Stop re-attempting, but keep the local row exactly as the user left it
// (mirrors markNoteTerminal). A later rename re-flags pendingSync.
this.database.update(folders).set({ pendingSync: 0 }).where(eq(folders.id, localId)).run();
}
adoptDuplicateFolder(
survivingLocalId: number,
duplicateLocalId: number,
remoteId: string,
serverUpdatedAt: string,
): void {
this.database.transaction((tx) => {
// No pendingSync bump: both rows already resolve to the same server
// folder once this transaction commits, so the note's remote folder_id
// is unaffected — this only fixes up which LOCAL row it points at.
tx.update(notes)
.set({ folderId: survivingLocalId })
.where(eq(notes.folderId, duplicateLocalId))
.run();
tx.update(folders)
.set({ remoteId, pendingSync: 0, updatedAt: serverUpdatedAt })
.where(eq(folders.id, survivingLocalId))
.run();
tx.delete(folders).where(eq(folders.id, duplicateLocalId)).run();
});
}
// serverUpdatedAt drives local bookkeeping (`updatedAt`) and, by default,
// also seeds/refreshes `cloudUpdatedAt` (the base echoed back as
// base_updated_at on the next update). The two diverge for the batch-create
// caller only: older backends (pre-2026-07-28) omit `updated_at` from the
// create response, so pushNotes.ts falls back to a local-clock value for
// `updatedAt` bookkeeping — but that local-clock value is NOT a real server
// ack and must never become the sync base (it's guaranteed to mismatch the
// server's real updated_at, false-409-parking the row on its very next
// edit). That caller passes cloudUpdatedAt explicitly as `null` to opt out;
// the PATCH-update caller always has a genuine RemoteNote.updated_at, so it
// relies on the default (identical to today's behavior).
markNotePushed(
pushed: Note,
remoteId: string,
serverUpdatedAt: string,
cloudUpdatedAt: string | null = serverUpdatedAt,
serverCreatedAt?: string,
): void {
const current = this.getNoteById(pushed.id);
// Forked or re-identified while the request was in flight: the ack names
// a server row this note no longer corresponds to.
if (!current || current.clientNoteId !== pushed.clientNoteId) return;
// The network request may have been in flight while the user edited or
// deleted the note (mirrors markTranscriptPushed). Only settle the row when
// what was serialized is still what is stored; otherwise keep it queued and
// record the server revision so the follow-up push PATCHes the right base
// instead of re-creating the note.
const unchanged = NOTE_PUSH_ACK_FIELDS.every((field) => current[field] === pushed[field]);
const createdAt = serverCreatedAt ? { createdAt: serverCreatedAt } : {};
this.database
.update(notes)
.set(
unchanged
? { remoteId, pendingSync: 0, updatedAt: serverUpdatedAt, cloudUpdatedAt, ...createdAt }
: { remoteId, cloudUpdatedAt, ...createdAt },
)
.where(eq(notes.id, pushed.id))
.run();
if (unchanged) this.clearSyncState(pushRejectedKey(pushed.id));
}
markNoteTerminal(localId: number): void {
// Clear pendingSync so the row stops re-attempting; preserve the local state
// so the user still sees their attempted change. They can edit it to fix and
// retry — that will re-flag pending.
this.database.transaction((tx) => {
tx.insert(syncState)
.values({ key: pushRejectedKey(localId), value: '1' })
.onConflictDoUpdate({ target: syncState.key, set: { value: '1' } })
.run();
tx.update(notes).set({ pendingSync: 0 }).where(eq(notes.id, localId)).run();
});
}
dropNotePushAttempt(localId: number): void {
// The server refused this write on permission grounds, so its own row is
// whatever it was before — the next pull carries the truth. Clearing
// cloud_updated_at alongside pendingSync means that pull re-seeds the sync
// base instead of leaving a base this device can no longer trust.
this.database.transaction((tx) => {
// A cleared queue flag alone must never be mistaken for uploaded content.
tx.insert(syncState)
.values({ key: pushRejectedKey(localId), value: '1' })
.onConflictDoUpdate({ target: syncState.key, set: { value: '1' } })
.run();
tx.update(notes)
.set({ pendingSync: 0, cloudUpdatedAt: null })
.where(eq(notes.id, localId))
.run();
});
}
parkNoteConflict(localId: number, serverNote: RemoteNote): void {
// pendingSync is intentionally left untouched — the local edit stays
// queued, but getPendingNotes excludes this row while conflictServerNote
// is set, so pushNotes won't retry the stale base_updated_at.
this.database
.update(notes)
.set({ conflictServerNote: JSON.stringify(serverNote) })
.where(eq(notes.id, localId))
.run();
}
listConflictedNotes(): ConflictedNote[] {
const rows = this.database
.select({ id: notes.id, title: notes.title, conflictServerNote: notes.conflictServerNote })
.from(notes)
// Explicit user intent (delete/go-private) always clears conflictServerNote
// (see deleteNote/setNotePrivacy), but filter defensively too: a
// soft-deleted or private row must never reach the future banner.
.where(
and(isNotNull(notes.conflictServerNote), isNull(notes.deletedAt), eq(notes.isPrivate, 0)),
)
.all();
return rows.map((row) => ({
id: row.id,
title: row.title,
// null when the stashed JSON fails to parse (should not happen — this
// repository only ever writes its own JSON.stringify output — but stay
// defensive). The row is still surfaced (rather than silently dropped)
// so the banner can offer keep-mine, which clears the park with no
// dependency on parseable data; see resolveConflictKeepMine.
conflictServerNote: row.conflictServerNote
? this.parseStoredConflictNote(row.conflictServerNote)
: null,
}));
}
resolveConflictKeepMine(noteId: number): void {
const row = this.database
.select({ conflictServerNote: notes.conflictServerNote })
.from(notes)
.where(eq(notes.id, noteId))
.get();
if (!row?.conflictServerNote) return;
const stored = this.parseStoredConflictNote(row.conflictServerNote);
// Clear the park even when the stashed JSON is corrupt: keep-mine needs no
// data from the copy (it just stops retrying with the old base). Without
// this, a corrupt stash would permanently wedge the row — invisible to a
// naive listConflictedNotes and forever excluded from getPendingNotes.
// Adopting the server's updated_at as the new base is a no-op when there's
// nothing to parse; cloudUpdatedAt is simply left as-is.
this.database
.update(notes)
.set({
conflictServerNote: null,
pendingSync: 1,
...(stored ? { cloudUpdatedAt: stored.updated_at } : {}),
})
.where(eq(notes.id, noteId))
.run();
}
resolveConflictUseServer(noteId: number): void {
const local = this.database.select().from(notes).where(eq(notes.id, noteId)).get();
if (!local?.conflictServerNote) return;
const remote = this.parseStoredConflictNote(local.conflictServerNote);
// Corrupt stashed JSON: unlike keep-mine, there is no local-only fallback
// here — "use server" has nothing to apply, so leave the row parked rather
// than guessing.
if (!remote) return;
if (remote.deleted_at) {
// The server's authoritative state (as of the conflict) is "deleted".
// "Use server's copy" must mean accepting that, not resurrecting a
// zombie local row that still points remoteId at a gone server note —
// the delta-cursor pull may never re-deliver that tombstone once our
// watermark has moved past it. Mirrors applyRemoteNote's tombstone path.
this.hardDeleteNote(local.id);
return;
}
// Bypasses applyRemoteNote's pendingSync/conflict guards on purpose — the
// user explicitly chose the server's copy, so the local edit is discarded.
// forceTranscript additionally bypasses the hasDirtyTranscript guard so a
// dirty local transcript doesn't survive to silently re-push over the
// server copy just chosen.
const localFolderId = this.resolveFolderByRemoteId(remote.folder_id);
this.applyRemoteNoteToExisting(local, remote, localFolderId, { forceTranscript: true });
}
hardDeleteFolder(localId: number): void {
this.database.delete(folders).where(eq(folders.id, localId)).run();
}
isNotePushRejected(localId: number): boolean {
return this.getSyncState(pushRejectedKey(localId)) !== null;
}
hardDeleteNote(localId: number): void {
this.clearSyncState(pushRejectedKey(localId));
this.deleteNoteChildrenAndAudio(localId);
this.database.delete(notes).where(eq(notes.id, localId)).run();
}
getSyncState(key: string): string | null {
const row = this.database.select().from(syncState).where(eq(syncState.key, key)).get();
return row?.value ?? null;
}
setSyncState(key: string, value: string): void {
this.database
.insert(syncState)
.values({ key, value })
.onConflictDoUpdate({ target: syncState.key, set: { value } })
.run();
}
clearSyncState(key: string): void {
this.database.delete(syncState).where(eq(syncState.key, key)).run();
}
getFoldersMissingClientId(): Folder[] {
return this.database.select().from(folders).where(isNull(folders.clientFolderId)).all();
}
getNotesMissingClientId(): Note[] {
return this.database.select().from(notes).where(isNull(notes.clientNoteId)).all();
}
setFolderClientId(localId: number, clientFolderId: string): Folder {
return this.database
.update(folders)
.set({ clientFolderId, pendingSync: 1 })
.where(eq(folders.id, localId))
.returning()
.get();
}
setNoteClientId(localId: number, clientNoteId: string): Note {
return this.database
.update(notes)
.set({ clientNoteId, pendingSync: 1 })
.where(eq(notes.id, localId))
.returning()
.get();
}
setNotePrivacy(localId: number, isPrivate: boolean): void {
// Private rows stop uploading immediately, but keep their identifiers until
// cloud deletion succeeds. Explicit privacy changes also supersede a parked
// conflict so later publication can proceed under a fresh server identity.
this.database
.update(notes)
.set({
isPrivate: isPrivate ? 1 : 0,
conflictServerNote: null,
...(isPrivate
? { pendingSync: 0, updatedAt: sql`datetime('now')` }
: {
pendingSync: 1,
clientNoteId: randomUUID(),
remoteId: null,
cloudUpdatedAt: null,
updatedAt: sql`datetime('now')`,
}),
})
.where(eq(notes.id, localId))
.run();
}
clearNoteRemoteId(localId: number): void {
this.database
.update(notes)
.set({ remoteId: null, cloudUpdatedAt: null })
.where(eq(notes.id, localId))
.run();
}
/**
* Called when an anonymous onboarding session links to an account. The server
* migrates billing only, so every remote id here still names a row it keeps
* under the anonymous user: forget them and re-dirty the rows so the next push
* re-creates everything under the account instead of PATCHing ids it does not
* own. Client ids are kept — this is the same content, not a fork.
*/
dropRemoteIdsForAccountLink(): void {
this.database
.update(notes)
.set({
remoteId: null,
cloudUpdatedAt: null,
ownerUserId: null,
updatedByUserId: null,
pendingSync: 1,
})
.run();
for (const table of [folders, transcriptSegments, speakers, dictionaryEntries, snippets]) {
this.database.update(table).set({ remoteId: null, pendingSync: 1 }).run();
}
}
clearNoteClientId(localId: number): void {
this.database.update(notes).set({ clientNoteId: null }).where(eq(notes.id, localId)).run();
}
wipeAllSyncableData(): void {
// Free persisted meeting WAVs + diarization rows (FK cascade is inert) before wiping notes.
const rows = this.database
.select({ id: notes.id, sourceFile: notes.sourceFile })
.from(notes)
.all();
for (const row of rows) {
if (row.sourceFile && isManagedMeetingAudioUri(row.id, row.sourceFile)) {
FileSystem.deleteAsync(row.sourceFile, { idempotent: true }).catch(() => {});
}
}
this.database.delete(transcriptSegments).run();
this.database.delete(speakers).run();
this.database.delete(speakerProfiles).run();
// Deleting notes/folders wholesale also drops every per-note conflict
// stash (conflict_server_note lives on the notes row itself).
this.database.delete(notes).run();
this.database.delete(folders).run();
// Team spaces belong to the account that's being switched away from; the
// private space is device-local (getPrivateSpaceId() must keep resolving
// to it) and is deliberately left alone — see the class-level comment above.
this.database.delete(spaces).where(eq(spaces.kind, 'team')).run();
this.database.delete(dictionaryEntries).run();
this.database.delete(snippets).run();
// Wholesale, not per-key: also clears the team cursors/capability flag/park
// keys (teamCursors.ts, teamSpacesCapability.ts, pullParkTracker.ts) —
// every sync_state row is per-account bookkeeping with no reason to
// survive a user switch. The caller re-seeds sync.user_id right after.
this.database.delete(syncState).run();
}
// Transcript segments
getSegments(noteId: number): Segment[] {
return this.database
.select()
.from(transcriptSegments)
.where(and(eq(transcriptSegments.noteId, noteId), isNull(transcriptSegments.deletedAt)))
.orderBy(asc(transcriptSegments.sortOrder), asc(transcriptSegments.startMs))
.all();
}
replaceSegments(noteId: number, segments: NewSegment[]): void {
this.database.transaction((tx) => {
tx.delete(transcriptSegments).where(eq(transcriptSegments.noteId, noteId)).run();
segments.forEach((seg, index) => {
tx.insert(transcriptSegments)
.values({ ...seg, noteId, sortOrder: seg.sortOrder ?? index, pendingSync: 1 })
.run();
});
});
this.markNoteTranscriptDirty(noteId);
}
// A transcript edit (segments/speakers) must dirty the parent note so the sync
// engine's note-level `getPendingNotes` picks it up. Gated on privacy: private
// notes never sync, and marking one would trip pushNotes' private-note guard.
private markNoteTranscriptDirty(noteId: number): void {
const row = this.database
.select({ isPrivate: notes.isPrivate })
.from(notes)
.where(eq(notes.id, noteId))
.get();
if (!row || row.isPrivate === 1) return;
this.database
.update(notes)
.set({ pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(notes.id, noteId))
.run();
}
// Cross-device transcript sync (desktop <-> mobile via notes.transcript)
applyRemoteTranscript(noteId: number, raw: string): void {
const parsed = parseRemoteTranscript(raw);
this.database.transaction((tx) => {
// Only rebuild rows when the raw actually parses; a malformed string still
// updates notes.transcript (so the equality check converges) but never
// destroys local segments.
if (parsed) {
const existingSpeakers = tx
.select()
.from(speakers)
.where(and(eq(speakers.noteId, noteId), isNull(speakers.deletedAt)))
.all();
const existingSpeakerByLabel = new Map(
existingSpeakers.map((speaker) => [speaker.speakerLabel, speaker]),
);
tx.delete(transcriptSegments).where(eq(transcriptSegments.noteId, noteId)).run();
tx.delete(speakers).where(eq(speakers.noteId, noteId)).run();
parsed.speakers.forEach((speaker, index) => {
const existing = existingSpeakerByLabel.get(speaker.speakerLabel);
tx.insert(speakers)
.values({
...speaker,
noteId,
// Voice-profile links and colors are device-local. Preserve them
// when the wire speaker identity survives a remote transcript edit.
profileId: existing?.profileId ?? null,
color: existing?.color ?? null,
sortOrder: speaker.sortOrder ?? index,
pendingSync: 0,
})
.run();
});
parsed.segments.forEach((segment, index) => {
tx.insert(transcriptSegments)
.values({ ...segment, noteId, sortOrder: segment.sortOrder ?? index, pendingSync: 0 })
.run();
});
}
tx.update(notes).set({ transcript: raw }).where(eq(notes.id, noteId)).run();
});
}
hasDirtyTranscript(noteId: number): boolean {
const dirtySegment = this.database
.select({ id: transcriptSegments.id })
.from(transcriptSegments)
.where(
and(
eq(transcriptSegments.noteId, noteId),
eq(transcriptSegments.pendingSync, 1),
isNull(transcriptSegments.deletedAt),
),
)
.get();
if (dirtySegment) return true;
const dirtySpeaker = this.database
.select({ id: speakers.id })
.from(speakers)
.where(
and(eq(speakers.noteId, noteId), eq(speakers.pendingSync, 1), isNull(speakers.deletedAt)),
)
.get();
return !!dirtySpeaker;
}
markTranscriptPushed(
noteId: number,
raw: string,
segmentIds: number[],
speakerIds: number[],
): void {
// The network request may have been in flight while the user edited the
// transcript. IDs alone are insufficient (a speaker rename keeps its ID),
// so only acknowledge the snapshot when the current wire representation is
// still identical. Otherwise keep the parent queued for the next sync.
const currentRaw = serializeSegmentsForSync(
this.getSegments(noteId),
this.getSpeakers(noteId),
this.getNoteById(noteId)?.transcript,
);
if (currentRaw !== raw) {
this.markNoteTranscriptDirty(noteId);
return;
}
this.database.transaction((tx) => {
tx.update(notes).set({ transcript: raw }).where(eq(notes.id, noteId)).run();
if (segmentIds.length > 0) {
tx.update(transcriptSegments)
.set({ pendingSync: 0 })
.where(inArray(transcriptSegments.id, segmentIds))
.run();
}
if (speakerIds.length > 0) {
tx.update(speakers).set({ pendingSync: 0 }).where(inArray(speakers.id, speakerIds)).run();
}
});
}
// Speakers
getSpeakers(noteId: number): Speaker[] {
return this.database
.select()
.from(speakers)
.where(and(eq(speakers.noteId, noteId), isNull(speakers.deletedAt)))
.orderBy(asc(speakers.sortOrder))
.all();
}
upsertSpeakers(noteId: number, rows: NewSpeaker[]): void {
this.database.transaction((tx) => {
// Numbered after the note's existing speakers, so a re-diarization that adds one
// never shows a second "Speaker 1".
const nextSortOrder = tx
.select({ sortOrder: speakers.sortOrder })
.from(speakers)
.where(and(eq(speakers.noteId, noteId), isNull(speakers.deletedAt)))
.all()
.reduce((next, row) => Math.max(next, row.sortOrder + 1), 0);
rows.forEach((row, index) => {
tx.insert(speakers)
.values({
...row,
noteId,
sortOrder: row.sortOrder ?? nextSortOrder + index,
pendingSync: 1,
})
.run();
});
});
}
updateSpeaker(id: number, updates: Partial<Speaker>): void {
this.database
.update(speakers)
.set({ ...updates, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(eq(speakers.id, id))
.run();
const row = this.database
.select({ noteId: speakers.noteId })
.from(speakers)
.where(eq(speakers.id, id))
.get();
if (row) this.markNoteTranscriptDirty(row.noteId);
}
mergeSpeakers(
noteId: number,
sourceSpeakerId: number,
targetSpeakerId: number,
targetUpdates: Partial<Speaker>,
): void {
if (sourceSpeakerId === targetSpeakerId) {
throw new Error('Cannot merge a speaker into itself');
}
const source = this.database
.select()
.from(speakers)
.where(
and(
eq(speakers.id, sourceSpeakerId),
eq(speakers.noteId, noteId),
isNull(speakers.deletedAt),
),
)
.get();
const target = this.database
.select()
.from(speakers)
.where(
and(
eq(speakers.id, targetSpeakerId),
eq(speakers.noteId, noteId),
isNull(speakers.deletedAt),
),
)
.get();
if (!source) throw new Error('Source speaker not found');
if (!target) throw new Error('Target speaker not found');
this.database.transaction((tx) => {
tx.update(transcriptSegments)
.set({
speakerLabel: target.speakerLabel,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(
and(
eq(transcriptSegments.noteId, noteId),
eq(transcriptSegments.speakerLabel, source.speakerLabel),
isNull(transcriptSegments.deletedAt),
),
)
.run();
tx.update(speakers)
.set({ ...targetUpdates, pendingSync: 1, updatedAt: sql`datetime('now')` })
.where(
and(eq(speakers.id, target.id), eq(speakers.noteId, noteId), isNull(speakers.deletedAt)),
)
.run();
tx.update(speakers)
.set({
deletedAt: sql`datetime('now')`,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(
and(eq(speakers.id, source.id), eq(speakers.noteId, noteId), isNull(speakers.deletedAt)),
)
.run();
});
this.markNoteTranscriptDirty(noteId);
}
// Speaker profiles
getSpeakerProfiles(): SpeakerProfile[] {
return this.database
.select()
.from(speakerProfiles)
.orderBy(asc(speakerProfiles.id))
.all()
.map((row) => this.mapSpeakerProfile(row));
}
getSpeakerProfileById(id: number): SpeakerProfile | null {
const row = this.database
.select()
.from(speakerProfiles)
.where(eq(speakerProfiles.id, id))
.get();
return row ? this.mapSpeakerProfile(row) : null;
}
createSpeakerProfile(input: NewSpeakerProfile): SpeakerProfile {
validateSpeakerProfileOwnerFlag(input.isOwner);
this.validateSpeakerProfileEmbedding(input.embedding);
try {
const row = this.database
.insert(speakerProfiles)
.values({ ...input, embedding: encodeSpeakerProfileEmbedding(input.embedding) })
.returning()
.get();
return this.mapSpeakerProfile(row);
} catch (error) {
mapSpeakerProfileOwnerConstraint(error);
}
}
createOwnerProfileForSpeaker(
speakerId: number,
profileInput: Omit<NewSpeakerProfile, 'isOwner'>,
speakerPatch: Partial<Speaker>,
): SpeakerProfile {
this.validateSpeakerProfileEmbedding(profileInput.embedding);
let result: { profile: SpeakerProfile; noteId: number };
try {
result = this.database.transaction((tx) => {
const row = tx
.insert(speakerProfiles)
.values({
...profileInput,
isOwner: 1,
embedding: encodeSpeakerProfileEmbedding(profileInput.embedding),
})
.returning()
.get();
const profile = this.mapSpeakerProfile(row);
const [linked] = tx
.update(speakers)
.set({
...speakerPatch,
profileId: profile.id,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(and(eq(speakers.id, speakerId), isNull(speakers.deletedAt)))
.returning({ noteId: speakers.noteId })
.all();
// Throwing rolls back the insert, so no owner profile is saved without its speaker.
if (!linked) throw new Error(`Speaker ${speakerId} not found`);
return { profile, noteId: linked.noteId };
});
} catch (error) {
mapSpeakerProfileOwnerConstraint(error);
}
this.markNoteTranscriptDirty(result.noteId);
return result.profile;
}
updateSpeakerProfile(id: number, updates: Partial<SpeakerProfile>): void {
const { embedding, ...rest } = updates;
delete rest.id;
validateSpeakerProfileOwnerFlag(rest.isOwner);
const dbUpdates: Partial<typeof speakerProfiles.$inferInsert> = { ...rest };
if (embedding !== undefined) {
this.validateSpeakerProfileEmbedding(embedding, id);
dbUpdates.embedding = encodeSpeakerProfileEmbedding(embedding);
}
try {
this.database
.update(speakerProfiles)
.set({ ...dbUpdates, updatedAt: sql`datetime('now')` })
.where(eq(speakerProfiles.id, id))
.run();
} catch (error) {
mapSpeakerProfileOwnerConstraint(error);
}
}
deleteSpeakerProfile(id: number): void {
this.database.transaction((tx) => {
tx.update(speakers)
.set({
profileId: null,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(
and(
eq(speakers.profileId, id),
eq(speakers.speakerLocked, 0),
or(isNull(speakers.speakerLockSource), ne(speakers.speakerLockSource, 'user')),
),
)
.run();
tx.delete(speakerProfiles).where(eq(speakerProfiles.id, id)).run();
});
}
deleteAllSpeakerProfiles(): void {
this.database.transaction((tx) => {
tx.update(speakers)
.set({
profileId: null,
pendingSync: 1,
updatedAt: sql`datetime('now')`,
})
.where(
and(
isNotNull(speakers.profileId),
eq(speakers.speakerLocked, 0),
or(isNull(speakers.speakerLockSource), ne(speakers.speakerLockSource, 'user')),
),
)
.run();
tx.delete(speakerProfiles).run();
});
}
// Transcription status
getTranscriptionStatus(noteId: number): TranscriptionStatus {
const row = this.database
.select({ status: notes.transcriptionStatus })
.from(notes)
.where(eq(notes.id, noteId))
.get();
return row && isTranscriptionStatus(row.status ?? '')
? (row.status as TranscriptionStatus)
: 'idle';
}
setTranscriptionStatus(noteId: number, status: TranscriptionStatus): void {
this.database
.update(notes)
.set({ transcriptionStatus: status, updatedAt: sql`datetime('now')` })
.where(eq(notes.id, noteId))
.run();
}
updateNoteMeta(noteId: number, updates: MeetingNoteUpdate): void {
this.database
.update(notes)
.set({ ...updates, updatedAt: sql`datetime('now')` })
.where(eq(notes.id, noteId))
.run();
}
restoreMeetingRecordingPath(noteId: number, sourceFile: string): void {
this.database
.update(notes)
.set({ sourceFile })
.where(and(eq(notes.id, noteId), isNull(notes.sourceFile)))
.run();
}
updateNoteCalendarContext(noteId: number, updates: MeetingCalendarContextUpdate): void {
const row = this.database
.select({ isPrivate: notes.isPrivate })
.from(notes)
.where(eq(notes.id, noteId))
.get();
this.database
.update(notes)
.set({
...updates,
...(row?.isPrivate === 1 ? {} : { pendingSync: 1 }),
updatedAt: sql`datetime('now')`,
})
.where(eq(notes.id, noteId))
.run();
}
}