What is Dafine?

Dafine (Da+fine) is an open-source, AI-powered data cleaning platform. You upload a messy dataset — CSV, Parquet, Excel, or SQLite — and Dafine profiles it, constructs a targeted prompt, calls an LLM via OpenRouter, executes the generated DuckDB SQL, and returns a clean file.

The goal is to automate the tedious, repetitive parts of data preparation: filling missing values with statistically appropriate defaults, normalising inconsistent string casing, removing duplicates, and flagging outliers — without requiring you to write a single line of SQL yourself.

Dafine is built as a standard web app: a FastAPI backend and a pure-HTML/JS frontend. There is no framework lock-in. You can run it locally in under five minutes or deploy it to any cloud that accepts a Dockerfile.

How it Works

Every cleaning request travels through a six-step pipeline, all server-side. Your raw file never leaves the backend until you download the clean result.

Deep Profiling

Before any AI call, Dafine runs a thorough statistical pass over every column using DuckDB's native aggregation functions. The collected stats directly drive the imputation strategy chosen for each column:

Stat	How it's used
Null %	Columns with >40% nulls get the HIGH_NULL label so the AI knows imputation is risky.
Skewness	|skew| > 0.5 → median fill. Near-zero skew → mean fill. Avoids outlier-contaminated means.
IQR (Tukey)	Outliers defined as values outside Q1 − 1.5×IQR or Q3 + 1.5×IQR. Counted per column and reported post-clean.
Unique count	≤20 unique values & <5% of rows → LIKELY_CATEGORICAL. Uses mode fill instead of mean.
Avg string length	>100 chars → LONG_TEXT. Those columns are passed through untransformed.
Casing check	If LOWER(col) has fewer uniques than col, column gets MIXED_CASING → LOWER(TRIM(…)) applied.
Keyword detection	Column names containing date / time / year / timestamp / created / updated get TIME_SERIES → forward-fill strategy.
Financial keywords	revenue / cost / profit / price / … columns grouped. AI may derive missing values from correlated peers.
Duplicate rows	Exact-row duplicates counted. If >0, a SELECT DISTINCT is added to the generated SQL's CTE.

Prompt Construction

The prompt passed to the LLM is fully deterministic — it is built from the profiling output, not from freeform text. For each column, the prompt includes: the null strategy, the computed fill value, applicable labels, top-value distribution, and any user-provided column context.

The AI is instructed to output only raw SQL — no markdown fences, no comments, no prose. The backend strips any stray formatting before handing the SQL to DuckDB.

Outlier Report

After the cleaned table is created, a second IQR pass runs on cleaned_table (not the source). The result is sent back in the X-Outlier-Report response header as a compact JSON object and rendered in the UI so you can decide whether to action the remaining outliers manually.

Tech Stack

Layer	Technology	Role
Backend	FastAPI 0.111+	HTTP API, file handling, streaming responses
Query engine	DuckDB 1.0+	In-process SQL: profiling, executing AI SQL, exporting
AI	OpenRouter API	Routes prompt to chosen LLM (openai/gpt-oss-120b:free default)
Auth	JWT HS256 + Argon2id	Session tokens; password hashing
Key storage	AES-256-GCM	OpenRouter API key encrypted at rest per user
Database	Supabase (Postgres)	Users, cleaning history, AI output, column context
File storage	Supabase Storage	Cleaned files stored as Parquet, downloaded as CSV
Frontend	Vanilla HTML/JS + Tailwind CDN	No build step required
Charts	Chart.js 4.4	Dashboard visualisations
Containerisation	Docker	Single-image backend, compatible with Railway / Fly / Render

Prerequisites

Before running Dafine locally, make sure you have the following:

Requirement	Notes
Python 3.11+	Required by the FastAPI backend and DuckDB bindings.
pip	Used to install Python dependencies from requirements.txt.
Supabase project	Free tier is sufficient. You need the project URL and service role key.
OpenRouter account	Free-tier models are supported. Get your key at openrouter.ai.
A live-server tool	For the frontend: VS Code Live Server, npx serve, or any static file server.

Supabase Schema

Create the following tables in your Supabase project's SQL editor:

-- Users table
CREATE TABLE users (
  id               BIGSERIAL PRIMARY KEY,
  email            TEXT UNIQUE NOT NULL,
  password_hash    TEXT NOT NULL,
  encrypted_api_key TEXT,
  created_at       TIMESTAMPTZ DEFAULT now(),
  updated_at       TIMESTAMPTZ DEFAULT now()
);

-- Column context (optional per-session context)
CREATE TABLE column_context (
  id      BIGSERIAL PRIMARY KEY,
  context JSONB
);

-- AI output (SQL + reasoning stored per session)
CREATE TABLE ai_output (
  id          BIGSERIAL PRIMARY KEY,
  sql_query   TEXT,
  explanation TEXT
);

-- Clean file metadata
CREATE TABLE clean_file (
  id            BIGSERIAL PRIMARY KEY,
  user_id       BIGINT REFERENCES users(id),
  original_name TEXT,
  name          TEXT,
  type          TEXT,
  location      TEXT,
  size_bytes    BIGINT,
  created_at    TIMESTAMPTZ DEFAULT now()
);

-- Cleaning history
CREATE TABLE cleaning_history (
  id                 BIGSERIAL PRIMARY KEY,
  user_id            BIGINT REFERENCES users(id),
  title              TEXT,
  original_file_name TEXT,
  original_file_type TEXT,
  original_file_size BIGINT,
  rows_before        BIGINT,
  rows_after         BIGINT,
  status             TEXT DEFAULT 'completed',
  column_context_id  BIGINT REFERENCES column_context(id),
  ai_output_id       BIGINT REFERENCES ai_output(id),
  clean_file_id      BIGINT REFERENCES clean_file(id),
  created_at         TIMESTAMPTZ DEFAULT now()
);

Also create a private Supabase Storage bucket named dafine-files.

Installation

Clone the repository

git clone https://github.com/your-username/dafine.git
cd dafine

Create a Python virtual environment

python -m venv .venv

# macOS / Linux
source .venv/bin/activate

# Windows
.venv\Scripts\activate

Install backend dependencies

cd backend
pip install -r requirements.txt

The requirements.txt includes: FastAPI, Uvicorn, DuckDB, Pandas, httpx, Argon2-cffi, cryptography, PyJWT, Supabase Python client, and openpyxl.

Environment Variables

Create a .env file inside the backend/ directory:

# Supabase
SUPABASE_URL=https://your-project-id.supabase.co
SUPABASE_SERVICE_KEY=your-supabase-service-role-key

# JWT session signing key (generate with: openssl rand -hex 32)
JWT_SECRET=replace-with-a-long-random-string
JWT_EXPIRE_HOURS=24

# AES-256-GCM key for encrypting user API keys (must decode to 32 bytes)
# Generate with: python -c "import os,base64; print(base64.b64encode(os.urandom(32)).decode())"
ENCRYPTION_KEY=your-base64-32-byte-key

# Optional: fallback OpenRouter key (overridden by per-user keys)
OPENROUTER_API_KEY=sk-or-...
OPENROUTER_MODEL=openai/gpt-oss-120b:free

Variable	Required	Description
SUPABASE_URL	Required	Your Supabase project URL.
SUPABASE_SERVICE_KEY	Required	Service role key — bypasses Row Level Security so the backend can read/write all user rows.
JWT_SECRET	Required	Random string used to sign JWT session tokens. Change this before going to production.
ENCRYPTION_KEY	Required	Base64-encoded 32-byte key for AES-256-GCM encryption of stored OpenRouter API keys.
JWT_EXPIRE_HOURS	Optional	Token lifetime in hours. Defaults to 24.
OPENROUTER_API_KEY	Optional	Fallback key used if a user has not saved their own key. Useful for testing.
OPENROUTER_MODEL	Optional	Model string passed to OpenRouter. Defaults to openai/gpt-oss-120b:free.
PORT	Optional	Port the Uvicorn server binds to. Defaults to 8000. Set automatically by most cloud platforms.

Running Locally

Start the backend

cd backend
uvicorn main:app --reload --port 8000

The API is now available at http://127.0.0.1:8000. You can explore the auto-generated docs at http://127.0.0.1:8000/docs.

Serve the frontend

Open frontend/index.html (or login.html) with a live-server tool:

# With VS Code Live Server: right-click login.html → Open with Live Server
# Result: http://127.0.0.1:5500/frontend/login.html

# Or with npx:
npx serve frontend
# Result: http://localhost:3000/login.html

Docker (optional)

cd backend
docker build -t dafine-backend .
docker run -p 8000:8000 --env-file .env dafine-backend

Upload & Preview

The first page a logged-in user sees is the Clean page (main.html). Drag a file onto the drop zone or click "Choose File".

Supported formats

Extension	Notes
.csv	Auto-detected delimiter via DuckDB's read_csv_auto.
.parquet	Columnar binary format. Loaded with read_parquet.
.xlsx / .xls	Loaded via DuckDB's spatial extension (st_read). First sheet only.
.db / .sqlite	Attached as a SQLite database. First table is used as the source.

Preview endpoint

Clicking Preview Dataset calls POST /preview. The backend reads the file into a temporary DuckDB view named source_table and returns:

• → File type, total rows, total columns, total null count
• → Column names and DuckDB inferred types
• → First 10 rows as a JSON array

The preview renders a metadata grid, column type chips, and a scrollable data table so you can sanity-check the file before running the (billable) AI cleaning step.

Session title

Above the drop zone is an optional Session Title field. Whatever you type here is stored with the cleaning record in the database and shown in History. If left blank, the original filename is used.

AI Cleaning Pipeline

Clicking Clean with AI triggers POST /clean. This is the core endpoint — it runs all six steps and streams back the cleaned file.

What the AI generates

The LLM is given a strict prompt that demands exactly one SQL statement beginning with CREATE TABLE cleaned_table AS. A typical output looks like:

CREATE TABLE cleaned_table AS
WITH base AS (
  SELECT DISTINCT "id", "name", "age", "salary", "department", "hire_date"
  FROM source_table
)
SELECT
  "id",
  LOWER(TRIM(COALESCE("name", 'Unknown'))) AS "name",
  COALESCE("age", 32) AS "age",
  COALESCE("salary", 58400.0) AS "salary",
  LOWER(TRIM(COALESCE("department", 'Engineering'))) AS "department",
  COALESCE("hire_date", '2023-01-01') AS "hire_date"
FROM base;

Response headers

Alongside the file binary, /clean sends several custom headers that the frontend reads:

Header	Contents
X-AI-SQL	The full SQL that was executed (URL-encoded).
X-AI-Reasoning	The LLM's reasoning field, if the model supports it.
X-Outlier-Report	JSON object mapping numeric column names to IQR stats and outlier counts.
X-History-ID	The ID of the newly created cleaning_history record.
Content-Disposition	Filename for the downloaded file, e.g. sales_cleaned.csv.

Output format

Dafine returns the cleaned file in the same format you uploaded whenever possible. SQLite files are exported as CSV (DuckDB cannot write SQLite). If Pandas/openpyxl is unavailable, XLSX also falls back to CSV.

Column Context

After previewing your data, each column has an optional free-text input below the preview table. This is the Column Context grid.

The text you enter here is injected into the AI prompt as a [USER CONTEXT: …] annotation for that specific column, and it takes the highest priority over any automatically computed strategy. Use it to clarify domain meaning that the profiler cannot infer from statistics alone.

Examples

Column	Context you provide	Effect
gender	1=Male, 2=Female, 3=Non-binary	AI treats numeric values as category codes, not quantities.
price	in USD, should never be negative	AI may add a guard clause clamping to 0.
notes	free-text field, do not modify or truncate	AI passes the column through untouched even if WHITESPACE_ISSUE is flagged.
status	active / inactive / pending only	AI can replace any other value with a NULL or the mode.

Context strings are saved to the column_context table and shown in the History detail panel so you can review what guidance was given to the AI for past sessions.

Dashboard

The Dashboard (dashboard.html) lets you load any previously cleaned dataset and build interactive charts — no code required.

Loading a dataset

Click Load Dataset to open the session picker. Select any completed cleaning session; the backend downloads the stored Parquet from Supabase Storage, converts it to CSV on the fly, and streams it to the browser. The browser then parses the CSV in-memory using a custom parser.

Chart types

Five chart types are available via the toolbar: Bar Line Scatter Pie Doughnut. All are rendered by Chart.js 4.4.

Configuration options

Control	Description
X-Axis / Labels	The column whose distinct values form the chart's X axis or pie slices.
Y-Axis / Values	One or more numeric columns plotted as series. Click Add for multi-series charts.
Aggregation	None (raw), Sum, Average, Count, Max, Min. Applied per X-axis group.
Sort	Sort by any column, ascending or descending, before rendering.
Rank Limit	Restrict to Top N or Bottom N rows (applied after sort).
Filters	Row-level filters: =, !=, >, <, ≥, ≤, contains. Multiple filters stack (AND logic).
Legend / Grid	Toggle Chart.js legend and gridlines.

Saving dashboards

Click Save to persist the full chart configuration (type, axes, series, filters, sort, rank, display options, title) to localStorage under the key dafine_dashboards. Up to 30 dashboards are kept. Saved dashboards appear in the History page's Dashboard History tab and can be reopened via a URL parameter (dashboard.html?history_id=123).

History

The History page (history.html) has two tabs:

Cleaning History tab

Every completed /clean call is recorded here. Each row shows: session title, original filename, format badge, file size, row counts before → after, and the date. Expanding a row reveals three sub-tabs:

• → Column Context — the context strings you provided before cleaning.
• → SQL Query — the DuckDB SQL generated by the AI (hidden by default, reveal on click).
• → AI Reasoning — the model's own explanation of its choices (hidden by default).

You can also Download the cleaned CSV directly from this page, or Delete the record (which also removes the stored Parquet from Supabase Storage).

Dashboard History tab

Lists all chart configurations you have saved in localStorage. Click Open to load the dashboard immediately with the saved settings restored.

API Endpoints

Auth routes — /auth/*

Method + Path	Auth	Description
POST /auth/register	Public	Create a new account. Body: { email, password }. Returns JWT.
POST /auth/login	Public	Login. Returns JWT + has_api_key flag.
GET /auth/me	JWT	Returns current user info: id, email, has_api_key, created_at.
PUT /auth/password	JWT	Change password. Body: { current_password, new_password }.
PUT /auth/api-key	JWT	Save or update OpenRouter key. Body: { api_key }. Stored AES-256-GCM encrypted.
GET /auth/api-key/status	JWT	Returns { has_api_key: bool }.

Data routes

Method + Path	Auth	Description
POST /preview	Public	Upload a file, returns schema + first 10 rows. No cleaning performed.
POST /clean	JWT	Full cleaning pipeline. Accepts file, column_contexts (JSON string), optional title. Returns binary file + custom headers.
GET /history	JWT	List all cleaning sessions for the authenticated user, newest first.
GET /history/{id}/download	JWT	Download cleaned CSV for a given history record. Converts stored Parquet on the fly.
DELETE /history/{id}	JWT	Delete a history record and its associated Parquet from Supabase Storage.

Authentication

All protected endpoints require a Bearer token in the Authorization header:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Tokens are signed with HS256 using the JWT_SECRET environment variable. The default expiry is 24 hours (JWT_EXPIRE_HOURS). After expiry, the user must log in again.

Passwords are hashed with Argon2id via the argon2-cffi library before storage. Plain-text passwords are never stored anywhere in the system.

Supported File Types

The backend accepts the following extensions at both /preview and /clean:

.csv .parquet .xlsx .xls .db .sqlite

Any other extension returns HTTP 415 Unsupported Media Type. There is no hard file-size limit enforced by the backend, but very large files (>100 MB) will significantly increase profiling and AI prompt time. The frontend upload zone displays "max 100 MB" as a recommended guideline.

Security Model

Passwords

User passwords are hashed with Argon2id before being stored in Supabase. Argon2id is the current recommended algorithm for password hashing. Parameters are managed by the argon2-cffi library's defaults.

API key encryption

OpenRouter API keys entered by users are encrypted using AES-256-GCM with a random 96-bit nonce before being stored in the database column encrypted_api_key. The master key is the ENCRYPTION_KEY environment variable (32 bytes, base64-encoded). Keys are only decrypted in memory at the moment they are needed for an AI call and are never returned to the frontend.

Supabase service role key

The backend uses Supabase's service role key, which bypasses Row Level Security. This key must never be included in any frontend code or public repository. It is loaded from the .env file and only exists server-side.

Uploaded files

Uploaded files are written to a temporary file on the server (tempfile.NamedTemporaryFile) and deleted immediately after processing in a finally block. They are not persisted to disk. Only the cleaned result is stored — as Parquet in Supabase Storage, in a private bucket.

CORS

The backend currently sets allow_origins=["*"] for development convenience. In production, restrict this to your frontend's origin.

OpenRouter API Key

Dafine does not bundle any AI API key. Each user must provide their own OpenRouter key in Account Settings before they can use the cleaning feature. This design means:

• → You control which model you use and how much you spend.
• → The operator (you, if self-hosting) is not billed for users' AI calls.
• → Keys are encrypted with AES-256-GCM and never exposed to the browser.

To get a key, create an account at openrouter.ai. Free-tier models (labelled :free) work out of the box. The default model is openai/gpt-oss-120b:free.

You can change the default model by setting OPENROUTER_MODEL in your .env. Any model available on OpenRouter with instruction-following ability should work. Models with extended thinking / reasoning fields will populate the "AI Reasoning" section of the result.

FAQ

Does Dafine send my data to OpenRouter?

No. Dafine sends the AI a statistical summary of your data (column names, data types, null percentages, skewness, top-value distributions, sample values), not the raw rows. Your actual data never leaves your server. You can inspect the exact prompt by reading backend/main.py → build_prompt().

Why DuckDB instead of Pandas?

DuckDB is an in-process analytical database that handles Parquet, CSV, and Excel natively with SQL. It can profile millions of rows faster than equivalent Pandas operations and requires no separate database server. The generated cleaning SQL also runs in DuckDB, making the whole pipeline stateless and simple to containerise.

Can I use a different LLM?

Yes — any model available on OpenRouter. Set OPENROUTER_MODEL in .env. You can also replace the OpenRouter call in call_ai() in main.py to point at any OpenAI-compatible API.

What happens if the AI generates invalid SQL?

DuckDB will raise an exception, which the backend catches and returns as HTTP 422 with the failed SQL included in the error message. The frontend shows the error in the red error box. You can retry — the prompt is deterministic, so adding more column context sometimes resolves ambiguity that confused the model.

Is my data stored after cleaning?

The cleaned result is stored as a Parquet file in your Supabase Storage bucket so you can download it later from the History page. The original uploaded file is deleted from the server immediately after processing. You can delete any cleaning session (and its stored file) from the History page at any time.

Why is the outlier report shown after cleaning, not before?

The outlier report runs on cleaned_table (the output) rather than the source data. This tells you what outliers remain after the AI's cleaning pass — which is more actionable than knowing what outliers existed before. If you want pre-cleaning outlier data, it is computed during profiling and included in the AI prompt as the POTENTIAL_OUTLIER label.

Known Limits

Area	Limitation
SQLite source	Only the first table in the database is used as the source. Multi-table SQLite databases are not joined automatically.
XLSX	Only the first sheet is read. Requires DuckDB's spatial extension, which is auto-installed on first use.
Dashboard storage	Saved dashboard configurations live in browser localStorage. They are not synced to the cloud and are lost if browser data is cleared.
Data table preview	The dashboard's data table displays a maximum of 200 filtered rows to keep the DOM manageable. The full dataset is used for chart aggregation.
AI model quality	Free-tier models may occasionally produce SQL with minor syntax errors. Retry with a paid model if you encounter repeated failures.
Forward fill	True time-series forward fill (propagating the previous row's value) is not yet implemented in DuckDB SQL via the AI prompt. Dafine approximates it using the column mode as a fallback fill value.
Multi-sheet XLSX	Only the first sheet is read; additional sheets are ignored.
CORS	allow_origins=["*"] in development. Tighten this before public deployment.