444 lines
13 KiB
TypeScript
444 lines
13 KiB
TypeScript
import { describe, expect, it } from "bun:test";
|
|
import { readFileSync } from "node:fs";
|
|
import { join } from "node:path";
|
|
import { Database } from "bun:sqlite";
|
|
import { __dbInternals } from "./index";
|
|
|
|
function applyMigration(client: Database, fileName: string) {
|
|
const sql = readFileSync(join(process.cwd(), "drizzle", fileName), "utf8");
|
|
client.exec(sql);
|
|
}
|
|
|
|
describe("sqlite schema compatibility bootstrap", () => {
|
|
it("adds missing watchlist columns and taxonomy tables for older local databases", () => {
|
|
const client = new Database(":memory:");
|
|
client.exec("PRAGMA foreign_keys = ON;");
|
|
|
|
applyMigration(client, "0000_cold_silver_centurion.sql");
|
|
applyMigration(client, "0001_glossy_statement_snapshots.sql");
|
|
applyMigration(client, "0002_workflow_task_projection_metadata.sql");
|
|
applyMigration(client, "0003_task_stage_event_timeline.sql");
|
|
applyMigration(client, "0009_task_notification_context.sql");
|
|
|
|
expect(__dbInternals.hasColumn(client, "watchlist_item", "category")).toBe(
|
|
false,
|
|
);
|
|
expect(__dbInternals.hasColumn(client, "watchlist_item", "status")).toBe(
|
|
false,
|
|
);
|
|
expect(__dbInternals.hasColumn(client, "holding", "company_name")).toBe(
|
|
false,
|
|
);
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_snapshot")).toBe(
|
|
false,
|
|
);
|
|
expect(__dbInternals.hasTable(client, "research_journal_entry")).toBe(
|
|
false,
|
|
);
|
|
expect(__dbInternals.hasTable(client, "research_artifact")).toBe(false);
|
|
expect(__dbInternals.hasTable(client, "research_memo")).toBe(false);
|
|
|
|
__dbInternals.ensureLocalSqliteSchema(client);
|
|
|
|
expect(__dbInternals.hasColumn(client, "watchlist_item", "category")).toBe(
|
|
true,
|
|
);
|
|
expect(__dbInternals.hasColumn(client, "watchlist_item", "tags")).toBe(
|
|
true,
|
|
);
|
|
expect(__dbInternals.hasColumn(client, "watchlist_item", "status")).toBe(
|
|
true,
|
|
);
|
|
expect(__dbInternals.hasColumn(client, "watchlist_item", "priority")).toBe(
|
|
true,
|
|
);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "watchlist_item", "updated_at"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "watchlist_item", "last_reviewed_at"),
|
|
).toBe(true);
|
|
expect(__dbInternals.hasColumn(client, "holding", "company_name")).toBe(
|
|
true,
|
|
);
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_snapshot")).toBe(
|
|
true,
|
|
);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"parser_engine",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"parser_version",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"taxonomy_regime",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"faithful_rows",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"surface_rows",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"detail_rows",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_snapshot", "kpi_rows"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"computed_definitions",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"normalization_summary",
|
|
),
|
|
).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_context")).toBe(
|
|
true,
|
|
);
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_fact")).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_concept", "balance"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_concept", "period_type"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_concept", "data_type"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_concept",
|
|
"authoritative_concept_key",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_concept",
|
|
"mapping_method",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_concept", "surface_key"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_concept",
|
|
"detail_parent_surface_key",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_concept", "kpi_key"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_concept",
|
|
"residual_flag",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_fact", "data_type"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_fact",
|
|
"authoritative_concept_key",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_fact", "mapping_method"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_fact", "surface_key"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_fact",
|
|
"detail_parent_surface_key",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_fact", "kpi_key"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_fact", "residual_flag"),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "filing_taxonomy_fact", "precision"),
|
|
).toBe(true);
|
|
expect(__dbInternals.hasColumn(client, "filing_taxonomy_fact", "nil")).toBe(
|
|
true,
|
|
);
|
|
expect(__dbInternals.hasColumn(client, "task_run", "stage_context")).toBe(
|
|
true,
|
|
);
|
|
expect(
|
|
__dbInternals.hasColumn(client, "task_stage_event", "stage_context"),
|
|
).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "research_journal_entry")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "search_document")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "search_chunk")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "research_artifact")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "research_memo")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "research_memo_evidence")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "company_overview_cache")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "issuer_overlay")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "issuer_overlay_revision")).toBe(
|
|
true,
|
|
);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"issuer_overlay_revision_id",
|
|
),
|
|
).toBe(true);
|
|
|
|
__dbInternals.loadSqliteExtensions(client);
|
|
__dbInternals.ensureSearchVirtualTables(client);
|
|
|
|
expect(__dbInternals.hasTable(client, "search_chunk_fts")).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "search_chunk_vec")).toBe(true);
|
|
|
|
client.close();
|
|
});
|
|
|
|
it("backfills legacy taxonomy snapshot sidecar columns and remains idempotent", () => {
|
|
const client = new Database(":memory:");
|
|
client.exec("PRAGMA foreign_keys = ON;");
|
|
|
|
applyMigration(client, "0000_cold_silver_centurion.sql");
|
|
applyMigration(client, "0005_financial_taxonomy_v3.sql");
|
|
|
|
client.exec(`
|
|
INSERT INTO \`filing\` (
|
|
\`ticker\`,
|
|
\`filing_type\`,
|
|
\`filing_date\`,
|
|
\`accession_number\`,
|
|
\`cik\`,
|
|
\`company_name\`,
|
|
\`created_at\`,
|
|
\`updated_at\`
|
|
) VALUES (
|
|
'AAPL',
|
|
'10-K',
|
|
'2024-09-28',
|
|
'0000320193-24-000001',
|
|
'0000320193',
|
|
'Apple Inc.',
|
|
'2024-10-30T00:00:00.000Z',
|
|
'2024-10-30T00:00:00.000Z'
|
|
);
|
|
`);
|
|
|
|
const statementRows =
|
|
'{"income":[{"label":"Revenue","value":1}],"balance":[],"cash_flow":[],"equity":[],"comprehensive_income":[]}';
|
|
|
|
client.exec(`
|
|
INSERT INTO \`filing_taxonomy_snapshot\` (
|
|
\`filing_id\`,
|
|
\`ticker\`,
|
|
\`filing_date\`,
|
|
\`filing_type\`,
|
|
\`parse_status\`,
|
|
\`source\`,
|
|
\`statement_rows\`,
|
|
\`created_at\`,
|
|
\`updated_at\`
|
|
) VALUES (
|
|
1,
|
|
'AAPL',
|
|
'2024-09-28',
|
|
'10-K',
|
|
'success',
|
|
'xbrl_instance',
|
|
'${statementRows}',
|
|
'2024-10-30T00:00:00.000Z',
|
|
'2024-10-30T00:00:00.000Z'
|
|
);
|
|
`);
|
|
|
|
__dbInternals.ensureLocalSqliteSchema(client);
|
|
__dbInternals.ensureLocalSqliteSchema(client);
|
|
|
|
const row = client
|
|
.query(
|
|
`
|
|
SELECT
|
|
\`parser_engine\`,
|
|
\`parser_version\`,
|
|
\`taxonomy_regime\`,
|
|
\`faithful_rows\`,
|
|
\`surface_rows\`,
|
|
\`detail_rows\`,
|
|
\`kpi_rows\`,
|
|
\`computed_definitions\`,
|
|
\`normalization_summary\`
|
|
FROM \`filing_taxonomy_snapshot\`
|
|
WHERE \`filing_id\` = 1
|
|
`,
|
|
)
|
|
.get() as {
|
|
parser_engine: string;
|
|
parser_version: string;
|
|
taxonomy_regime: string;
|
|
faithful_rows: string | null;
|
|
surface_rows: string | null;
|
|
detail_rows: string | null;
|
|
kpi_rows: string | null;
|
|
computed_definitions: string | null;
|
|
normalization_summary: string | null;
|
|
};
|
|
|
|
expect(row.parser_engine).toBe("fiscal-xbrl");
|
|
expect(row.parser_version).toBe("unknown");
|
|
expect(row.taxonomy_regime).toBe("unknown");
|
|
expect(row.faithful_rows).toBe(statementRows);
|
|
expect(row.surface_rows).toBe(
|
|
'{"income":[],"balance":[],"cash_flow":[],"equity":[],"comprehensive_income":[]}',
|
|
);
|
|
expect(row.detail_rows).toBe(
|
|
'{"income":{},"balance":{},"cash_flow":{},"equity":{},"comprehensive_income":{}}',
|
|
);
|
|
expect(row.kpi_rows).toBe("[]");
|
|
expect(row.computed_definitions).toBe("[]");
|
|
expect(row.normalization_summary).toBeNull();
|
|
|
|
client.close();
|
|
});
|
|
|
|
it("repairs partial taxonomy sidecar drift without requiring a table rebuild", () => {
|
|
const client = new Database(":memory:");
|
|
client.exec("PRAGMA foreign_keys = ON;");
|
|
|
|
applyMigration(client, "0000_cold_silver_centurion.sql");
|
|
applyMigration(client, "0005_financial_taxonomy_v3.sql");
|
|
client.exec(
|
|
"ALTER TABLE `filing_taxonomy_snapshot` ADD `parser_engine` text NOT NULL DEFAULT 'legacy-ts';",
|
|
);
|
|
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"parser_engine",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"normalization_summary",
|
|
),
|
|
).toBe(false);
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_context")).toBe(
|
|
false,
|
|
);
|
|
|
|
__dbInternals.ensureLocalSqliteSchema(client);
|
|
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"parser_version",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"taxonomy_regime",
|
|
),
|
|
).toBe(true);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"normalization_summary",
|
|
),
|
|
).toBe(true);
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_context")).toBe(
|
|
true,
|
|
);
|
|
|
|
client.close();
|
|
});
|
|
|
|
it("throws on missing parser_engine column when verifyCriticalSchema is called", () => {
|
|
const client = new Database(":memory:");
|
|
client.exec("PRAGMA foreign_keys = ON;");
|
|
|
|
applyMigration(client, "0000_cold_silver_centurion.sql");
|
|
applyMigration(client, "0005_financial_taxonomy_v3.sql");
|
|
|
|
expect(__dbInternals.hasTable(client, "filing_taxonomy_snapshot")).toBe(
|
|
true,
|
|
);
|
|
expect(
|
|
__dbInternals.hasColumn(
|
|
client,
|
|
"filing_taxonomy_snapshot",
|
|
"parser_engine",
|
|
),
|
|
).toBe(false);
|
|
|
|
expect(() => __dbInternals.verifyCriticalSchema(client)).toThrow(
|
|
/filing_taxonomy_snapshot is missing columns: parser_engine/,
|
|
);
|
|
|
|
client.close();
|
|
});
|
|
|
|
it("verifyCriticalSchema passes when all required columns exist", () => {
|
|
const client = new Database(":memory:");
|
|
client.exec("PRAGMA foreign_keys = ON;");
|
|
|
|
applyMigration(client, "0000_cold_silver_centurion.sql");
|
|
applyMigration(client, "0005_financial_taxonomy_v3.sql");
|
|
|
|
__dbInternals.ensureLocalSqliteSchema(client);
|
|
|
|
expect(() => __dbInternals.verifyCriticalSchema(client)).not.toThrow();
|
|
|
|
client.close();
|
|
});
|
|
});
|