Phase 2: test bed, storage, providers, policies and adapter SDK v2 (design)

Principles

The Phase 1 principles still hold, and the owner restated them for Phase 2:

• No users yet, so design it correctly, not compatibly. No shims, no aliases, no deprecation periods. Where a 0.5 shape is wrong, 0.6 replaces it.
• Never over-engineer. Ship what 0.6 needs. Anything that can wait, does.
• Prefer extension points over built-ins. A small interface and a way to register your own beats another built-in we have to maintain.
• No database-specific code outside adapters. The plan says what; the adapter says how.
• The package is the product. The CLI, nl2sql-api and the playground use the public API only.

One more rule comes from how Phase 2 was ordered: nothing about a real database is designed from memory. Until now every test ran against SQLite, DuckDB and mocks. Phase 2 builds a test bed with real PostgreSQL, MySQL and SQL Server first, and the adapter work is proven on it.

Owner decisions

The owner answered every question this design raised, and confirmed the choices it made on its own. Each answer is written into the section it affects.

Where Phase 1 left us

1. Goals and non-goals

What ships in 0.6

Not in Phase 2

2. Order of work

Phase 2 runs in two waves, in this order because until now there was no real database to test against.

flowchart LR
  subgraph A["Wave A"]
    T["Track 1: the real-database test bed<br/>one compose file, CI, GCP VM, realdb marker"]
    N["Track 2: work that needs no real database<br/>stores, providers, policies"]
  end
  T --> B["Wave B: adapter SDK v2<br/>built and proven on the test bed"]
  N --> R["Release 0.6"]
  B --> R

• Wave A, track 1 builds the test bed. Nothing else depends on the GCP part; the CI containers and fixtures are what Wave B needs.
• Wave A, track 2 changes code that SQLite, DuckDB and the fake LLM can test: storage, LLM and embeddings providers, and policies.
• Wave B starts once track 1's CI job is merged. It may overlap the end of track 2.
• Release 0.6 follows the last Wave B pull request.

3. The real-database test bed

What it is

One file, testbed/docker-compose.yml, defines the whole test bed. The same file runs in three places (owner decision 2):

The generated schema is loaded on all three servers everywhere, because IndexScope and catalog row estimates are per adapter, and one compose file means one behaviour.

There is no Cloud SQL. An earlier draft of this page used three Cloud SQL instances; a stopped Cloud SQL instance still bills its public IPv4 address, and a VM running the same compose file is cheaper, simpler and identical to CI.

The sample databases and their licences

The compose file's samples service downloads each sample from a pinned URL and checks its SHA-256. Nothing is committed to the repository, so we never redistribute the files. If we ever vendor one, its licence file goes beside it.

The server images have their own terms. The SQL Server image runs the free Developer edition, which needs ACCEPT_EULA=Y and is licensed for development and testing, which is all we do with it. The PostgreSQL and MySQL images are used as they are.

The generated wide schema

testbed/wide_schema.py --dialect postgres|mysql|tsql --seed 7 writes one SQL file. The same seed always gives the same schema. It has:

The big tables are filled on the server with a series function (generate_series, a recursive CTE, or a numbers table), so the file stays small. That SQL lives in the test bed, which is test code, not engine code.

The compose file

docker compose -f testbed/docker-compose.yml up -d --wait starts and loads everything. Loading happens once per data volume, so a second up is quick.

Every service has a healthcheck, so --wait returns only when every server is loaded. Each server also gets a login nl2sql_reader with read grants on its sample database only; the conformance kit uses it for the permission stage of test_connection.

The file reads four variables. The bind address has a default; the passwords do not, so no password is written in the repository:

The ports avoid a clash with a database already installed locally.

Local use

python testbed/passwords.py > testbed/.env               # once
docker compose -f testbed/docker-compose.yml up -d --wait
set -a; . testbed/.env; set +a
REALDB=compose pytest -m realdb
docker compose -f testbed/docker-compose.yml down        # add -v to drop the data

CI: the realdb workflow

A new workflow, .github/workflows/realdb.yml, with three jobs (owner decision 2):

The paths that make realdb run:

packages/sdk/**
packages/nl2sql/src/nl2sql/adapters/**
packages/nl2sql/src/nl2sql/indexing/**
packages/nl2sql/src/nl2sql/storage/**        (and schema/** until S3 moves it)
packages/nl2sql/tests/realdb/**
testbed/**
.github/workflows/realdb.yml

REALDB_REQUIRED=1 turns a skipped realdb test into a failure: a green run that skipped the real databases proves nothing. The unit job in test.yml changes to pytest -m "not integration and not realdb". The containers run on GitHub's runners; no cloud credentials are involved.

The realdb marker and fixtures

pytest.ini registers the marker:

realdb: needs a real PostgreSQL, MySQL or SQL Server (the test bed). Start
    testbed/docker-compose.yml and set REALDB=compose, or run it through
    scripts/testbed.py for the GCP VM.

The fixtures live in packages/nl2sql/tests/realdb/conftest.py:

@pytest.fixture(params=["postgres", "mysql", "mssql"])
def server(request) -> RealServer: ...            # skips loudly, or fails under REALDB_REQUIRED

def datasource(server, database="pagila", **index) -> Datasource:
    """A Datasource for one test-bed database, built from the server's URL."""

@pytest.fixture
def engine_on(server, tmp_path) -> Callable[..., NL2SQL]: ...   # an engine on that server, fake LLM

The skip message names the fix: "realdb: no PostgreSQL. Run docker compose -f testbed/docker-compose.yml up -d --wait and set REALDB=compose, or run python scripts/testbed.py run -- pytest -m realdb."

Test code may name dialects and use SQLAlchemy directly. The boundary tests cover packages/*/src, not the tests.

The GCP VM

One small VM, nl2sql-testbed in nl2sql-testbed-261014 (zone us-central1-a), runs the same compose file (owner decision 3). It is started for a session and stopped after it.

There are no GitHub-to-GCP credentials. The owner, or an agent the owner asks, runs the VM by hand.

scripts/testbed.py is Python, not a shell script, because the owner works on Windows and CI runs Linux. It calls gcloud (found with shutil.which, so gcloud.cmd works) and never writes its own process's environment.

Passwords are read with gcloud secrets versions access latest into memory, and are never written to a file or printed.

The nightly backstop. If testbed.py is killed hard, or the VM is started by hand, it would keep billing. One Cloud Scheduler job calls the Compute Engine API directly at 03:00 UTC, with an OAuth token for a service account that may only stop instances in this project: POST https://compute.googleapis.com/compute/v1/projects/nl2sql-testbed-261014/zones/us-central1-a/instances/nl2sql-testbed/stop. Stopping a stopped VM does nothing, so the job needs no logic, and one job is inside Cloud Scheduler's free tier.

docs/testing/test-bed.md gives the commands for the owner to create the VM, the firewall rule and the scheduler job once. Nothing in the repository creates cloud resources by itself.

Cost controls

So even a daily session stays well inside $10.

4. Storage: four stores

The config

Every sub-block is optional. A config with only path stays valid and means the built-ins below.

# storage.yaml
path: .nl2sql
index:   { type: chroma }                       # <path>/index/
state:   { type: sqlite }                       # <path>/state.db
traces:  { type: files, max_age_days: 14 }      # <path>/traces/
results: { type: local, max_age_days: 7 }       # <path>/results/

A plugin store takes its own keys, validated by its config_model:

index: { type: qdrant, url: "http://qdrant:6333", api_key: "${env:QDRANT_KEY}" }

The same in Python, by name or with an object:

from nl2sql import Storage, StoreConfig

Storage(path=".nl2sql")
Storage(path=".nl2sql", traces=StoreConfig(type="files", max_age_days=30))
Storage(path=".nl2sql", index=MyQdrantIndex(url="http://qdrant:6333"))   # any IndexStore object

StoreConfig is like SecretProviderConfig: type, plus the store's own keys, which keep their references until the store is built. max_age_days is a key of the files and local stores, not of every store.

Names. Store names share one entry point group, nl2sql.stores (owner decision), so they are unique across the four slots. Each store class says which slot it fills (slot = "index"), and the engine checks it: "chroma is an index store; results needs a results store. Installed: local."

The interfaces

They live in nl2sql_sdk.stores, so they need only pydantic and the standard library. Each has interface_version = 1, a slot, a config_model and from_config(cfg, *, folder: Path), where folder is storage.path.

class Entry(BaseModel):          # one index entry
    id: str
    text: str
    vector: list[float]
    metadata: dict[str, str | int | float | bool]

class Filter(BaseModel):         # what the engine filters on today, and nothing more
    eq: dict[str, Scalar] = {}
    ne: dict[str, Scalar] = {}
    in_: dict[str, list[Scalar]] = Field({}, alias="in")

class BuildPointer(BaseModel):   # which build of one datasource is live
    build_id: str
    schema_version: str
    built_at: datetime
    curated_hash: str | None = None

class IndexStore(Protocol):
    slot: ClassVar[Literal["index"]]
    def open(self, namespace: str, dimension: int) -> None: ...
    def add(self, namespace: str, entries: Sequence[Entry]) -> None: ...
    def query(self, namespace: str, vector: Sequence[float], k: int, where: Filter | None = None,
              with_vectors: bool = False) -> list[Hit]: ...        # MMR needs the vectors
    def get(self, namespace: str, where: Filter, with_vectors: bool = False) -> list[Entry]: ...
    def delete(self, namespace: str, where: Filter) -> int: ...
    def pointers(self, namespace: str) -> dict[str, BuildPointer]: ...        # by datasource id
    def set_pointers(self, namespace: str, updates: dict[str, BuildPointer | None]) -> None: ...  # atomic
    def namespaces(self) -> list[str]: ...
    def drop(self, namespace: str) -> None: ...
    def close(self) -> None: ...

class StateStore(Protocol):
    slot: ClassVar[Literal["state"]]
    def migrate(self) -> list[str]: ...                       # applied migration names
    def put_snapshot(self, snapshot: SchemaSnapshot) -> str: ...      # returns schema_version
    def get_snapshot(self, datasource_id: str, schema_version: str) -> SchemaSnapshot | None: ...
    def list_versions(self, datasource_id: str) -> list[str]: ...              # newest first (S3)
    def keep_snapshots(self, datasource_id: str, keep: set[str]) -> int: ...   # deletes the rest
    def get_plan(self, key: str, datasource_id: str, schema_version: str) -> str | None: ...
    def put_plan(self, key: str, datasource_id: str, schema_version: str, plan_json: str) -> None: ...
    def clear_plans(self) -> int: ...
    def add_feedback(self, row: dict) -> dict: ...
    def list_feedback(self, limit: int | None = None) -> list[dict]: ...
    def clear_feedback(self) -> int: ...
    def lock(self, name: str, ttl_s: int) -> ContextManager[None]: ...
    def close(self) -> None: ...

class TraceStore(Protocol):
    slot: ClassVar[Literal["traces"]]
    def write(self, trace_id: str, document: dict) -> None: ...
    def get(self, trace_id: str) -> dict | None: ...
    def purge(self, older_than: datetime) -> int: ...

class ResultStore(Protocol):     # bytes only: the engine keeps writing Parquet
    slot: ClassVar[Literal["results"]]
    def write(self, key: str, chunks: Iterable[bytes]) -> int: ...
    def open(self, key: str) -> BinaryIO: ...
    def exists(self, key: str) -> bool: ...
    def purge(self, older_than: datetime) -> int: ...

Design notes:

• The engine embeds. Today Chroma's LangChain wrapper embeds documents itself. In 0.6 the engine embeds with its own embedder and hands the store vectors, so a store never depends on LangChain, and MMR runs in the engine on the vectors query returns.
• No staging or rename in the interface. A rebuild writes a new build beside the live one and switches a pointer, so no backend has to rename anything.
• The filter is small on purpose. eq, ne and in (combined with "and") are all the engine uses today. Each backend translates them.
• ResultStore stores bytes. The engine still writes Parquet and the manifest; the store only puts and gets them. A remote store (S3, ADLS) then needs four methods and no Polars knowledge.
• The lock is a lease in the state store: in SQLite, a locks row taken in a BEGIN IMMEDIATE transaction, with an expiry so a crashed holder cannot block forever. It makes nl2sql index safe to run while nl2sql-api serves the same storage.path, and needs no new dependency.

The build pointer is the commit (bug B5)

The bug. Indexing registers the schema snapshot in the state store before it writes the index (indexing/orchestrator.py), and the resolver, retriever, curated matcher and datasources.schema() read the state store's latest snapshot. If the index build fails, the "latest" snapshot describes a schema the live index was never built from. Today this only produces a warning (settings.schema.version_mismatch).

The fix. A snapshot is an immutable row keyed by schema_version. The index's BuildPointer records the schema_version the build was made from, and every reader resolves "live" through the pointer. Switching the pointer is the commit.

sequenceDiagram
  participant I as Indexing
  participant S as State store
  participant X as Index store
  participant R as A run
  I->>S: put_snapshot(snapshot) returns v42 (nothing reads it yet)
  I->>X: add(entries tagged build b7)
  I->>X: set_pointers(sales to build b7 and schema v42)
  Note over X: the commit
  R->>X: pointers() once, at the start of the run
  R->>S: get_snapshot(sales, v42)
  R->>X: query(where build_id = b7)

• A failed build leaves an unreferenced snapshot and entries that no pointer names. The next build of that datasource deletes them.
• get_latest_snapshot and get_latest_version are deleted, so no reader can ask the wrong question. settings.schema.version_mismatch is deleted, because the mismatch can no longer happen.
• keep_snapshots keeps every version a pointer names, plus the newest settings.schema.max_versions.
• It works for any pair of backends (Chroma and SQLite today, pgvector and PostgreSQL later) without a transaction across the two.

A run pins the pointers

A run reads pointers() once, when it starts, the way it already pins its LLM set. Every step of the run uses that build and that schema version. To make that safe, a new build deletes only the builds before the previous one, so an in-flight run never loses its entries. That replaces switch_guard and the playground's RunGate for index switches.

One namespace per embedding model

The index namespace is nl2sql__<model slug>__<dimension>, for example nl2sql__local-all-minilm-l6-v2__384. The slug and dimension come from the embedder (see section 5).

• Changing the embedding model opens a new, empty namespace. check() reports the index as missing for the new model, with "run nl2sql index". EmbeddingModelMismatchError and the dimension check are deleted.
• Switching back to an earlier model finds its namespace still built.
• engine.index.prune() and nl2sql index prune drop every namespace except the current one, including 0.5's nl2sql_store collection, which 0.6 never reads.
• A full rebuild no longer needs a staging collection: index.build() with no ids builds every datasource in the current namespace. The full argument, create_staging, promote and discard are deleted.

Migrations and retention

• Migrations. The SQLite state store keeps a nl2sql_meta table and applies numbered migrations at startup. Migration 1 creates 0.5's three tables if they do not exist, so a 0.5 state.db is adopted as it is. The feedback store's ad-hoc ALTER TABLE statements move into migrations.
• Retention. files traces and local results take max_age_days (defaults 14 and 7). engine.storage.purge() deletes what is older and returns the counts. nl2sql storage purge calls it, and nl2sql-api calls it at startup and once a day. Feedback and snapshots have no age limit: feedback is cleared by hand, and snapshots by keep_snapshots.

The facade

What changes in existing code

A new boundary test: only nl2sql/storage/ imports chromadb or sqlite3.

5. LLM and embeddings providers

The LLM config

Phase 1's llm section stays flat. Six fields are added to LLM and to AgentLLM, and an agent inherits each one it does not set:

# llm.yaml
provider: openai_compatible
base_url: http://vllm:8000/v1
model: qwen3-32b
api_key: "${env:VLLM_KEY}"          # optional for this provider
structured_output: json_schema      # tool_calling | json_schema | json_mode
parse_retries: 1
max_tokens: 8000
timeout_s: 60
max_retries: 2
headers: { X-Team: analytics }
agents:
  ast_planner: { provider: anthropic, model: claude-opus-5, max_tokens: 16000 }
  answer_synthesizer:
    provider: openai_compatible     # Azure OpenAI's v1 endpoint
    base_url: https://my-resource.openai.azure.com/openai/v1/
    api_key: "${env:AZURE_OPENAI_API_KEY}"
    model: gpt-5-4-prod             # the Azure deployment name

from nl2sql import LLM, AgentLLM

LLM(provider="openai_compatible", base_url="http://vllm:8000/v1", model="qwen3-32b",
    structured_output="json_schema",
    agents={"ast_planner": AgentLLM(provider="anthropic", model="claude-opus-5", max_tokens=16000)})

The client cache key already covers every field of the agent model, so these need no change there.

Providers

The built-ins stay minimal (owner decision 7):

• openai_compatible is any server with an OpenAI-style chat API: Azure OpenAI's v1 endpoint (https://<resource>.openai.azure.com/openai/v1/, with model as the deployment name), vLLM, LiteLLM, LM Studio, llama.cpp's server or a company gateway. Without api_key it sends a placeholder, because the OpenAI client refuses to start without one. Its default is json_schema because local servers support schema-guided decoding more often than tool calls; set tool_calling for Azure.
• openrouter and ollama stay, as named presets of openai_compatible: a PROVIDER_PRESETS row each, with a fixed base_url, a key variable (or a placeholder key for Ollama) and a default structured_output, and no code path of their own. nl2sql init --provider openrouter and the demo's sk-or- key detection keep working. PROVIDER_PRESETS stays the one table of key variables.
• Anything else is a plugin: a class implementing LLMProvider, registered with registry.add_llm or the nl2sql.llms entry point group. An Azure provider with Entra ID sign-in, Bedrock or Gemini would each be one.

Structured output, and parse-and-retry

Every structured call already goes through one function, structured() in llm/wires/__init__.py. Phase 2 changes it:

1. Ask with the agent's structured_output method. For json_mode, the schema is added to the prompt, since JSON mode alone does not carry it.
2. If the answer does not parse (OutputParserException, a pydantic ValidationError, no tool call, or None), ask again, up to parse_retries times. Each retry adds the model's answer and the validation errors to the messages: "Your answer did not match the schema: steps[0].op: field required. Answer again with only PlanModel."
3. If it still fails, raise StructuredOutputError(agent, schema, attempts). provider_error() maps it to a new ErrorCode.MODEL_OUTPUT_INVALID, whose message names the agent and suggests another structured_output or a stronger model.

The method is not switched automatically: the agent's setting is what runs. Retries are counted in the run's trace and in token usage. A streamed answer that fails to parse is retried without streaming.

Embeddings

# embeddings.yaml
provider: openai_compatible
base_url: http://ollama:11434/v1
model: nomic-embed-text
dimension: 768            # optional: otherwise asked of the provider, or probed with one call

Model and dimension. The engine needs both to name the index namespace. The embedder reports them as EmbeddingInfo(provider, model, dimension). When the dimension is unknown, the engine embeds the text "dimension probe" once, at the first index build or query, and remembers the answer for the engine's life. Writing dimension: skips that one paid call.

Interface changes

As in Phase 1, the engine accepts exactly the version it was built for.

What changes in existing code

6. Policies, step 1

The config

# policies.yaml
default_role: viewer
admin_roles: [admin]
refusal_names_tables: false
roles:
  admin:
    description: Everything
    allow: ["*"]
  analyst:
    description: Sales, except the audit schema and PII tables
    allow: [sales]
    deny:  [sales.audit.*, "sales.*.*_pii"]
  viewer:
    description: Reporting views and support tickets
    allow: [sales.reporting.*, support.public.tickets]
check:                                 # optional: a registered PolicyCheck
  type: corp_iam
  url: "${env:IAM_URL}"

from nl2sql import Policies, Role

Policies(default_role="viewer", admin_roles=["admin"],
         roles={"analyst": Role(description="Sales", allow=["sales"], deny=["sales.audit.*"])},
         check=MyIamCheck())             # an object, or a {type: ...} block

allow_all, default_role, admin_roles and refusal_names_tables are Phase 1's and do not change. nl2sql-api already reads admin_roles for its admin routes. Role.allowed_datasources and Role.allowed_tables are replaced by allow and deny.

Pattern rules

One Scope

@dataclass(frozen=True)
class Scope:
    datasource_id: str
    def reaches_datasource(self) -> bool: ...
    def allows(self, schema: str | None, table: str) -> bool: ...
    def rule_for(self, schema: str | None, table: str) -> str | None: ...   # the deny or missing allow, for the log

PolicyEngine.scope(user, datasource_id) -> Scope builds it from the roles, allow_all, default_role and the optional check. A run builds each datasource's Scope once and keeps it, so every step decides the same way.

table_allowed, RBAC, RolePolicy and default_schema() in auth/ are deleted. nl2sql/auth/ becomes nl2sql/policies/.

Denied tables are hidden from the planner

The owner decided (D7) that denied tables are hidden by default. In 0.5 the planner sees every table's name, columns and keys ("structure yes, data no"). In 0.6 the retriever filters entries through the Scope before it takes the top k, fetching more candidates when needed, so a schema with many denied tables still fills the prompt with readable ones.

What the user sees changes: a question that needs only denied tables gets "no data for that question" from the planner rather than a refusal. A plan that names a hidden table anyway is still refused by the validator. With refusal_names_tables: true the refusal names the table, for debugging.

The PolicyCheck extension point

# nl2sql_sdk.policy
class UserContext(BaseModel):          # moves here from nl2sql.auth.models
    user_id: str | None = None
    tenant_id: str | None = None
    roles: list[str] = []

class Grant(BaseModel):
    allow: list[str] = []              # the same patterns as policies.yaml
    deny: list[str] = []

class PolicyCheck(Protocol):
    interface_version: ClassVar[int]   # 1
    config_model: ClassVar[type[BaseModel]]
    @classmethod
    def from_config(cls, cfg: BaseModel) -> "PolicyCheck": ...
    def grant(self, user: UserContext, datasource_id: str) -> Grant: ...

The docs page gets a 30-line example that calls an HTTP service.

7. Adapter SDK v2 (Wave B)

Wave B is built on the test bed. Every behaviour below that touches a database has a realdb test.

The contract

# nl2sql_sdk (core: pydantic and the standard library)
class ConnectionModel(BaseModel):
    model_config = ConfigDict(extra="forbid", hide_input_in_errors=True)
    # SecretStr fields are secrets. Field(title, description, examples) and
    # json_schema_extra={"group": "advanced"} drive the generated form.

class Limits(BaseModel):               # moves here from nl2sql.config
    row_limit: PositiveInt | None = None
    timeout_ms: PositiveInt | None = None
    max_bytes: PositiveInt | None = None

class IndexScope(BaseModel):           # a datasource's `index:` block
    include_schemas: list[str] = []    # globs; empty means every non-system schema
    exclude_schemas: list[str] = []
    include_tables: list[str] = []     # "schema.table" globs
    exclude_tables: list[str] = []
    exclude_columns: list[str] = []    # "schema.table.column" globs
    profile: Literal["catalog", "stats"] = "stats"
    sample_values: bool = False
    max_profile_rows: PositiveInt = 1_000_000
    def wants_table(self, ref: TableRef) -> bool: ...    # one matcher for every adapter
    def wants_column(self, ref: TableRef, column: str) -> bool: ...

class ConnectionCheck(BaseModel):
    ok: bool
    stage: Literal["config", "driver", "network", "auth", "database", "permission"] | None = None
    message: str | None = None
    fix: str | None = None
    server_version: str | None = None

class IndexIssue(BaseModel):           # a table or column indexed without something
    table: str
    column: str | None = None
    stage: Literal["reflect", "estimate", "profile", "values"]
    message: str

class DatasourceCapability(str, Enum):
    SUPPORTS_SQL; SUPPORTS_BOUND_PARAMETERS
    ENFORCES_READ_ONLY                 # new: the session cannot write
    ENFORCES_TIMEOUT                   # new: a statement over limits.timeout_ms is stopped by the server or driver
    # SUPPORTS_REST is removed: nothing uses it

class DatasourceAdapter(Protocol):     # replaces DatasourceAdapterProtocol
    interface_version: ClassVar[int]   # 2
    title: ClassVar[str]               # "PostgreSQL", for the form
    connection_model: ClassVar[type[ConnectionModel]]
    driver_modules: ClassVar[tuple[str, ...]]
    install_hint: ClassVar[str]

    def __init__(self, datasource_id: str, connection: ConnectionModel, limits: Limits,
                 *, folder: Path | None = None) -> None: ...      # never connects
    def capabilities(self) -> set[DatasourceCapability]: ...
    def get_dialect(self) -> str: ...                  # a sqlglot name, unchanged
    def default_schema(self) -> str | None: ...
    def test_connection(self) -> ConnectionCheck: ...
    def fetch_schema_snapshot(self, scope: IndexScope,
                              on_table: Callable[[str], None] | None = None) -> SchemaSnapshot: ...
    def execute(self, request: AdapterRequest) -> ResultFrame: ...
    def close(self) -> None: ...

SchemaSnapshot gains issues: list[IndexIssue], and TableMetadata gains row_count_source: "catalog" | "exact" | None. The optional StreamingAdapterProtocol and SqlRenderingAdapterProtocol do not change.

What goes from version 1: connect() (an adapter connects on first use, and test_connection connects), datasource_engine_type, connection_args, statement_timeout_ms, row_limit and max_bytes (now limits), and the unused dry_run, explain and cost_estimate.

ConnectionModel and the generated form

Each built-in declares its model publicly. For example:

class PostgresConnection(SQLAlchemyConnection):     # SQLAlchemyConnection adds `pool`
    host: str = Field(title="Host", examples=["db.internal"])
    port: int = Field(5432, title="Port")
    database: str = Field(title="Database")
    user: str = Field(title="User")
    password: SecretStr = Field(title="Password")
    sslmode: Literal["disable", "prefer", "require", "verify-full"] = Field(
        "prefer", json_schema_extra={"group": "advanced"})

• Strict. extra="forbid": hostname: is now an error with "did you mean 'host'", where 0.5 silently ignored it (bug B18).
• Validated at build. The engine resolves the references in connection, then validates against the adapter's model. Python callers may pass a dict or a model instance: Datasource(connection=PostgresConnection(...)).
• The form. connection_model.model_json_schema() is the form. A SecretStr field is "format": "password", "writeOnly": true, which a form renders as a password input. A field in the advanced group is folded away. A new adapter gets a form for nothing.
• Pool. SQLAlchemyConnection adds pool: {size: 5, max_overflow: 0, timeout_s: 30, recycle_s: 1800}. With no overflow, the pool size is also the cap on concurrent queries against that database, so NL2SQL cannot overload a production server.

IndexScope: what gets indexed

# datasources/sales.yaml
type: postgres
connection: { host: db.internal, database: sales, user: nl2sql, password: "${env:SALES_DB_PASSWORD}" }
index:
  include_schemas: [public, reporting]
  exclude_tables: ["*.*_audit", "public.tmp_*"]
  exclude_columns: ["public.customers.email"]
  profile: stats
  sample_values: false

The filter runs before any per-table query, so an excluded schema costs nothing. The scope is part of the snapshot's fingerprint, so changing it makes a new schema_version.

Profiling and row estimates

The SQLAlchemy base changes how it reads a schema:

• Reflect once. inspector.get_multi_columns, get_multi_pk_constraint and get_multi_foreign_keys per schema, instead of reflecting the whole database twice table by table. The wide schema measures it: the target is a catalog snapshot of 1,200 PostgreSQL tables in under 60 seconds in CI.
• Catalog row estimates. An overridable row_estimates(conn, schema) -> dict[str, int] | None: PostgreSQL pg_class.reltuples, MySQL information_schema.tables.table_rows, SQL Server sys.partitions, DuckDB duckdb_tables().estimated_size. SQLite returns None, and gets COUNT(*) under profile: stats. Nothing counts every row of every table any more.
• Type-aware statistics. An overridable column_profile(sa_type) -> ColumnProfile(min_max, distinct, values). No MIN/MAX on boolean, uuid, json, binary, xml or arrays; values only for text and only with sample_values. This makes 0.4.1's skip list a public rule.
• Isolation. Each column's statistics query is isolated as today (a rollback after a failure), and a failure becomes an IndexIssue instead of a log line, so index.build() can report it.

Read-only and timeouts, honestly

Each adapter does its session setup in one place, session_setup(dbapi_conn), run on every new pooled connection. A capability is declared only when the conformance kit proves it on a real server.

There is no read_only setting: the engine never writes, so every adapter always makes its session read-only when it can. When it cannot (SQL Server), engine.check() adds a warning to that datasource's status: "sales (mssql): this adapter cannot make the session read-only. Connect with a login that has only db_datareader." The typed plan and the validator still block every write. This is owner decision 4.

test_connection: which stage failed

Mapping a driver error to a stage is the adapter's job: an overridable classify(exc) on the SQLAlchemy base reads the SQLSTATE class (08 connection, 28 authorization, 3D database) and each adapter adds its own codes (MySQL 1045 and 1049, SQL Server 18456 and 4060). check()'s DatasourceStatus.stage is now one of these, where 0.5 always said connect.

The SQLAlchemy base, public

BaseSQLAlchemyAdapter moves from the engine to nl2sql_sdk.sqlalchemy, installed with nl2sql-sdk[sqlalchemy], as SQLAlchemyAdapter with SQLAlchemyConnection. The five built-ins import it from there.

• The SDK core still imports only pydantic and the standard library. The sqlalchemy subpackage may also import SQLAlchemy and sqlglot, and the boundary test says so.
• Its overridable methods are the whole extension surface: url(connection) (with sqlalchemy.engine.URL.create, so credentials are escaped), session_setup, row_estimates, column_profile, classify, system_schemas and render_sql.
• The docs' "build an adapter" page shows a complete adapter in about 40 lines.

The conformance kit

nl2sql_sdk.testing, installed with nl2sql-sdk[testing] (adds pytest and sqlglot), ships AdapterConformance, a test class an adapter author subclasses. The author provides three fixtures:

What it checks:

The kit proves itself on the five built-ins: SQLite and DuckDB in the unit job, PostgreSQL, MySQL and SQL Server in the realdb job.

engine.datasources: the methods behind Connect a database

These are what the Phase 4 form, the terminal app and nl2sql datasources add will call. Saving the result to a file stays the calling app's job, as Phase 1 decided.

What changes in existing code

8. The registry after Phase 2

New on Registry: add_store, add_policy_check, and the store and policy_check decorators. Hooks, observers, scorers and knowledge sources stay in later phases.

9. Events and hooks

Phase 2 adds no event bus and no hooks: nothing in 0.6 would consume them. Where Phase 3 will emit an event, Phase 2 returns the data instead, so Phase 3 only has to publish it.

10. Bugs Phase 2 fixes

The rest of the original list: B1 to B4, B6 to B12 and B15 to B17 were fixed in 0.4.1 and 0.5. B13 (results readable by anyone with the request id) is a later policy step, and B14 (OpenTelemetry only on the CLI path) is Phase 3.

11. Boundary and invariant changes

12. PR plan

Each PR is written test-first and updates the docs it affects in the same PR. At most four PRs are open at once.

flowchart TD
  D["0. This design"] --> T1["T1. Test bed: compose file, realdb marker, CI workflow"]
  D --> S1["S1. Store interfaces, config, registry kind"]
  D --> E1["E1. Embeddings providers v2"]
  D --> P1["P1. Policies: globs, Scope, hidden tables"]
  T1 --> T2["T2. GCP VM: testbed.py, IAP tunnels, nightly stop"]
  S1 --> S2["S2. Index store, pointer as commit (B5), namespaces"]
  E1 --> S2
  D --> L1["L1. LLM providers and agent fields"]
  P1 --> P2["P2. PolicyCheck extension point"]
  S2 --> S3["S3. State store, migrations, lock"]
  S1 --> S4["S4. Trace and result stores, retention"]
  L1 --> L2["L2. Parse-and-retry"]
  T1 --> B1["B1. SDK v2 contract"]
  B1 --> B2["B2. SQLAlchemy base in the SDK, IndexScope, profiling"]
  B1 --> B3["B3. Read-only, timeouts, test_connection stages"]
  B1 --> B5["B5. datasources.types, test, add"]
  B2 --> B4["B4. Conformance kit on five adapters"]
  B3 --> B4
  B4 --> R["Release 0.6"]
  S3 --> R
  S4 --> R
  L2 --> R
  P2 --> R
  T2 --> R
  B5 --> R

The waves, with at most four PRs at once:

13. Release 0.6

• release-please opens the release PR as usual. Releasing is the owner's act (see Releasing); no agent merges it.
• docs/releases/0.6.0.md is written with the release-notes process. Its "breaking changes" table covers: the storage sub-blocks and the new index namespace (run nl2sql index after upgrading), the policy pattern rules (allow/deny replace allowed_datasources/allowed_tables; two-part patterns are rejected), denied tables hidden from the planner, sample_values off by default, strict connection keys, adapter interface version 2, LLM and embeddings provider interface version 2, and the deleted index.build(full=..., switch_guard=...) arguments and settings.schema.version_mismatch.
• Before tagging, the realdb workflow is run on demand on main, and the owner runs python scripts/testbed.py run -- pytest -m realdb once on the GCP VM. Both results go in the release notes.

14. Open questions

None. The owner answered all seven and confirmed the design's own choices; see Owner decisions.