Skip to content

Commit adaff67

Browse files
colbymchenrycolbymchenry
authored
Merge pull request colbymchenry#25 from MO2k4/feat/db-performance-schema-v2
feat: Schema v2 migration + database performance
2 parents 04e1f3e + 1c023c4 commit adaff67

5 files changed

Lines changed: 116 additions & 60 deletions

File tree

‎src/db/index.ts‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,14 @@ export class DatabaseConnection {
5252
const schema = fs.readFileSync(schemaPath, 'utf-8');
5353
db.exec(schema);
5454

55+
// Record current schema version so migrations aren't re-applied on open
56+
const currentVersion = getCurrentVersion(db);
57+
if (currentVersion < CURRENT_SCHEMA_VERSION) {
58+
db.prepare(
59+
'INSERT OR IGNORE INTO schema_versions (version, applied_at, description) VALUES (?, ?, ?)'
60+
).run(CURRENT_SCHEMA_VERSION, Date.now(), 'Initial schema includes all migrations');
61+
}
62+
5563
return new DatabaseConnection(db, dbPath);
5664
}
5765

‎src/db/migrations.ts‎

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -29,11 +29,19 @@ interface Migration {
2929
const migrations: Migration[] = [
3030
{
3131
version: 2,
32-
description: 'Add filePath and language to unresolved_refs for performance',
32+
description: 'Add project metadata, provenance tracking, and unresolved ref context',
3333
up: (db) => {
3434
db.exec(`
35-
ALTER TABLE unresolved_refs ADD COLUMN file_path TEXT;
36-
ALTER TABLE unresolved_refs ADD COLUMN language TEXT;
35+
CREATE TABLE IF NOT EXISTS project_metadata (
36+
key TEXT PRIMARY KEY,
37+
value TEXT NOT NULL,
38+
updated_at INTEGER NOT NULL
39+
);
40+
ALTER TABLE unresolved_refs ADD COLUMN file_path TEXT NOT NULL DEFAULT '';
41+
ALTER TABLE unresolved_refs ADD COLUMN language TEXT NOT NULL DEFAULT 'unknown';
42+
ALTER TABLE edges ADD COLUMN provenance TEXT DEFAULT NULL;
43+
CREATE INDEX IF NOT EXISTS idx_unresolved_file_path ON unresolved_refs(file_path);
44+
CREATE INDEX IF NOT EXISTS idx_edges_provenance ON edges(provenance);
3745
`);
3846
},
3947
},

‎src/db/queries.ts‎

Lines changed: 81 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,7 @@ interface EdgeRow {
5454
metadata: string | null;
5555
line: number | null;
5656
col: number | null;
57+
provenance: string | null;
5758
}
5859

5960
interface FileRow {
@@ -74,9 +75,9 @@ interface UnresolvedRefRow {
7475
reference_kind: string;
7576
line: number;
7677
col: number;
77-
file_path: string | null;
78-
language: string | null;
7978
candidates: string | null;
79+
file_path: string;
80+
language: string;
8081
}
8182

8283
/**
@@ -118,6 +119,7 @@ function rowToEdge(row: EdgeRow): Edge {
118119
metadata: row.metadata ? safeJsonParse(row.metadata, undefined) : undefined,
119120
line: row.line ?? undefined,
120121
column: row.col ?? undefined,
122+
provenance: row.provenance as Edge['provenance'],
121123
};
122124
}
123125

@@ -639,8 +641,8 @@ export class QueryBuilder {
639641
insertEdge(edge: Edge): void {
640642
if (!this.stmts.insertEdge) {
641643
this.stmts.insertEdge = this.db.prepare(`
642-
INSERT OR IGNORE INTO edges (source, target, kind, metadata, line, col)
643-
VALUES (@source, @target, @kind, @metadata, @line, @col)
644+
INSERT OR IGNORE INTO edges (source, target, kind, metadata, line, col, provenance)
645+
VALUES (@source, @target, @kind, @metadata, @line, @col, @provenance)
644646
`);
645647
}
646648

@@ -651,6 +653,7 @@ export class QueryBuilder {
651653
metadata: edge.metadata ? JSON.stringify(edge.metadata) : null,
652654
line: edge.line ?? null,
653655
col: edge.column ?? null,
656+
provenance: edge.provenance ?? null,
654657
});
655658
}
656659

@@ -678,10 +681,22 @@ export class QueryBuilder {
678681
/**
679682
* Get outgoing edges from a node
680683
*/
681-
getOutgoingEdges(sourceId: string, kinds?: EdgeKind[]): Edge[] {
682-
if (kinds && kinds.length > 0) {
683-
const sql = `SELECT * FROM edges WHERE source = ? AND kind IN (${kinds.map(() => '?').join(',')})`;
684-
const rows = this.db.prepare(sql).all(sourceId, ...kinds) as EdgeRow[];
684+
getOutgoingEdges(sourceId: string, kinds?: EdgeKind[], provenance?: string): Edge[] {
685+
if ((kinds && kinds.length > 0) || provenance) {
686+
let sql = 'SELECT * FROM edges WHERE source = ?';
687+
const params: (string | number)[] = [sourceId];
688+
689+
if (kinds && kinds.length > 0) {
690+
sql += ` AND kind IN (${kinds.map(() => '?').join(',')})`;
691+
params.push(...kinds);
692+
}
693+
694+
if (provenance) {
695+
sql += ' AND provenance = ?';
696+
params.push(provenance);
697+
}
698+
699+
const rows = this.db.prepare(sql).all(...params) as EdgeRow[];
685700
return rows.map(rowToEdge);
686701
}
687702

@@ -800,8 +815,8 @@ export class QueryBuilder {
800815
insertUnresolvedRef(ref: UnresolvedReference): void {
801816
if (!this.stmts.insertUnresolved) {
802817
this.stmts.insertUnresolved = this.db.prepare(`
803-
INSERT INTO unresolved_refs (from_node_id, reference_name, reference_kind, line, col, file_path, language, candidates)
804-
VALUES (@fromNodeId, @referenceName, @referenceKind, @line, @col, @filePath, @language, @candidates)
818+
INSERT INTO unresolved_refs (from_node_id, reference_name, reference_kind, line, col, candidates, file_path, language)
819+
VALUES (@fromNodeId, @referenceName, @referenceKind, @line, @col, @candidates, @filePath, @language)
805820
`);
806821
}
807822

@@ -811,39 +826,23 @@ export class QueryBuilder {
811826
referenceKind: ref.referenceKind,
812827
line: ref.line,
813828
col: ref.column,
814-
filePath: ref.filePath ?? null,
815-
language: ref.language ?? null,
816829
candidates: ref.candidates ? JSON.stringify(ref.candidates) : null,
830+
filePath: ref.filePath ?? '',
831+
language: ref.language ?? 'unknown',
817832
});
818833
}
819834

820835
/**
821-
* Insert multiple unresolved references in a single transaction
836+
* Insert multiple unresolved references in a transaction
822837
*/
823838
insertUnresolvedRefsBatch(refs: UnresolvedReference[]): void {
824839
if (refs.length === 0) return;
825-
826-
if (!this.stmts.insertUnresolved) {
827-
this.stmts.insertUnresolved = this.db.prepare(`
828-
INSERT INTO unresolved_refs (from_node_id, reference_name, reference_kind, line, col, file_path, language, candidates)
829-
VALUES (@fromNodeId, @referenceName, @referenceKind, @line, @col, @filePath, @language, @candidates)
830-
`);
831-
}
832-
833-
this.db.transaction(() => {
840+
const insert = this.db.transaction(() => {
834841
for (const ref of refs) {
835-
this.stmts.insertUnresolved!.run({
836-
fromNodeId: ref.fromNodeId,
837-
referenceName: ref.referenceName,
838-
referenceKind: ref.referenceKind,
839-
line: ref.line,
840-
col: ref.column,
841-
filePath: ref.filePath ?? null,
842-
language: ref.language ?? null,
843-
candidates: ref.candidates ? JSON.stringify(ref.candidates) : null,
844-
});
842+
this.insertUnresolvedRef(ref);
845843
}
846-
})();
844+
});
845+
insert();
847846
}
848847

849848
/**
@@ -874,9 +873,9 @@ export class QueryBuilder {
874873
referenceKind: row.reference_kind as EdgeKind,
875874
line: row.line,
876875
column: row.col,
877-
filePath: row.file_path ?? undefined,
878-
language: (row.language as Language) ?? undefined,
879-
candidates: row.candidates ? safeJsonParse<string[]>(row.candidates, []) : undefined,
876+
candidates: row.candidates ? safeJsonParse(row.candidates, undefined) : undefined,
877+
filePath: row.file_path,
878+
language: row.language as Language,
880879
}));
881880
}
882881

@@ -891,9 +890,9 @@ export class QueryBuilder {
891890
referenceKind: row.reference_kind as EdgeKind,
892891
line: row.line,
893892
column: row.col,
894-
filePath: row.file_path ?? undefined,
895-
language: (row.language as Language) ?? undefined,
896-
candidates: row.candidates ? safeJsonParse<string[]>(row.candidates, []) : undefined,
893+
candidates: row.candidates ? safeJsonParse(row.candidates, undefined) : undefined,
894+
filePath: row.file_path,
895+
language: row.language as Language,
897896
}));
898897
}
899898

@@ -921,17 +920,13 @@ export class QueryBuilder {
921920
* Get graph statistics
922921
*/
923922
getStats(): GraphStats {
924-
const nodeCount = (
925-
this.db.prepare('SELECT COUNT(*) as count FROM nodes').get() as { count: number }
926-
).count;
927-
928-
const edgeCount = (
929-
this.db.prepare('SELECT COUNT(*) as count FROM edges').get() as { count: number }
930-
).count;
931-
932-
const fileCount = (
933-
this.db.prepare('SELECT COUNT(*) as count FROM files').get() as { count: number }
934-
).count;
923+
// Single query for all three aggregate counts
924+
const counts = this.db.prepare(`
925+
SELECT
926+
(SELECT COUNT(*) FROM nodes) AS node_count,
927+
(SELECT COUNT(*) FROM edges) AS edge_count,
928+
(SELECT COUNT(*) FROM files) AS file_count
929+
`).get() as { node_count: number; edge_count: number; file_count: number };
935930

936931
const nodesByKind = {} as Record<NodeKind, number>;
937932
const nodeKindRows = this.db
@@ -958,9 +953,9 @@ export class QueryBuilder {
958953
}
959954

960955
return {
961-
nodeCount,
962-
edgeCount,
963-
fileCount,
956+
nodeCount: counts.node_count,
957+
edgeCount: counts.edge_count,
958+
fileCount: counts.file_count,
964959
nodesByKind,
965960
edgesByKind,
966961
filesByLanguage,
@@ -969,6 +964,39 @@ export class QueryBuilder {
969964
};
970965
}
971966

967+
// ===========================================================================
968+
// Project Metadata
969+
// ===========================================================================
970+
971+
/**
972+
* Get a metadata value by key
973+
*/
974+
getMetadata(key: string): string | null {
975+
const row = this.db.prepare('SELECT value FROM project_metadata WHERE key = ?').get(key) as { value: string } | undefined;
976+
return row?.value ?? null;
977+
}
978+
979+
/**
980+
* Set a metadata key-value pair (upsert)
981+
*/
982+
setMetadata(key: string, value: string): void {
983+
this.db.prepare(
984+
'INSERT INTO project_metadata (key, value, updated_at) VALUES (?, ?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at'
985+
).run(key, value, Date.now());
986+
}
987+
988+
/**
989+
* Get all metadata as a key-value record
990+
*/
991+
getAllMetadata(): Record<string, string> {
992+
const rows = this.db.prepare('SELECT key, value FROM project_metadata').all() as { key: string; value: string }[];
993+
const result: Record<string, string> = {};
994+
for (const row of rows) {
995+
result[row.key] = row.value;
996+
}
997+
return result;
998+
}
999+
9721000
/**
9731001
* Clear all data from the database
9741002
*/

‎src/db/schema.sql‎

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -11,8 +11,6 @@ CREATE TABLE IF NOT EXISTS schema_versions (
1111
-- Insert initial version
1212
INSERT INTO schema_versions (version, applied_at, description)
1313
VALUES (1, strftime('%s', 'now') * 1000, 'Initial schema');
14-
INSERT INTO schema_versions (version, applied_at, description)
15-
VALUES (2, strftime('%s', 'now') * 1000, 'Add filePath and language to unresolved_refs');
1614

1715
-- =============================================================================
1816
-- Core Tables
@@ -51,6 +49,7 @@ CREATE TABLE IF NOT EXISTS edges (
5149
metadata TEXT, -- JSON object
5250
line INTEGER,
5351
col INTEGER,
52+
provenance TEXT DEFAULT NULL,
5453
FOREIGN KEY (source) REFERENCES nodes(id) ON DELETE CASCADE,
5554
FOREIGN KEY (target) REFERENCES nodes(id) ON DELETE CASCADE
5655
);
@@ -75,9 +74,9 @@ CREATE TABLE IF NOT EXISTS unresolved_refs (
7574
reference_kind TEXT NOT NULL,
7675
line INTEGER NOT NULL,
7776
col INTEGER NOT NULL,
78-
file_path TEXT,
79-
language TEXT,
8077
candidates TEXT, -- JSON array
78+
file_path TEXT NOT NULL DEFAULT '',
79+
language TEXT NOT NULL DEFAULT 'unknown',
8180
FOREIGN KEY (from_node_id) REFERENCES nodes(id) ON DELETE CASCADE
8281
);
8382

@@ -136,6 +135,9 @@ CREATE INDEX IF NOT EXISTS idx_files_modified_at ON files(modified_at);
136135
-- Unresolved refs indexes
137136
CREATE INDEX IF NOT EXISTS idx_unresolved_from_node ON unresolved_refs(from_node_id);
138137
CREATE INDEX IF NOT EXISTS idx_unresolved_name ON unresolved_refs(reference_name);
138+
CREATE INDEX IF NOT EXISTS idx_unresolved_file_path ON unresolved_refs(file_path);
139+
CREATE INDEX IF NOT EXISTS idx_unresolved_from_name ON unresolved_refs(from_node_id, reference_name);
140+
CREATE INDEX IF NOT EXISTS idx_edges_provenance ON edges(provenance);
139141

140142
-- =============================================================================
141143
-- Vector Storage (for future semantic search)
@@ -152,3 +154,10 @@ CREATE TABLE IF NOT EXISTS vectors (
152154
);
153155

154156
CREATE INDEX IF NOT EXISTS idx_vectors_model ON vectors(model);
157+
158+
-- Project metadata for version/provenance tracking
159+
CREATE TABLE IF NOT EXISTS project_metadata (
160+
key TEXT PRIMARY KEY,
161+
value TEXT NOT NULL,
162+
updated_at INTEGER NOT NULL
163+
);

‎src/types.ts‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -166,6 +166,9 @@ export interface Edge {
166166

167167
/** Column number where relationship occurs */
168168
column?: number;
169+
170+
/** How this edge was created */
171+
provenance?: 'tree-sitter' | 'scip' | 'heuristic';
169172
}
170173

171174
/**

0 commit comments

Comments
 (0)