1447 lines
44 KiB
TypeScript
1447 lines
44 KiB
TypeScript
import { sql } from "drizzle-orm";
|
|
import {
|
|
index,
|
|
integer,
|
|
numeric,
|
|
sqliteTable,
|
|
text,
|
|
uniqueIndex,
|
|
} from "drizzle-orm/sqlite-core";
|
|
import type { TaskStageContext } from "@/lib/types";
|
|
|
|
type FilingMetrics = {
|
|
revenue: number | null;
|
|
netIncome: number | null;
|
|
totalAssets: number | null;
|
|
cash: number | null;
|
|
debt: number | null;
|
|
};
|
|
|
|
type TaxonomyAssetType =
|
|
| "instance"
|
|
| "schema"
|
|
| "presentation"
|
|
| "label"
|
|
| "calculation"
|
|
| "definition"
|
|
| "pdf"
|
|
| "other";
|
|
|
|
type TaxonomyParseStatus = "ready" | "partial" | "failed";
|
|
type TaxonomyMetricValidationStatus =
|
|
| "not_run"
|
|
| "matched"
|
|
| "mismatch"
|
|
| "error";
|
|
type IssuerOverlayStatus = "empty" | "active" | "error";
|
|
type CoverageStatus = "backlog" | "active" | "watch" | "archive";
|
|
type CoveragePriority = "low" | "medium" | "high";
|
|
type ResearchJournalEntryType = "note" | "filing_note" | "status_change";
|
|
type ResearchArtifactKind =
|
|
| "filing"
|
|
| "ai_report"
|
|
| "note"
|
|
| "upload"
|
|
| "memo_snapshot"
|
|
| "status_change";
|
|
type ResearchArtifactSource = "system" | "user";
|
|
type ResearchVisibilityScope = "private" | "organization";
|
|
type ResearchMemoRating = "strong_buy" | "buy" | "hold" | "sell";
|
|
type ResearchMemoConviction = "low" | "medium" | "high";
|
|
type ResearchMemoSection =
|
|
| "thesis"
|
|
| "variant_view"
|
|
| "catalysts"
|
|
| "risks"
|
|
| "disconfirming_evidence"
|
|
| "next_actions";
|
|
type FinancialCadence = "annual" | "quarterly" | "ltm";
|
|
type SearchDocumentScope = "global" | "user";
|
|
type SearchDocumentSourceKind =
|
|
| "filing_document"
|
|
| "filing_brief"
|
|
| "research_note";
|
|
type SearchIndexStatus = "pending" | "indexed" | "failed";
|
|
type FinancialSurfaceKind =
|
|
| "income_statement"
|
|
| "balance_sheet"
|
|
| "cash_flow_statement"
|
|
| "equity_statement"
|
|
| "disclosures"
|
|
| "ratios"
|
|
| "segments_kpis"
|
|
| "adjusted"
|
|
| "custom_metrics";
|
|
|
|
type FilingAnalysis = {
|
|
provider?: string;
|
|
model?: string;
|
|
text?: string;
|
|
legacyInsights?: string;
|
|
extraction?: {
|
|
summary: string;
|
|
keyPoints: string[];
|
|
redFlags: string[];
|
|
followUpQuestions: string[];
|
|
portfolioSignals: string[];
|
|
segmentSpecificData: string[];
|
|
geographicRevenueBreakdown: string[];
|
|
companySpecificData: string[];
|
|
secApiCrossChecks: string[];
|
|
confidence: number;
|
|
};
|
|
extractionMeta?: {
|
|
provider: string;
|
|
model: string;
|
|
source: "primary_document" | "metadata_fallback";
|
|
generatedAt: string;
|
|
};
|
|
};
|
|
|
|
type FinancialStatementKind =
|
|
| "income"
|
|
| "balance"
|
|
| "cash_flow"
|
|
| "disclosure"
|
|
| "equity"
|
|
| "comprehensive_income";
|
|
|
|
export type IssuerOverlayDefinition = {
|
|
version: "fiscal-v1";
|
|
ticker: string;
|
|
pack: string | null;
|
|
mappings: Array<{
|
|
surface_key: string;
|
|
statement: FinancialStatementKind;
|
|
allowed_source_concepts: string[];
|
|
allowed_authoritative_concepts: string[];
|
|
}>;
|
|
};
|
|
|
|
export type IssuerOverlayStats = {
|
|
pack: string | null;
|
|
sampledSnapshotCount: number;
|
|
sampledSnapshotIds: number[];
|
|
acceptedMappingCount: number;
|
|
rejectedMappingCount: number;
|
|
publishedRevisionNumber: number | null;
|
|
};
|
|
|
|
export type IssuerOverlayDiagnostics = {
|
|
pack: string | null;
|
|
sampledSnapshotIds: number[];
|
|
acceptedMappings: Array<{
|
|
qname: string;
|
|
surface_key: string;
|
|
statement: FinancialStatementKind;
|
|
reason: "authoritative_match" | "local_name_match";
|
|
source_snapshot_ids: number[];
|
|
}>;
|
|
rejectedMappings: Array<{
|
|
qname: string;
|
|
reason: string;
|
|
source_snapshot_ids: number[];
|
|
}>;
|
|
};
|
|
|
|
type FilingStatementPeriod = {
|
|
id: string;
|
|
filingId: number;
|
|
accessionNumber: string;
|
|
filingDate: string;
|
|
periodStart: string | null;
|
|
periodEnd: string | null;
|
|
filingType: "10-K" | "10-Q";
|
|
periodLabel: string;
|
|
};
|
|
|
|
type StatementValuesByPeriod = Record<string, number | null>;
|
|
|
|
type FilingFaithfulStatementSnapshotRow = {
|
|
key: string;
|
|
label: string;
|
|
concept: string | null;
|
|
order: number;
|
|
depth: number;
|
|
isSubtotal: boolean;
|
|
values: StatementValuesByPeriod;
|
|
};
|
|
|
|
type StandardizedStatementSnapshotRow = {
|
|
key: string;
|
|
label: string;
|
|
concept: string;
|
|
category: string;
|
|
sourceConcepts: string[];
|
|
values: StatementValuesByPeriod;
|
|
};
|
|
|
|
type DimensionStatementSnapshotRow = {
|
|
rowKey: string;
|
|
concept: string | null;
|
|
periodId: string;
|
|
axis: string;
|
|
member: string;
|
|
value: number | null;
|
|
unit: string | null;
|
|
};
|
|
|
|
type FilingStatementBundle = {
|
|
periods: FilingStatementPeriod[];
|
|
statements: Record<
|
|
FinancialStatementKind,
|
|
FilingFaithfulStatementSnapshotRow[]
|
|
>;
|
|
};
|
|
|
|
type StandardizedStatementBundle = {
|
|
periods: FilingStatementPeriod[];
|
|
statements: Record<
|
|
FinancialStatementKind,
|
|
StandardizedStatementSnapshotRow[]
|
|
>;
|
|
};
|
|
|
|
type DimensionStatementBundle = {
|
|
statements: Record<FinancialStatementKind, DimensionStatementSnapshotRow[]>;
|
|
};
|
|
|
|
type TaxonomyDimensionMember = {
|
|
axis: string;
|
|
member: string;
|
|
};
|
|
|
|
type TaxonomyStatementSnapshotRow = {
|
|
key: string;
|
|
label: string;
|
|
conceptKey: string;
|
|
qname: string;
|
|
namespaceUri: string;
|
|
localName: string;
|
|
isExtension: boolean;
|
|
statement: FinancialStatementKind;
|
|
roleUri: string | null;
|
|
order: number;
|
|
depth: number;
|
|
parentKey: string | null;
|
|
values: Record<string, number | null>;
|
|
units: Record<string, string | null>;
|
|
hasDimensions: boolean;
|
|
sourceFactIds: number[];
|
|
};
|
|
|
|
type TaxonomyStatementBundle = {
|
|
periods: FilingStatementPeriod[];
|
|
statements: Record<FinancialStatementKind, TaxonomyStatementSnapshotRow[]>;
|
|
};
|
|
|
|
type TaxonomySurfaceSnapshotRow = {
|
|
key: string;
|
|
label: string;
|
|
category: string;
|
|
templateSection?: string;
|
|
order: number;
|
|
unit: "currency" | "count" | "shares" | "percent" | "ratio";
|
|
values: Record<string, number | null>;
|
|
sourceConcepts: string[];
|
|
sourceRowKeys: string[];
|
|
sourceFactIds: number[];
|
|
formulaKey: string | null;
|
|
hasDimensions: boolean;
|
|
resolvedSourceRowKeys: Record<string, string | null>;
|
|
statement?: "income" | "balance" | "cash_flow" | "equity" | "disclosure";
|
|
detailCount?: number;
|
|
};
|
|
|
|
type TaxonomyDetailSnapshotRow = {
|
|
key: string;
|
|
parentSurfaceKey: string;
|
|
label: string;
|
|
conceptKey: string;
|
|
qname: string;
|
|
namespaceUri: string;
|
|
localName: string;
|
|
unit: string | null;
|
|
values: Record<string, number | null>;
|
|
sourceFactIds: number[];
|
|
isExtension: boolean;
|
|
dimensionsSummary: string[];
|
|
residualFlag: boolean;
|
|
};
|
|
|
|
type TaxonomyDetailStatementMap = Record<string, TaxonomyDetailSnapshotRow[]>;
|
|
|
|
type StructuredKpiSnapshotRow = {
|
|
key: string;
|
|
label: string;
|
|
category: string;
|
|
unit: "currency" | "count" | "shares" | "percent" | "ratio";
|
|
order: number;
|
|
segment: string | null;
|
|
axis: string | null;
|
|
member: string | null;
|
|
values: Record<string, number | null>;
|
|
sourceConcepts: string[];
|
|
sourceFactIds: number[];
|
|
provenanceType: "taxonomy" | "structured_note";
|
|
hasDimensions: boolean;
|
|
};
|
|
|
|
type ComputationSpecSnapshot =
|
|
| { type: "ratio"; numerator: string; denominator: string }
|
|
| { type: "yoy_growth"; source: string }
|
|
| { type: "cagr"; source: string; years: number }
|
|
| { type: "per_share"; source: string; shares_key: string }
|
|
| { type: "simple"; formula: string };
|
|
|
|
type ComputedDefinitionSnapshotRow = {
|
|
key: string;
|
|
label: string;
|
|
category: string;
|
|
order: number;
|
|
unit: "currency" | "count" | "shares" | "percent" | "ratio";
|
|
computation: ComputationSpecSnapshot;
|
|
supported_cadences?: FinancialCadence[];
|
|
requires_external_data?: string[];
|
|
};
|
|
|
|
type TaxonomyNormalizationSummary = {
|
|
surfaceRowCount: number;
|
|
detailRowCount: number;
|
|
kpiRowCount: number;
|
|
unmappedRowCount: number;
|
|
materialUnmappedRowCount: number;
|
|
residualPrimaryCount: number;
|
|
residualDisclosureCount: number;
|
|
unsupportedConceptCount: number;
|
|
issuerOverlayMatchCount: number;
|
|
warnings: string[];
|
|
};
|
|
|
|
type TaxonomyMetricValidationCheck = {
|
|
metricKey: keyof FilingMetrics;
|
|
taxonomyValue: number | null;
|
|
llmValue: number | null;
|
|
absoluteDiff: number | null;
|
|
relativeDiff: number | null;
|
|
status: TaxonomyMetricValidationStatus;
|
|
evidencePages: number[];
|
|
pdfUrl: string | null;
|
|
provider: string | null;
|
|
model: string | null;
|
|
error: string | null;
|
|
};
|
|
|
|
type TaxonomyMetricValidationResult = {
|
|
status: TaxonomyMetricValidationStatus;
|
|
checks: TaxonomyMetricValidationCheck[];
|
|
validatedAt: string | null;
|
|
};
|
|
|
|
const authDateColumn = {
|
|
mode: "timestamp_ms",
|
|
} as const;
|
|
|
|
export const user = sqliteTable(
|
|
"user",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
name: text("name").notNull(),
|
|
email: text("email").notNull(),
|
|
emailVerified: integer("emailVerified", { mode: "boolean" })
|
|
.notNull()
|
|
.default(false),
|
|
image: text("image"),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
updatedAt: integer("updatedAt", authDateColumn).notNull(),
|
|
role: text("role"),
|
|
banned: integer("banned", { mode: "boolean" }).default(false),
|
|
banReason: text("banReason"),
|
|
banExpires: integer("banExpires", authDateColumn),
|
|
},
|
|
(table) => ({
|
|
userEmailUnique: uniqueIndex("user_email_uidx").on(table.email),
|
|
}),
|
|
);
|
|
|
|
export const organization = sqliteTable(
|
|
"organization",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
name: text("name").notNull(),
|
|
slug: text("slug").notNull(),
|
|
logo: text("logo"),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
metadata: text("metadata"),
|
|
},
|
|
(table) => ({
|
|
organizationSlugUnique: uniqueIndex("organization_slug_uidx").on(
|
|
table.slug,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const session = sqliteTable(
|
|
"session",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
expiresAt: integer("expiresAt", authDateColumn).notNull(),
|
|
token: text("token").notNull(),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
updatedAt: integer("updatedAt", authDateColumn).notNull(),
|
|
ipAddress: text("ipAddress"),
|
|
userAgent: text("userAgent"),
|
|
userId: text("userId")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
impersonatedBy: text("impersonatedBy"),
|
|
activeOrganizationId: text("activeOrganizationId"),
|
|
},
|
|
(table) => ({
|
|
sessionTokenUnique: uniqueIndex("session_token_uidx").on(table.token),
|
|
sessionUserIdIndex: index("session_userId_idx").on(table.userId),
|
|
}),
|
|
);
|
|
|
|
export const account = sqliteTable(
|
|
"account",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
accountId: text("accountId").notNull(),
|
|
providerId: text("providerId").notNull(),
|
|
userId: text("userId")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
accessToken: text("accessToken"),
|
|
refreshToken: text("refreshToken"),
|
|
idToken: text("idToken"),
|
|
accessTokenExpiresAt: integer("accessTokenExpiresAt", authDateColumn),
|
|
refreshTokenExpiresAt: integer("refreshTokenExpiresAt", authDateColumn),
|
|
scope: text("scope"),
|
|
password: text("password"),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
updatedAt: integer("updatedAt", authDateColumn).notNull(),
|
|
},
|
|
(table) => ({
|
|
accountUserIdIndex: index("account_userId_idx").on(table.userId),
|
|
}),
|
|
);
|
|
|
|
export const verification = sqliteTable(
|
|
"verification",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
identifier: text("identifier").notNull(),
|
|
value: text("value").notNull(),
|
|
expiresAt: integer("expiresAt", authDateColumn).notNull(),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
updatedAt: integer("updatedAt", authDateColumn).notNull(),
|
|
},
|
|
(table) => ({
|
|
verificationIdentifierIndex: index("verification_identifier_idx").on(
|
|
table.identifier,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const member = sqliteTable(
|
|
"member",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
organizationId: text("organizationId")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
userId: text("userId")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
role: text("role").notNull().default("member"),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
},
|
|
(table) => ({
|
|
memberOrganizationIdIndex: index("member_organizationId_idx").on(
|
|
table.organizationId,
|
|
),
|
|
memberUserIdIndex: index("member_userId_idx").on(table.userId),
|
|
}),
|
|
);
|
|
|
|
export const invitation = sqliteTable(
|
|
"invitation",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
organizationId: text("organizationId")
|
|
.notNull()
|
|
.references(() => organization.id, { onDelete: "cascade" }),
|
|
email: text("email").notNull(),
|
|
role: text("role"),
|
|
status: text("status").notNull().default("pending"),
|
|
expiresAt: integer("expiresAt", authDateColumn).notNull(),
|
|
createdAt: integer("createdAt", authDateColumn).notNull(),
|
|
inviterId: text("inviterId")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
},
|
|
(table) => ({
|
|
invitationOrganizationIdIndex: index("invitation_organizationId_idx").on(
|
|
table.organizationId,
|
|
),
|
|
invitationEmailIndex: index("invitation_email_idx").on(table.email),
|
|
}),
|
|
);
|
|
|
|
export const watchlistItem = sqliteTable(
|
|
"watchlist_item",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker").notNull(),
|
|
company_name: text("company_name").notNull(),
|
|
sector: text("sector"),
|
|
category: text("category"),
|
|
tags: text("tags", { mode: "json" }).$type<string[]>(),
|
|
status: text("status").$type<CoverageStatus>().notNull().default("backlog"),
|
|
priority: text("priority")
|
|
.$type<CoveragePriority>()
|
|
.notNull()
|
|
.default("medium"),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
last_reviewed_at: text("last_reviewed_at"),
|
|
},
|
|
(table) => ({
|
|
watchlistUserTickerUnique: uniqueIndex("watchlist_user_ticker_uidx").on(
|
|
table.user_id,
|
|
table.ticker,
|
|
),
|
|
watchlistUserCreatedIndex: index("watchlist_user_created_idx").on(
|
|
table.user_id,
|
|
table.created_at,
|
|
),
|
|
watchlistUserUpdatedIndex: index("watchlist_user_updated_idx").on(
|
|
table.user_id,
|
|
table.updated_at,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const holding = sqliteTable(
|
|
"holding",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker").notNull(),
|
|
company_name: text("company_name"),
|
|
shares: numeric("shares").notNull(),
|
|
avg_cost: numeric("avg_cost").notNull(),
|
|
current_price: numeric("current_price"),
|
|
market_value: numeric("market_value").notNull(),
|
|
gain_loss: numeric("gain_loss").notNull(),
|
|
gain_loss_pct: numeric("gain_loss_pct").notNull(),
|
|
last_price_at: text("last_price_at"),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
holdingUserTickerUnique: uniqueIndex("holding_user_ticker_uidx").on(
|
|
table.user_id,
|
|
table.ticker,
|
|
),
|
|
holdingUserIndex: index("holding_user_idx").on(table.user_id),
|
|
}),
|
|
);
|
|
|
|
export const filing = sqliteTable(
|
|
"filing",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
ticker: text("ticker").notNull(),
|
|
filing_type: text("filing_type").$type<"10-K" | "10-Q" | "8-K">().notNull(),
|
|
filing_date: text("filing_date").notNull(),
|
|
accession_number: text("accession_number").notNull(),
|
|
cik: text("cik").notNull(),
|
|
company_name: text("company_name").notNull(),
|
|
filing_url: text("filing_url"),
|
|
submission_url: text("submission_url"),
|
|
primary_document: text("primary_document"),
|
|
metrics: text("metrics", { mode: "json" }).$type<FilingMetrics | null>(),
|
|
analysis: text("analysis", { mode: "json" }).$type<FilingAnalysis | null>(),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingAccessionUnique: uniqueIndex("filing_accession_uidx").on(
|
|
table.accession_number,
|
|
),
|
|
filingTickerDateIndex: index("filing_ticker_date_idx").on(
|
|
table.ticker,
|
|
table.filing_date,
|
|
),
|
|
filingDateIndex: index("filing_date_idx").on(table.filing_date),
|
|
}),
|
|
);
|
|
|
|
export const filingStatementSnapshot = sqliteTable(
|
|
"filing_statement_snapshot",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
filing_id: integer("filing_id")
|
|
.notNull()
|
|
.references(() => filing.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker").notNull(),
|
|
filing_date: text("filing_date").notNull(),
|
|
filing_type: text("filing_type").$type<"10-K" | "10-Q">().notNull(),
|
|
period_end: text("period_end"),
|
|
statement_bundle: text("statement_bundle", {
|
|
mode: "json",
|
|
}).$type<FilingStatementBundle | null>(),
|
|
standardized_bundle: text("standardized_bundle", {
|
|
mode: "json",
|
|
}).$type<StandardizedStatementBundle | null>(),
|
|
dimension_bundle: text("dimension_bundle", {
|
|
mode: "json",
|
|
}).$type<DimensionStatementBundle | null>(),
|
|
parse_status: text("parse_status")
|
|
.$type<"ready" | "partial" | "failed">()
|
|
.notNull(),
|
|
parse_error: text("parse_error"),
|
|
source: text("source")
|
|
.$type<"sec_filing_summary" | "xbrl_instance" | "companyfacts_fallback">()
|
|
.notNull(),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingStatementFilingUnique: uniqueIndex("filing_stmt_filing_uidx").on(
|
|
table.filing_id,
|
|
),
|
|
filingStatementTickerDateIndex: index("filing_stmt_ticker_date_idx").on(
|
|
table.ticker,
|
|
table.filing_date,
|
|
),
|
|
filingStatementDateIndex: index("filing_stmt_date_idx").on(
|
|
table.filing_date,
|
|
),
|
|
filingStatementStatusIndex: index("filing_stmt_status_idx").on(
|
|
table.parse_status,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const filingTaxonomySnapshot = sqliteTable(
|
|
"filing_taxonomy_snapshot",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
filing_id: integer("filing_id")
|
|
.notNull()
|
|
.references(() => filing.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker").notNull(),
|
|
filing_date: text("filing_date").notNull(),
|
|
filing_type: text("filing_type").$type<"10-K" | "10-Q">().notNull(),
|
|
parse_status: text("parse_status").$type<TaxonomyParseStatus>().notNull(),
|
|
parse_error: text("parse_error"),
|
|
source: text("source")
|
|
.$type<
|
|
"xbrl_instance" | "xbrl_instance_with_linkbase" | "legacy_html_fallback"
|
|
>()
|
|
.notNull(),
|
|
parser_engine: text("parser_engine").notNull().default("fiscal-xbrl"),
|
|
parser_version: text("parser_version").notNull().default("unknown"),
|
|
taxonomy_regime: text("taxonomy_regime")
|
|
.$type<"us-gaap" | "ifrs-full" | "unknown">()
|
|
.notNull()
|
|
.default("unknown"),
|
|
fiscal_pack: text("fiscal_pack"),
|
|
periods: text("periods", { mode: "json" }).$type<FilingStatementPeriod[]>(),
|
|
faithful_rows: text("faithful_rows", { mode: "json" }).$type<
|
|
TaxonomyStatementBundle["statements"] | null
|
|
>(),
|
|
statement_rows: text("statement_rows", { mode: "json" }).$type<
|
|
TaxonomyStatementBundle["statements"] | null
|
|
>(),
|
|
surface_rows: text("surface_rows", { mode: "json" }).$type<Record<
|
|
FinancialStatementKind,
|
|
TaxonomySurfaceSnapshotRow[]
|
|
> | null>(),
|
|
detail_rows: text("detail_rows", { mode: "json" }).$type<Record<
|
|
FinancialStatementKind,
|
|
TaxonomyDetailStatementMap
|
|
> | null>(),
|
|
kpi_rows: text("kpi_rows", { mode: "json" }).$type<
|
|
StructuredKpiSnapshotRow[] | null
|
|
>(),
|
|
computed_definitions: text("computed_definitions", { mode: "json" }).$type<
|
|
ComputedDefinitionSnapshotRow[] | null
|
|
>(),
|
|
derived_metrics: text("derived_metrics", {
|
|
mode: "json",
|
|
}).$type<FilingMetrics | null>(),
|
|
validation_result: text("validation_result", {
|
|
mode: "json",
|
|
}).$type<TaxonomyMetricValidationResult | null>(),
|
|
normalization_summary: text("normalization_summary", {
|
|
mode: "json",
|
|
}).$type<TaxonomyNormalizationSummary | null>(),
|
|
issuer_overlay_revision_id: integer("issuer_overlay_revision_id"),
|
|
facts_count: integer("facts_count").notNull().default(0),
|
|
concepts_count: integer("concepts_count").notNull().default(0),
|
|
dimensions_count: integer("dimensions_count").notNull().default(0),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingTaxonomySnapshotFilingUnique: uniqueIndex(
|
|
"filing_taxonomy_snapshot_filing_uidx",
|
|
).on(table.filing_id),
|
|
filingTaxonomySnapshotTickerDateIndex: index(
|
|
"filing_taxonomy_snapshot_ticker_date_idx",
|
|
).on(table.ticker, table.filing_date),
|
|
filingTaxonomySnapshotStatusIndex: index(
|
|
"filing_taxonomy_snapshot_status_idx",
|
|
).on(table.parse_status),
|
|
}),
|
|
);
|
|
|
|
export const issuerOverlayRevision = sqliteTable(
|
|
"issuer_overlay_revision",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
ticker: text("ticker").notNull(),
|
|
revision_number: integer("revision_number").notNull(),
|
|
definition_hash: text("definition_hash").notNull(),
|
|
definition_json: text("definition_json", {
|
|
mode: "json",
|
|
}).$type<IssuerOverlayDefinition>(),
|
|
diagnostics_json: text("diagnostics_json", {
|
|
mode: "json",
|
|
}).$type<IssuerOverlayDiagnostics | null>(),
|
|
source_snapshot_ids: text("source_snapshot_ids", {
|
|
mode: "json",
|
|
}).$type<number[]>(),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
issuerOverlayRevisionTickerRevisionUnique: uniqueIndex(
|
|
"issuer_overlay_revision_ticker_revision_uidx",
|
|
).on(table.ticker, table.revision_number),
|
|
issuerOverlayRevisionTickerHashUnique: uniqueIndex(
|
|
"issuer_overlay_revision_ticker_hash_uidx",
|
|
).on(table.ticker, table.definition_hash),
|
|
issuerOverlayRevisionTickerCreatedIndex: index(
|
|
"issuer_overlay_revision_ticker_created_idx",
|
|
).on(table.ticker, table.created_at),
|
|
}),
|
|
);
|
|
|
|
export const issuerOverlay = sqliteTable(
|
|
"issuer_overlay",
|
|
{
|
|
ticker: text("ticker").primaryKey(),
|
|
status: text("status")
|
|
.$type<IssuerOverlayStatus>()
|
|
.notNull()
|
|
.default("empty"),
|
|
active_revision_id: integer("active_revision_id").references(
|
|
() => issuerOverlayRevision.id,
|
|
{ onDelete: "set null" },
|
|
),
|
|
last_built_at: text("last_built_at"),
|
|
last_error: text("last_error"),
|
|
stats_json: text("stats_json", {
|
|
mode: "json",
|
|
}).$type<IssuerOverlayStats | null>(),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
issuerOverlayStatusIndex: index("issuer_overlay_status_idx").on(
|
|
table.status,
|
|
table.updated_at,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const filingTaxonomyContext = sqliteTable(
|
|
"filing_taxonomy_context",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
snapshot_id: integer("snapshot_id")
|
|
.notNull()
|
|
.references(() => filingTaxonomySnapshot.id, { onDelete: "cascade" }),
|
|
context_id: text("context_id").notNull(),
|
|
entity_identifier: text("entity_identifier"),
|
|
entity_scheme: text("entity_scheme"),
|
|
period_start: text("period_start"),
|
|
period_end: text("period_end"),
|
|
period_instant: text("period_instant"),
|
|
segment_json: text("segment_json", { mode: "json" }).$type<Record<
|
|
string,
|
|
unknown
|
|
> | null>(),
|
|
scenario_json: text("scenario_json", { mode: "json" }).$type<Record<
|
|
string,
|
|
unknown
|
|
> | null>(),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingTaxonomyContextSnapshotIndex: index(
|
|
"filing_taxonomy_context_snapshot_idx",
|
|
).on(table.snapshot_id),
|
|
filingTaxonomyContextUnique: uniqueIndex("filing_taxonomy_context_uidx").on(
|
|
table.snapshot_id,
|
|
table.context_id,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const filingTaxonomyAsset = sqliteTable(
|
|
"filing_taxonomy_asset",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
snapshot_id: integer("snapshot_id")
|
|
.notNull()
|
|
.references(() => filingTaxonomySnapshot.id, { onDelete: "cascade" }),
|
|
asset_type: text("asset_type").$type<TaxonomyAssetType>().notNull(),
|
|
name: text("name").notNull(),
|
|
url: text("url").notNull(),
|
|
size_bytes: integer("size_bytes"),
|
|
score: numeric("score"),
|
|
is_selected: integer("is_selected", { mode: "boolean" })
|
|
.notNull()
|
|
.default(false),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingTaxonomyAssetSnapshotIndex: index(
|
|
"filing_taxonomy_asset_snapshot_idx",
|
|
).on(table.snapshot_id),
|
|
filingTaxonomyAssetTypeIndex: index("filing_taxonomy_asset_type_idx").on(
|
|
table.snapshot_id,
|
|
table.asset_type,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const filingTaxonomyConcept = sqliteTable(
|
|
"filing_taxonomy_concept",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
snapshot_id: integer("snapshot_id")
|
|
.notNull()
|
|
.references(() => filingTaxonomySnapshot.id, { onDelete: "cascade" }),
|
|
concept_key: text("concept_key").notNull(),
|
|
qname: text("qname").notNull(),
|
|
namespace_uri: text("namespace_uri").notNull(),
|
|
local_name: text("local_name").notNull(),
|
|
label: text("label"),
|
|
is_extension: integer("is_extension", { mode: "boolean" })
|
|
.notNull()
|
|
.default(false),
|
|
balance: text("balance"),
|
|
period_type: text("period_type"),
|
|
data_type: text("data_type"),
|
|
statement_kind: text("statement_kind").$type<FinancialStatementKind>(),
|
|
role_uri: text("role_uri"),
|
|
authoritative_concept_key: text("authoritative_concept_key"),
|
|
mapping_method: text("mapping_method"),
|
|
surface_key: text("surface_key"),
|
|
detail_parent_surface_key: text("detail_parent_surface_key"),
|
|
kpi_key: text("kpi_key"),
|
|
residual_flag: integer("residual_flag", { mode: "boolean" })
|
|
.notNull()
|
|
.default(false),
|
|
presentation_order: numeric("presentation_order"),
|
|
presentation_depth: integer("presentation_depth"),
|
|
parent_concept_key: text("parent_concept_key"),
|
|
is_abstract: integer("is_abstract", { mode: "boolean" })
|
|
.notNull()
|
|
.default(false),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingTaxonomyConceptSnapshotIndex: index(
|
|
"filing_taxonomy_concept_snapshot_idx",
|
|
).on(table.snapshot_id),
|
|
filingTaxonomyConceptStatementIndex: index(
|
|
"filing_taxonomy_concept_statement_idx",
|
|
).on(table.snapshot_id, table.statement_kind),
|
|
filingTaxonomyConceptUnique: uniqueIndex("filing_taxonomy_concept_uidx").on(
|
|
table.snapshot_id,
|
|
table.concept_key,
|
|
table.role_uri,
|
|
table.presentation_order,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const filingTaxonomyFact = sqliteTable(
|
|
"filing_taxonomy_fact",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
snapshot_id: integer("snapshot_id")
|
|
.notNull()
|
|
.references(() => filingTaxonomySnapshot.id, { onDelete: "cascade" }),
|
|
concept_key: text("concept_key").notNull(),
|
|
qname: text("qname").notNull(),
|
|
namespace_uri: text("namespace_uri").notNull(),
|
|
local_name: text("local_name").notNull(),
|
|
data_type: text("data_type"),
|
|
statement_kind: text("statement_kind").$type<FinancialStatementKind>(),
|
|
role_uri: text("role_uri"),
|
|
authoritative_concept_key: text("authoritative_concept_key"),
|
|
mapping_method: text("mapping_method"),
|
|
surface_key: text("surface_key"),
|
|
detail_parent_surface_key: text("detail_parent_surface_key"),
|
|
kpi_key: text("kpi_key"),
|
|
residual_flag: integer("residual_flag", { mode: "boolean" })
|
|
.notNull()
|
|
.default(false),
|
|
context_id: text("context_id").notNull(),
|
|
unit: text("unit"),
|
|
decimals: text("decimals"),
|
|
precision: text("precision"),
|
|
nil: integer("nil", { mode: "boolean" }).notNull().default(false),
|
|
value_num: numeric("value_num").notNull(),
|
|
period_start: text("period_start"),
|
|
period_end: text("period_end"),
|
|
period_instant: text("period_instant"),
|
|
dimensions: text("dimensions", { mode: "json" })
|
|
.$type<TaxonomyDimensionMember[]>()
|
|
.notNull(),
|
|
is_dimensionless: integer("is_dimensionless", { mode: "boolean" })
|
|
.notNull()
|
|
.default(true),
|
|
source_file: text("source_file"),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingTaxonomyFactSnapshotIndex: index(
|
|
"filing_taxonomy_fact_snapshot_idx",
|
|
).on(table.snapshot_id),
|
|
filingTaxonomyFactConceptIndex: index(
|
|
"filing_taxonomy_fact_concept_idx",
|
|
).on(table.snapshot_id, table.concept_key),
|
|
filingTaxonomyFactPeriodIndex: index("filing_taxonomy_fact_period_idx").on(
|
|
table.snapshot_id,
|
|
table.period_end,
|
|
table.period_instant,
|
|
),
|
|
filingTaxonomyFactStatementIndex: index(
|
|
"filing_taxonomy_fact_statement_idx",
|
|
).on(table.snapshot_id, table.statement_kind),
|
|
}),
|
|
);
|
|
|
|
export const filingTaxonomyMetricValidation = sqliteTable(
|
|
"filing_taxonomy_metric_validation",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
snapshot_id: integer("snapshot_id")
|
|
.notNull()
|
|
.references(() => filingTaxonomySnapshot.id, { onDelete: "cascade" }),
|
|
metric_key: text("metric_key").$type<keyof FilingMetrics>().notNull(),
|
|
taxonomy_value: numeric("taxonomy_value"),
|
|
llm_value: numeric("llm_value"),
|
|
absolute_diff: numeric("absolute_diff"),
|
|
relative_diff: numeric("relative_diff"),
|
|
status: text("status").$type<TaxonomyMetricValidationStatus>().notNull(),
|
|
evidence_pages: text("evidence_pages", { mode: "json" })
|
|
.$type<number[]>()
|
|
.notNull(),
|
|
pdf_url: text("pdf_url"),
|
|
provider: text("provider"),
|
|
model: text("model"),
|
|
error: text("error"),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingTaxonomyMetricValidationSnapshotIndex: index(
|
|
"filing_taxonomy_metric_validation_snapshot_idx",
|
|
).on(table.snapshot_id),
|
|
filingTaxonomyMetricValidationStatusIndex: index(
|
|
"filing_taxonomy_metric_validation_status_idx",
|
|
).on(table.snapshot_id, table.status),
|
|
filingTaxonomyMetricValidationUnique: uniqueIndex(
|
|
"filing_taxonomy_metric_validation_uidx",
|
|
).on(table.snapshot_id, table.metric_key),
|
|
}),
|
|
);
|
|
|
|
export const companyFinancialBundle = sqliteTable(
|
|
"company_financial_bundle",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
ticker: text("ticker").notNull(),
|
|
surface_kind: text("surface_kind").$type<FinancialSurfaceKind>().notNull(),
|
|
cadence: text("cadence").$type<FinancialCadence>().notNull(),
|
|
bundle_version: integer("bundle_version").notNull(),
|
|
source_snapshot_ids: text("source_snapshot_ids", { mode: "json" })
|
|
.$type<number[]>()
|
|
.notNull(),
|
|
source_signature: text("source_signature").notNull(),
|
|
payload: text("payload", { mode: "json" })
|
|
.$type<Record<string, unknown>>()
|
|
.notNull(),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
companyFinancialBundleUnique: uniqueIndex(
|
|
"company_financial_bundle_uidx",
|
|
).on(table.ticker, table.surface_kind, table.cadence),
|
|
companyFinancialBundleTickerIndex: index(
|
|
"company_financial_bundle_ticker_idx",
|
|
).on(table.ticker, table.updated_at),
|
|
}),
|
|
);
|
|
|
|
export const companyOverviewCache = sqliteTable(
|
|
"company_overview_cache",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker").notNull(),
|
|
cache_version: integer("cache_version").notNull(),
|
|
source_signature: text("source_signature").notNull(),
|
|
payload: text("payload", { mode: "json" })
|
|
.$type<Record<string, unknown>>()
|
|
.notNull(),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
companyOverviewCacheUnique: uniqueIndex("company_overview_cache_uidx").on(
|
|
table.user_id,
|
|
table.ticker,
|
|
),
|
|
companyOverviewCacheLookupIndex: index(
|
|
"company_overview_cache_lookup_idx",
|
|
).on(table.user_id, table.ticker, table.updated_at),
|
|
}),
|
|
);
|
|
|
|
export const filingLink = sqliteTable(
|
|
"filing_link",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
filing_id: integer("filing_id")
|
|
.notNull()
|
|
.references(() => filing.id, { onDelete: "cascade" }),
|
|
link_type: text("link_type").notNull(),
|
|
url: text("url").notNull(),
|
|
source: text("source").notNull().default("sec"),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
filingLinkUnique: uniqueIndex("filing_link_unique_uidx").on(
|
|
table.filing_id,
|
|
table.url,
|
|
),
|
|
filingLinkFilingIndex: index("filing_link_filing_idx").on(table.filing_id),
|
|
}),
|
|
);
|
|
|
|
export const taskRun = sqliteTable(
|
|
"task_run",
|
|
{
|
|
id: text("id").primaryKey().notNull(),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
task_type: text("task_type")
|
|
.$type<
|
|
| "sync_filings"
|
|
| "refresh_prices"
|
|
| "analyze_filing"
|
|
| "portfolio_insights"
|
|
| "index_search"
|
|
>()
|
|
.notNull(),
|
|
status: text("status")
|
|
.$type<"queued" | "running" | "completed" | "failed">()
|
|
.notNull(),
|
|
stage: text("stage").notNull(),
|
|
stage_detail: text("stage_detail"),
|
|
stage_context: text("stage_context", {
|
|
mode: "json",
|
|
}).$type<TaskStageContext | null>(),
|
|
resource_key: text("resource_key"),
|
|
notification_read_at: text("notification_read_at"),
|
|
notification_silenced_at: text("notification_silenced_at"),
|
|
priority: integer("priority").notNull(),
|
|
payload: text("payload", { mode: "json" })
|
|
.$type<Record<string, unknown>>()
|
|
.notNull(),
|
|
result: text("result", { mode: "json" }).$type<Record<
|
|
string,
|
|
unknown
|
|
> | null>(),
|
|
error: text("error"),
|
|
attempts: integer("attempts").notNull(),
|
|
max_attempts: integer("max_attempts").notNull(),
|
|
workflow_run_id: text("workflow_run_id"),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
finished_at: text("finished_at"),
|
|
},
|
|
(table) => ({
|
|
taskUserCreatedIndex: index("task_user_created_idx").on(
|
|
table.user_id,
|
|
table.created_at,
|
|
),
|
|
taskUserUpdatedIndex: index("task_user_updated_idx").on(
|
|
table.user_id,
|
|
table.updated_at,
|
|
),
|
|
taskStatusIndex: index("task_status_idx").on(table.status),
|
|
taskUserResourceStatusIndex: index("task_user_resource_status_idx").on(
|
|
table.user_id,
|
|
table.task_type,
|
|
table.resource_key,
|
|
table.status,
|
|
table.created_at,
|
|
),
|
|
taskWorkflowRunUnique: uniqueIndex("task_workflow_run_uidx").on(
|
|
table.workflow_run_id,
|
|
),
|
|
}),
|
|
);
|
|
|
|
// Note: Partial unique index for active resource-scoped task deduplication is created via
|
|
// migration 0013_task_active_resource_unique.sql. SQLite does not support partial indexes
|
|
// in drizzle schema DSL, so the index is managed separately:
|
|
// CREATE UNIQUE INDEX task_active_resource_uidx ON task_run (user_id, task_type, resource_key)
|
|
// WHERE resource_key IS NOT NULL AND status IN ('queued', 'running');
|
|
|
|
export const taskStageEvent = sqliteTable(
|
|
"task_stage_event",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
task_id: text("task_id")
|
|
.notNull()
|
|
.references(() => taskRun.id, { onDelete: "cascade" }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
stage: text("stage").notNull(),
|
|
stage_detail: text("stage_detail"),
|
|
stage_context: text("stage_context", {
|
|
mode: "json",
|
|
}).$type<TaskStageContext | null>(),
|
|
status: text("status")
|
|
.$type<"queued" | "running" | "completed" | "failed">()
|
|
.notNull(),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
taskStageEventTaskCreatedIndex: index(
|
|
"task_stage_event_task_created_idx",
|
|
).on(table.task_id, table.created_at),
|
|
taskStageEventUserCreatedIndex: index(
|
|
"task_stage_event_user_created_idx",
|
|
).on(table.user_id, table.created_at),
|
|
}),
|
|
);
|
|
|
|
export const portfolioInsight = sqliteTable(
|
|
"portfolio_insight",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
provider: text("provider").notNull(),
|
|
model: text("model").notNull(),
|
|
content: text("content").notNull(),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
insightUserCreatedIndex: index("insight_user_created_idx").on(
|
|
table.user_id,
|
|
table.created_at,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const researchJournalEntry = sqliteTable(
|
|
"research_journal_entry",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker").notNull(),
|
|
accession_number: text("accession_number"),
|
|
entry_type: text("entry_type").$type<ResearchJournalEntryType>().notNull(),
|
|
title: text("title"),
|
|
body_markdown: text("body_markdown").notNull(),
|
|
metadata: text("metadata", { mode: "json" }).$type<Record<
|
|
string,
|
|
unknown
|
|
> | null>(),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
researchJournalTickerIndex: index("research_journal_ticker_idx").on(
|
|
table.user_id,
|
|
table.ticker,
|
|
table.created_at,
|
|
),
|
|
researchJournalAccessionIndex: index("research_journal_accession_idx").on(
|
|
table.user_id,
|
|
table.accession_number,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const searchDocument = sqliteTable(
|
|
"search_document",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
source_kind: text("source_kind")
|
|
.$type<SearchDocumentSourceKind>()
|
|
.notNull(),
|
|
source_ref: text("source_ref").notNull(),
|
|
scope: text("scope").$type<SearchDocumentScope>().notNull(),
|
|
user_id: text("user_id").references(() => user.id, { onDelete: "cascade" }),
|
|
ticker: text("ticker"),
|
|
accession_number: text("accession_number"),
|
|
title: text("title"),
|
|
content_text: text("content_text").notNull(),
|
|
content_hash: text("content_hash").notNull(),
|
|
metadata: text("metadata", { mode: "json" }).$type<Record<
|
|
string,
|
|
unknown
|
|
> | null>(),
|
|
index_status: text("index_status").$type<SearchIndexStatus>().notNull(),
|
|
indexed_at: text("indexed_at"),
|
|
last_error: text("last_error"),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
searchDocumentSourceUnique: uniqueIndex("search_document_source_uidx").on(
|
|
table.scope,
|
|
sql`ifnull(${table.user_id}, '')`,
|
|
table.source_kind,
|
|
table.source_ref,
|
|
),
|
|
searchDocumentScopeIndex: index("search_document_scope_idx").on(
|
|
table.scope,
|
|
table.source_kind,
|
|
table.ticker,
|
|
table.updated_at,
|
|
),
|
|
searchDocumentAccessionIndex: index("search_document_accession_idx").on(
|
|
table.accession_number,
|
|
table.source_kind,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const searchChunk = sqliteTable(
|
|
"search_chunk",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
document_id: integer("document_id")
|
|
.notNull()
|
|
.references(() => searchDocument.id, { onDelete: "cascade" }),
|
|
chunk_index: integer("chunk_index").notNull(),
|
|
chunk_text: text("chunk_text").notNull(),
|
|
char_count: integer("char_count").notNull(),
|
|
start_offset: integer("start_offset").notNull(),
|
|
end_offset: integer("end_offset").notNull(),
|
|
heading_path: text("heading_path"),
|
|
citation_label: text("citation_label").notNull(),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
searchChunkUnique: uniqueIndex("search_chunk_document_chunk_uidx").on(
|
|
table.document_id,
|
|
table.chunk_index,
|
|
),
|
|
searchChunkDocumentIndex: index("search_chunk_document_idx").on(
|
|
table.document_id,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const researchArtifact = sqliteTable(
|
|
"research_artifact",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
organization_id: text("organization_id").references(() => organization.id, {
|
|
onDelete: "set null",
|
|
}),
|
|
ticker: text("ticker").notNull(),
|
|
accession_number: text("accession_number"),
|
|
kind: text("kind").$type<ResearchArtifactKind>().notNull(),
|
|
source: text("source")
|
|
.$type<ResearchArtifactSource>()
|
|
.notNull()
|
|
.default("user"),
|
|
subtype: text("subtype"),
|
|
title: text("title"),
|
|
summary: text("summary"),
|
|
body_markdown: text("body_markdown"),
|
|
search_text: text("search_text"),
|
|
visibility_scope: text("visibility_scope")
|
|
.$type<ResearchVisibilityScope>()
|
|
.notNull()
|
|
.default("private"),
|
|
tags: text("tags", { mode: "json" }).$type<string[]>(),
|
|
metadata: text("metadata", { mode: "json" }).$type<Record<
|
|
string,
|
|
unknown
|
|
> | null>(),
|
|
file_name: text("file_name"),
|
|
mime_type: text("mime_type"),
|
|
file_size_bytes: integer("file_size_bytes"),
|
|
storage_path: text("storage_path"),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
researchArtifactTickerIndex: index("research_artifact_ticker_idx").on(
|
|
table.user_id,
|
|
table.ticker,
|
|
table.updated_at,
|
|
),
|
|
researchArtifactKindIndex: index("research_artifact_kind_idx").on(
|
|
table.user_id,
|
|
table.kind,
|
|
table.updated_at,
|
|
),
|
|
researchArtifactAccessionIndex: index("research_artifact_accession_idx").on(
|
|
table.user_id,
|
|
table.accession_number,
|
|
),
|
|
researchArtifactSourceIndex: index("research_artifact_source_idx").on(
|
|
table.user_id,
|
|
table.source,
|
|
table.updated_at,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const researchMemo = sqliteTable(
|
|
"research_memo",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
user_id: text("user_id")
|
|
.notNull()
|
|
.references(() => user.id, { onDelete: "cascade" }),
|
|
organization_id: text("organization_id").references(() => organization.id, {
|
|
onDelete: "set null",
|
|
}),
|
|
ticker: text("ticker").notNull(),
|
|
rating: text("rating").$type<ResearchMemoRating>(),
|
|
conviction: text("conviction").$type<ResearchMemoConviction>(),
|
|
time_horizon_months: integer("time_horizon_months"),
|
|
packet_title: text("packet_title"),
|
|
packet_subtitle: text("packet_subtitle"),
|
|
thesis_markdown: text("thesis_markdown").notNull().default(""),
|
|
variant_view_markdown: text("variant_view_markdown").notNull().default(""),
|
|
catalysts_markdown: text("catalysts_markdown").notNull().default(""),
|
|
risks_markdown: text("risks_markdown").notNull().default(""),
|
|
disconfirming_evidence_markdown: text("disconfirming_evidence_markdown")
|
|
.notNull()
|
|
.default(""),
|
|
next_actions_markdown: text("next_actions_markdown").notNull().default(""),
|
|
created_at: text("created_at").notNull(),
|
|
updated_at: text("updated_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
researchMemoTickerUnique: uniqueIndex("research_memo_ticker_uidx").on(
|
|
table.user_id,
|
|
table.ticker,
|
|
),
|
|
researchMemoUpdatedIndex: index("research_memo_updated_idx").on(
|
|
table.user_id,
|
|
table.updated_at,
|
|
),
|
|
}),
|
|
);
|
|
|
|
export const researchMemoEvidence = sqliteTable(
|
|
"research_memo_evidence",
|
|
{
|
|
id: integer("id").primaryKey({ autoIncrement: true }),
|
|
memo_id: integer("memo_id")
|
|
.notNull()
|
|
.references(() => researchMemo.id, { onDelete: "cascade" }),
|
|
artifact_id: integer("artifact_id")
|
|
.notNull()
|
|
.references(() => researchArtifact.id, { onDelete: "cascade" }),
|
|
section: text("section").$type<ResearchMemoSection>().notNull(),
|
|
annotation: text("annotation"),
|
|
sort_order: integer("sort_order").notNull().default(0),
|
|
created_at: text("created_at").notNull(),
|
|
},
|
|
(table) => ({
|
|
researchMemoEvidenceMemoIndex: index("research_memo_evidence_memo_idx").on(
|
|
table.memo_id,
|
|
table.section,
|
|
table.sort_order,
|
|
),
|
|
researchMemoEvidenceArtifactIndex: index(
|
|
"research_memo_evidence_artifact_idx",
|
|
).on(table.artifact_id),
|
|
researchMemoEvidenceUnique: uniqueIndex(
|
|
"research_memo_evidence_unique_uidx",
|
|
).on(table.memo_id, table.artifact_id, table.section),
|
|
}),
|
|
);
|
|
|
|
export const authSchema = {
|
|
user,
|
|
session,
|
|
account,
|
|
verification,
|
|
organization,
|
|
member,
|
|
invitation,
|
|
};
|
|
|
|
export const appSchema = {
|
|
watchlistItem,
|
|
holding,
|
|
filing,
|
|
filingStatementSnapshot,
|
|
filingTaxonomySnapshot,
|
|
issuerOverlay,
|
|
issuerOverlayRevision,
|
|
filingTaxonomyAsset,
|
|
filingTaxonomyConcept,
|
|
filingTaxonomyFact,
|
|
filingTaxonomyMetricValidation,
|
|
companyFinancialBundle,
|
|
companyOverviewCache,
|
|
filingLink,
|
|
taskRun,
|
|
taskStageEvent,
|
|
portfolioInsight,
|
|
researchJournalEntry,
|
|
searchDocument,
|
|
searchChunk,
|
|
researchArtifact,
|
|
researchMemo,
|
|
researchMemoEvidence,
|
|
};
|
|
|
|
export const schema = {
|
|
...authSchema,
|
|
...appSchema,
|
|
};
|