csvsleuth design

Principle: do everything deterministic in Python, send the model only a compact schema, and never trust its SQL.

1. Type inference (inference.py)

Each column is a list of raw strings. Nulls ("", NA, N/A, null, none, nan, -) are removed first. The column type is the first rule that every remaining value satisfies:

1. Number: optional currency symbol (₹ $ € £ Rs INR), optional sign or (parentheses) negative, digits grouped Western (1,234,567) or Indian (12,34,567) with optional decimals. ₹1,23,456.78 becomes 123456.78. Values with a leading zero (01234) are rejected so zip codes and IDs stay text. All ints gives INTEGER, otherwise REAL.
2. Date: ISO, yyyy/mm/dd, numeric a/b/yyyy (separators / - ., 2 or 4 digit year), 5 Mar 2024, March 5, 2024, each with optional time (24h or AM/PM). Stored as ISO text so SQLite date functions work.
3. Boolean: distinct values are a subset of one of true/false, yes/no, y/n, t/f. Stored 0/1. A column of 0/1 is an INTEGER already.
4. Otherwise TEXT.

Day/month ambiguity

04/05/2024 is 4 May or 5 April. The decision is made once per column, from all its values:

Impossible dates (31/02/2024) make the whole column non-date rather than silently dropping rows.

2. Names

Column and table names are lowercased snake_case: Order Date becomes order_date, customerID becomes customer_id, Discount % becomes discount_pct, 2024 Sales becomes c_2024_sales. Collisions get _2, SQL keywords get a trailing _, blank headers become col_N. Excel sheets become <file>_<sheet> (or just the sheet name when it equals the file name).

3. Schema card (schema.py)

TABLE "sales" (5000 rows)
  order_date DATE e.g. 2024-02-07, 2024-03-08, 2024-05-25; range 2024-01-01..2024-12-31; 366 distinct
  product TEXT values: Backpack | Desk Lamp | Headphones | Keyboard | Notebook | Water Bottle
LIKELY JOINS (shared column names):
  sales.customer_id = customers.customer_id

Bounds: 3 samples per column, each at most 24 characters; at most 60 columns per table; the whole card at most 8000 characters. TEXT columns with at most 12 distinct values list all values so the model can write exact filters. Joins are hinted for same-named columns with compatible types.

4. SQL safety (sandbox.py)

Layers, each independently sufficient for the attacks it targets:

1. Scanner: strips comments, respects quotes, rejects more than one statement and anything not starting with SELECT/WITH. A ; inside 'a;b' or a comment is fine.
2. Read-only database: data is loaded into a temp file, which is reopened with file:...?mode=ro. Writes fail with "readonly database".
3. Authorizer: only SQLITE_SELECT, READ, FUNCTION (minus load_extension and friends) and RECURSIVE are allowed. ATTACH, PRAGMA, INSERT, CREATE... are denied by SQLite itself.
4. Progress handler: every 10,000 VM steps the deadline is checked; exceeding it interrupts the query ("exceeded the 5s time limit").
5. LIMIT injection: without a top-level LIMIT the statement is wrapped as SELECT * FROM (<stmt>) LIMIT n+1; the extra row tells us whether the output was truncated.

5. Repair loop (engine.py)

If the SQL is rejected or fails, the conversation continues: user prompt, the model's own JSON, then That query failed with this SQLite error: .... The corrected answer is tried once. A second failure is reported to the user. Only successful queries enter session history.

6. Prompt, caching, cost (llm.py)

The request uses claude-haiku-5-5, output_config={"effort": "low", "format": json_schema} so the reply is {sql, explanation}. No temperature, thinking or prefill. The cache key is sha256 of model, system prompt and message list, so a repeated question (same schema) costs nothing.

7. Shell context

The shell keeps the last 3 successful (question, SQL) pairs and includes them in the prompt, so "only the ones in the South" can refine the previous query.

8. Chart

A chart needs a label column (first non-numeric column, or the first numeric one when every column is numeric) and a measure (first other numeric column). Bars are scaled to the largest absolute value using eighth-block glyphs; negative values use a lighter glyph. At most 40 bars.