Skip to content

overview.md

Latest commit

 

History

History
553 lines (448 loc) · 16.1 KB

overview.md

File metadata and controls

553 lines (448 loc) · 16.1 KB
Raw

Data Models Overview

TypeScript interfaces defining the application's data structures. Located in models/.

Core Models

DatabaseConnection

File: models/database-connection.interface.ts

Represents a saved database connection.

interface DatabaseConnection {
  id: string;                    // UUID
  name: string;                  // Display name
  type: string;                  // Database type (e.g., "postgresql")
  host: string;                  // Hostname or IP
  port: string;                  // Port number
  database: string;              // Database name
  username: string;              // Username
  password: string;              // Password (empty string when sent to client in auth mode)
  filepath?: string;             // File path (for SQLite)
  description?: string;          // Business context description
  status: "connected" | "disconnected";
  schemaFileId?: string;         // OpenAI file ID
  vectorStoreId?: string;        // OpenAI vector store ID
  source?: "local" | "server";   // Origin: user-created or admin-managed
  createdAt: string;             // ISO timestamp

  // Sharing (auth mode only) — set server-side by getConnectionsForUser
  accessLevel?: "owner" | "view" | "edit"; // undefined ⇒ owned (localStorage mode)
  sharedByEmail?: string;        // owner's email, shown on "Shared with you" items
  sharedByName?: string | null;
}

Notes:

  • schemaFileId and vectorStoreId are populated after schema upload
  • status is managed by context, not user-set
  • All four database types: postgresql, mysql, sqlserver, sqlite (typed as DatabaseType)
  • source: 'server' indicates an admin-managed connection (no owner, stored in app DB)
  • password is always empty string on the client in auth mode — credentials resolved server-side
  • Server connections are only visible to non-admin users after a schema has been uploaded
  • accessLevel/sharedBy* are populated only in auth mode for shared connections; undefined/"owner" means the current user owns it

Schema

File: models/schema.interface.ts

Container for database schema linked to a connection.

interface Schema {
  connectionId: string;    // Links to DatabaseConnection.id
  tables: DatabaseTable[];
}

Usage:

const schema: Schema = {
  connectionId: "abc-123",
  tables: [
    { name: "customers", columns: [...] },
    { name: "orders", columns: [...] }
  ]
};

DatabaseTable

File: models/database-table.interface.ts

Represents a database table.

interface DatabaseTable {
  name: string;               // Table name
  columns: Column[];          // Column definitions
  description?: string;       // Manual description
  aiDescription?: string;     // AI-generated description
  hidden?: boolean;           // Hide from query generation
  isNew?: boolean;            // Marked new after re-introspection
}

Description Fields:

  • description - User-provided description
  • aiDescription - Generated by OpenAI or fallback generator
  • Display priority: description > aiDescription > default

Column

File: models/column.interface.ts

Represents a table column.

interface Column {
  name: string;               // Column name
  type: string;               // Data type (e.g., "integer", "varchar")
  nullable: boolean;          // Allows NULL
  primary_key?: boolean;      // Is primary key
  foreign_key?: string;       // FK reference (e.g., "users.id")
  description?: string;       // Manual description
  aiDescription?: string;     // AI-generated description
  hidden?: boolean;           // Hide from query generation
  isNew?: boolean;            // Marked new after re-introspection
  isModified?: boolean;       // Type changed after re-introspection
}

Foreign Key Format:

// Format: "table_name.column_name"
{ foreign_key: "customers.id" }
{ foreign_key: "products.product_id" }

SavedReport

File: models/saved-report.interface.ts

Represents a saved/parameterized report.

interface SavedReport {
  id: string;
  connectionId: string;

  // Report metadata
  name: string;
  description?: string;

  // Query information
  naturalLanguageQuery: string;  // Original prompt
  sql: string;                   // Generated SQL (may contain {{parameters}})
  explanation: string;           // AI explanation
  warnings: string[];            // Any warnings
  confidence: number;            // AI confidence score

  // Parameters
  parameters?: ReportParameter[];

  // Timestamps
  createdAt: string;
  lastModified: string;
  lastRun?: string;

  // UI state
  isFavorite?: boolean;

  // Saved chart — when set, re-running renders this chart immediately (no AI generation)
  visualization?: ChartConfig;

  // Pins this report to the dashboard as a KPI metric or trend chart
  dashboardWidget?: DashboardWidgetConfig;

  // Origin: "local" (user-created) or "server" (loaded from config/reports.json, read-only)
  source?: "local" | "server";

  // Sharing (auth mode only) — set server-side by getReportsForUser
  accessLevel?: "owner" | "view" | "edit"; // undefined ⇒ owned (localStorage mode)
  sharedByEmail?: string;
  sharedByName?: string | null;
}

DashboardWidgetConfig (also in models/saved-report.interface.ts) pins a report to the dashboard:

interface DashboardWidgetConfig {
  kind: 'metric' | 'chart';
  // metric only — KPI value is rows[0][0] of the report's result
  target?: number;
  unit?: 'number' | 'currency' | 'percent';
  higherIsBetter?: boolean;     // default true
  // chart only — optional cached config; generated on the fly when absent
  chartConfig?: ChartConfig;
}

SQL with Parameters:

// Parameters in SQL use {{name}} syntax
{
  sql: "SELECT * FROM orders WHERE created_at > '{{start_date}}'",
  parameters: [
    { name: "start_date", type: "date", label: "Start Date" }
  ]
}

ReportParameter

File: models/saved-report.interface.ts

Defines a report parameter.

interface ReportParameter {
  name: string;                                    // Parameter identifier
  type: 'text' | 'number' | 'date' | 'datetime' | 'boolean';
  label: string;                                   // Display label
  defaultValue?: any;                              // Default value
  description?: string;                            // Help text
}

Example:

const parameters: ReportParameter[] = [
  {
    name: "start_date",
    type: "date",
    label: "Start Date",
    defaultValue: "2024-01-01",
    description: "Beginning of reporting period"
  },
  {
    name: "category",
    type: "text",
    label: "Product Category",
    description: "Filter by category name"
  }
];

ConnectionFormData

File: models/connection-form-data.interface.ts

Form data for creating/editing connections.

interface ConnectionFormData {
  name: string;
  type: string;
  host: string;
  port: string;
  database: string;
  username: string;
  password: string;
  description?: string;
}

Used to separate form state from full DatabaseConnection model.

DatabaseContextType

File: models/database-context-type.interface.ts

TypeScript interface for the database context.

interface DatabaseContextType {
  // State
  connections: DatabaseConnection[];
  connectionStatus: "idle" | "success" | "error";
  connectionSchemas: Schema[];
  currentConnection?: DatabaseConnection;
  currentSchema?: Schema;
  isInitialized: boolean;
  reports: SavedReport[];

  queryAccuracy: QueryAccuracyStats;

  // Connection methods
  setConnectionStatus: (status: "idle" | "success" | "error") => void;
  setCurrentConnection: (conn: DatabaseConnection) => void;
  getConnection: (id?: string) => DatabaseConnection | undefined;
  addConnection: (conn: DatabaseConnection) => void;
  updateConnection: (conn: DatabaseConnection) => void;
  deleteConnection: (id: string) => void;
  duplicateConnection: (id: string) => DatabaseConnection | null;
  importConnections: (conns: DatabaseConnection[]) => void;
  refreshConnections: () => Promise<void>;

  // Schema methods
  getSchema: (id?: string) => Schema | undefined;
  setSchema: (schema: Schema) => void;
  setCurrentSchema: (schema: Schema) => void;

  // Report methods
  loadReports: () => Promise<void>;
  saveReport: (report: SavedReport) => Promise<void>;
  updateReport: (report: SavedReport) => Promise<void>;
  deleteReport: (id: string) => Promise<void>;

  // Query history (device-local)
  recordQueryHistory: (entry: QueryHistoryEntry) => void;
  getQueryHistory: () => Promise<QueryHistoryEntry[]>;
  deleteQueryHistory: (id: string) => Promise<void>;
  clearQueryHistory: () => Promise<void>;

  // Query accuracy (global per-user; local by default, synced when auth enabled)
  recordQueryOutcome: (success: boolean) => void;
  overrideQueryOutcome: (oldSuccess: boolean, newSuccess: boolean) => void;

  // Learned query corrections (failed->revised pairs; pooled by fingerprint in auth mode)
  recordQueryCorrection: (entry: QueryCorrection) => void;
  getCorrectionsForFingerprint: (fingerprint: string) => Promise<QueryCorrection[]>;
  updateQueryCorrection: (id: string, patch: Partial<QueryCorrection>) => Promise<void>;
  deleteQueryCorrection: (id: string) => Promise<void>;
}

Notes:

  • refreshConnections() re-fetches all connections and schemas from the StorageProvider, useful after admin mutations
  • Reports, query history, accuracy, and corrections are all managed through the context (not directly via localStorage)
  • Query history and correction methods are fire-and-forget so they can never break the query-execution flow

ChartConfig

File: models/chart-config.interface.ts

Configuration for chart rendering. ChartConfig is a discriminated union keyed on type, not a single flat interface — each chart type carries its own column fields.

type ChartType = "bar" | "line" | "pie" | "area" | "scatter" | "composed";

interface BaseChartConfig {
  type: ChartType;
  title?: string;
  description?: string;
}

type ChartConfig =
  | BarChartConfig       // xAxisColumn, yAxisColumns[], stacked?, colors?
  | LineChartConfig      // xAxisColumn, yAxisColumns[], smooth?, showDots?
  | PieChartConfig       // nameColumn, valueColumn, showLabels?
  | AreaChartConfig      // xAxisColumn, yAxisColumns[], stacked?
  | ScatterChartConfig   // xAxisColumn, yAxisColumn, nameColumn?, color?
  | ComposedChartConfig; // xAxisColumn, bars?[], lines?[], areas?[]  (bar+line+area)

Notes:

  • The "composed" type combines bars, lines, and/or areas on shared axes
  • Related types in the same file: ChartGenerationRequest, ChartGenerationResponse, ChartToolDefinition, and the CHART_TOOLS OpenAI function-calling definitions

QueryTab

File: models/query-tab.interface.ts

Represents a query tab in the multi-tab query interface.

interface QueryTab {
  id: string;
  type: 'original' | 'followup';
  question: string;
  // Generation state
  isGenerating: boolean;
  generatedSql?: string;
  explanation?: string;
  confidence?: number;
  warnings?: string[];
  // Execution state
  isExecuting: boolean;
  results?: QueryExecutionResult;
  error?: string;
  // Follow-up specific
  parentTabId?: string;
  explanationResponse?: ExplanationResponse;
}

Related types:

  • FollowUpResponseType - 'query' | 'explanation'
  • RowLimitOption - 'none' | 25 | 50 | 100 | 'all'
  • QueryExecutionResult - { columns, rows, rowCount, executionTime }

CommonTypes

File: models/common-types.ts

Shared type definitions used across the application.

type CellValue = string | number | boolean | null | Date;
type DataRow = CellValue[];
type ParameterValue = string | number | boolean | Date | null | undefined;
type JsonValue = string | number | boolean | null | JsonObject | JsonArray;

interface AISuggestion {
  id: string;
  title: string;
  description: string;
  naturalLanguageQuery: string;
  category: string;
  priority: number;
}

QueryHistoryEntry

File: models/query-history.interface.ts

A single executed-query history entry. Captured at the execution choke point for every query that executes successfully; device-local (localStorage) in both auth and no-auth modes and never synced to the app DB.

interface QueryHistoryEntry {
  id: string;
  connectionId: string;
  connectionName?: string;   // denormalized so entries read well if the connection is deleted
  databaseType?: string;
  question?: string;         // natural-language question (when from generation/follow-up)
  sql: string;
  source: 'generated' | 'manual' | 'report' | 'followup';
  success: boolean;          // new entries are always true (failures aren't recorded)
  rowCount?: number;
  executionTimeMs?: number;
  error?: string;
  executedAt: string;        // ISO timestamp
}

QueryAccuracyStats

File: models/query-accuracy.interface.ts

Running tally of AI-generated query executions used to compute the "% Query Accuracy" dashboard stat. Per-user, global across all connections — counters only, no per-query records. Device-local (localStorage query_accuracy) by default; synced to Postgres (query_accuracy_stats) when auth is enabled.

interface QueryAccuracyStats {
  total: number;        // every AI-generated/follow-up execution counted
  successful: number;   // of those, how many succeeded (minus user overrides)
}

accuracy% = Math.round(successful / total * 100).

QueryCorrection

File: models/query-correction.interface.ts

A captured failed→revised SQL pair, used to warn the AI generator away from repeating known mistakes for a schema. Device-local (localStorage) when auth is disabled; pooled team-wide in Postgres keyed by schemaFingerprint when auth is enabled (migration 006_query_corrections.sql). ownerId/ownerName are attribution-only (curation rights).

interface QueryCorrection {
  id: string;
  schemaFingerprint: string; // see utils/schema-fingerprint
  question?: string;         // NL question that produced the bad SQL
  badSql: string;            // the SQL that failed execution
  error: string;             // sanitized execution error
  goodSql: string;           // corrected SQL from the revise endpoint
  databaseType?: string;
  createdAt: string;
  ownerId?: string;          // auth mode only — curation rights (author or admin)
  ownerName?: string;        // auth mode only — display name/email for the curation UI
  updatedAt?: string;        // auth mode only
}

Model Relationships

DatabaseConnection
        │
        │ connectionId
        ▼
     Schema
        │
        │ tables
        ▼
  DatabaseTable[]
        │
        │ columns
        ▼
    Column[]

DatabaseConnection
        │
        │ connectionId
        ▼
  SavedReport[]
        │
        │ parameters
        ▼
ReportParameter[]

localStorage Keys

Key Type
databaseConnections DatabaseConnection[]
currentDbConnection DatabaseConnection
connectionSchemas Schema[]
saved_reports SavedReport[]
query_history QueryHistoryEntry[]
query_accuracy QueryAccuracyStats
query_corrections QueryCorrection[]
suggestions_{id} AISuggestion[]

Type Safety Tips

Null Checks

// Always check for undefined
if (!currentConnection) return null;

// Optional chaining
const vectorStoreId = currentConnection?.vectorStoreId;

// Nullish coalescing
const description = column.description ?? column.aiDescription ?? "";

Type Guards

function isConnected(conn: DatabaseConnection): boolean {
  return conn.status === "connected";
}

function hasSchema(conn: DatabaseConnection): boolean {
  return !!conn.schemaFileId && !!conn.vectorStoreId;
}

Related Documentation