Schema Generation API

Draft a CREATE TABLE statement from JSON samples, for AI agents and rapid prototyping

Overview

The schema inference endpoints read JSON sample objects and return:

the inferred columns, each with a SQL type and a nullable flag
a CREATE TABLE statement you can review and run

The inference is a fast heuristic. Treat its DDL as a draft and review it before you run it (see Current Behaviour).

The endpoints are served by heliosdb-lite start under /v1 (and /api/v1) on the HTTP port (default 8080). They require a JWT signed with HELIOSDB_JWT_SECRET; see Authentication.

Quick Start

1. Infer a Schema from JSON

curl -X POST http://127.0.0.1:8080/v1/schema/infer \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "samples": [
      {"id": 1, "name": "Widget", "price": 29.99, "in_stock": true, "tags": ["electronics", "gadget"]},
      {"id": 2, "name": "Gadget Pro", "price": 49.99, "in_stock": false, "tags": ["electronics", "premium"]}
    ],
    "table_name": "products"
  }'

2. Review the Draft DDL

The response is wrapped in {"success": ..., "data": ...}. Abbreviated:

{
  "success": true,
  "data": {
    "table_name": "products",
    "columns": [
      {"name": "id", "sql_type": "INTEGER", "nullable": false},
      {"name": "name", "sql_type": "VARCHAR(12)", "nullable": false},
      {"name": "price", "sql_type": "NUMERIC", "nullable": false},
      {"name": "in_stock", "sql_type": "BOOLEAN", "nullable": false},
      {"name": "tags", "sql_type": "JSON", "nullable": false}
    ],
    "primary_key": ["id"],
    "ddl": "CREATE TABLE products (\n    id INTEGER NOT NULL,\n    ...\n    PRIMARY KEY (id)\n);",
    "confidence": 0.85,
    "warnings": ["Add 'id' column explicitly if needed for primary key"]
  }
}

Column order in the response is not guaranteed to follow the sample order.

3. Run the DDL

DDL and DML go to the execute endpoint of a branch:

curl -X POST http://127.0.0.1:8080/v1/branches/main/execute \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE products (id INTEGER NOT NULL, name VARCHAR(12) NOT NULL, price NUMERIC NOT NULL, in_stock BOOLEAN NOT NULL, tags JSON NOT NULL, PRIMARY KEY (id))"}'

API Endpoints

Infer Schema

POST /v1/schema/infer

Request Body:

Field	Type	Required	Description
`samples`	array	Yes	JSON objects; at least one is required
`table_name`	string	No	Table name (default `inferred_table`)
`options`	object	No	Inference options

Options that change the result:

Option	Type	Default	Effect
`prefer_narrow_types`	boolean	true	Integers become `INTEGER` (true) or `BIGINT` (false)
`max_varchar_length`	number	255	Strings longer than this become `TEXT`
`detect_vectors`	boolean	true	Arrays of numbers become `VECTOR(n)`
`detect_json`	boolean	true	Objects become `JSONB` (true) or `JSON` (false)
`detect_unique`	boolean	true	Sets the `unique` flag on columns present in every sample

The request also accepts detect_nullable, detect_primary_key, detect_foreign_keys and suggest_indexes. They do not change the result yet.

Batch Inference

POST /v1/schema/infer/batch

Infer schemas for several tables in one call:

{
  "tables": [
    {"name": "users", "samples": [{"id": 1, "name": "Alice", "email": "alice@example.com"}]},
    {"name": "orders", "samples": [{"id": 1, "user_id": 1, "total": 99.99}]}
  ]
}

The response has schemas (one entry per table) and combined_ddl (all CREATE TABLE statements). Batch DDL does not add NOT NULL or a primary key. The request also accepts detect_relationships. Relationship detection is planned; relationships is always empty today.

Planned Schema Endpoints

These routes are mounted today but return placeholder results. Their full behaviour is planned:

Endpoint	Planned behaviour
`POST /v1/schema/infer/file`	Infer a schema from an uploaded file
`POST /v1/schema/validate`	Validate DDL and suggest fixes
`POST /v1/schema/compare`	Diff two schemas and generate migrations
`POST /v1/schema/optimize`	Suggest schema optimizations
`POST /v1/schema/generate`	Generate a schema from a natural-language description
`GET /v1/schema/templates`, `POST /v1/schema/templates/instantiate`	Schema templates

Current Behaviour

JSON value	SQL type
`true` / `false`	`BOOLEAN`
Integer	`INTEGER` (or `BIGINT` with `prefer_narrow_types: false`)
Decimal	`NUMERIC`
String up to `max_varchar_length`	`VARCHAR(n)`, with n set to twice the sample length and capped at `max_varchar_length`
Longer string	`TEXT`
Array of numbers	`VECTOR(n)`
Other array	`JSON`
Object	`JSONB`
`null`	Marks the column nullable in `columns`

Review these points before you run the DDL:

When samples disagree, the column takes the type of the first non-null sample value. Pass representative samples.
Date and timestamp strings are typed as VARCHAR. Change them to DATE or TIMESTAMP yourself.
The single-table DDL marks every column NOT NULL, including columns flagged nullable, and always adds PRIMARY KEY (id). Edit the statement if your data has no id column or has optional fields.
confidence is a fixed 0.85, and indexes and constraints are empty.

For AI Agents

An agent can infer a draft, review it, and run it on a branch so that a bad guess never touches main:

import requests

BASE = "http://127.0.0.1:8080/v1"
HEADERS = {"Authorization": f"Bearer {TOKEN}"}

def create_table_from_samples(samples, table_name, branch="main"):
    schema = requests.post(f"{BASE}/schema/infer", headers=HEADERS,
                           json={"samples": samples, "table_name": table_name}).json()["data"]
    ddl = schema["ddl"]
    # Review step: adjust NOT NULL, the primary key and date columns before running
    requests.post(f"{BASE}/branches/{branch}/execute", headers=HEADERS,
                  json={"sql": ddl}).raise_for_status()
    return schema

Error Handling

Errors use the REST error format: an HTTP status plus {"error": "<kind>", "message": "<text>"}.

Status	`error`	Cause
400	`BadRequest`	`samples` is empty (`"At least one sample is required"`)
401	`Unauthorized`	Missing, expired or invalid token

Next Steps

Questions? API Reference | Join Discord