openai provider for stackql

This repository generates and documents the openai provider for StackQL, enabling SQL-based query and provisioning operations against the OpenAI platform surface available to standard API keys - models, files (metadata), fine-tuning (jobs, events, checkpoints, checkpoint permissions), batches, vector stores (stores, files, file batches), assistants/threads/runs (deprecation-labelled), evals, conversations, uploads (metadata), containers and skills. The provider is built using the @stackql/provider-utils package.

Design Principles

This is a ground-up replacement of the v1 openai provider, generated on the current toolchain from the vendor's published OpenAPI specification. Key points:

• Pinned vendor spec - the single source artifact is openapi.yaml from openai/openai-openapi (branch main), pinned by commit and hash in provider-dev/config/spec_pin.json. Refreshes are reviewed diffs, never silent regenerations (npm run fetch-spec -- --check detects drift).
• Scope boundaries - the organization/admin surface (/organization/... - usage, costs, projects, users, invites, audit logs, admin keys) uses a separate admin key class and lives in the sibling openai_admin provider. Inference invocation (chat/completions, responses, embeddings, images, audio, moderations, realtime, video generation) is the data plane and out of scope per the standing model-provider posture. Batches are in scope - an async job control surface, not an invocation. Binary transfer (file content, upload parts) is excluded; file and upload metadata is in scope. All exclusions are deterministic, reason-coded rules in provider-dev/scripts/clean_specs.mjs with the removal list in provider-dev/config/filter_report.csv.
• Fixed server, bearer auth - https://api.openai.com/v1 with Authorization: Bearer $OPENAI_API_KEY.
• Async jobs are the core surface - fine-tuning jobs, batches, vector store file batches, uploads: INSERT creates, SELECT polls, EXEC cancels; events and checkpoints are child SELECT resources.
• The OpenAI list envelope - list responses wrap as {"object": "list", "data": [...], "first_id": ..., "last_id": ..., "has_more": bool}; the provider unwraps $.data and drives pagination with the derived cursor (after fed from the previous page's last_id).
• Deprecation is a build input - the Assistants family carries the vendor's migration-to-Responses deprecation; the labels are carried into the provider docs and the drift CI watches the timeline.

Breaking Changes from the v1 Provider

This rebuild replaces the published v1 openai provider. Every v1 resource is dispositioned below (generated from provider-dev/config/predecessor_dispositions.csv: 94 v1 methods -> 42 carried, 13 renamed, 39 retired). The old provider version remains available in the registry for pinning.

Renamed resources (old -> new):

v1 resource	This rebuild
openai.batch.batches	openai.batches.batches
openai.fine_tuning.job_checkpoints	openai.fine_tuning.checkpoints
openai.vector_stores.files_in_vector_store_batches	openai.vector_stores.file_batch_files
openai.vector_stores.vector_store_file_batches	openai.vector_stores.file_batches
openai.vector_stores.vector_store_files	openai.vector_stores.files

Retired resources (reason-coded):

• Binary transfer - out of scope per the standing exclusions (metadata remains queryable): openai.uploads.upload_parts
• Inference invocation (data plane) - out of scope per the standing model-provider posture; use the vendor SDKs for invocation: openai.audio.speeches, openai.audio.transcriptions, openai.audio.translations, openai.chat.completions, openai.completions.completions, openai.embeddings.embeddings, openai.images.image_edits, openai.images.image_variations, openai.images.images, openai.moderations.moderations
• Organization/admin surface (separate admin key class) - moved to the sibling openai_admin provider: openai.audit_logs.audit_logs, openai.invites.invites, openai.invites.users, openai.projects.project_api_keys, openai.projects.project_service_accounts, openai.projects.project_users, openai.projects.projects, openai.users.users

Retired methods on surviving resources:

• openai.files.files.create_file - Multipart binary upload body - not expressible; file content uploads are out of scope (metadata remains queryable)
• openai.files.files.download_file - Binary transfer - out of scope per the standing exclusions (metadata remains queryable)

Method naming - v1 operation-derived method names become resource-scoped names (list_batches -> list, retrieve_batch -> get, create_fine_tuning_job -> create, ...). 54 of the 55 surviving methods are renamed this way; the mapping is one-to-one per resource and recorded in the dispositions CSV.

Build Pipeline

Deterministic and re-runnable throughout; every script validates and fails without writing. Node.js 20+ required.

Stages 0-4 are implemented and run (fetch/filter, split, mappings, normalize, generate); the generated provider resolves in stackql (offline SHOW/DESCRIBE verified). Stage 5 has a live pystackql smoke test (tests/smoke_test.py); the meta-route and mock integration layers, and stages 6-7 (publish, docs), are documented as the intended invocations. The bin/ wrappers (normalize.mjs, generate-provider.mjs) invoke the current @stackql/provider-utils directly; generate-provider.sh is a thin passthrough so no flag is dropped by the wrapper.

0. Fetch, pin and filter the spec; inventory the predecessor

npm run fetch-spec              # download openapi.yaml at the pinned ref, validate, pin
npm run fetch-spec -- --check   # drift check against the pin (CI)
npm run clean-specs             # apply the reason-coded scope filters -> openapi_cleaned.yaml
npm run inventory-predecessor   # read the v1 service docs -> predecessor_inventory.csv
node provider-dev/scripts/build_inventory.mjs          # endpoint inventory over the filtered spec
node provider-dev/scripts/disposition_predecessor.mjs  # disposition every v1 method; regenerate Breaking Changes

1. Split into service specs

Tag-discriminated (the spec's tags map 1:1 to services; the untagged Containers family is tag-stamped in clean_specs.mjs; overrides in provider-dev/config/service_names.json are keyed by normalized tag name):

node bin/split.mjs \
  --provider-name openai \
  --api-doc provider-dev/downloaded/openapi_cleaned.yaml \
  --output-dir provider-dev/source \
  --svc-discriminator tag \
  --svc-name-overrides "$(cat provider-dev/config/service_names.json)" \
  --overwrite

Produces 11 service specs in provider-dev/source/: assistants (assistants, threads, messages, runs, run_steps - deprecation-labelled family), batches, containers, conversations, evals, files, fine_tuning, models, skills, uploads, vector_stores.

2. Generate mappings

rm -f provider-dev/config/all_services.csv   # analyze appends; always start clean
node bin/generate-mappings.mjs --provider-name openai --input-dir provider-dev/source --output-dir provider-dev/config
node provider-dev/scripts/map_operations.mjs

map_operations.mjs fills the stackql_* columns from the endpoint inventory (one deterministic rule table) and gates on: coverage both directions, unique method keys, unique path-param signatures per (resource, SQL verb), object keys on every list, and disposition consistency against the predecessor table. Current state: 97 operations mapped (select 43, insert 17, delete 16, exec 12, update 9), 26 resources across 11 services.

3. Normalize the Service Specs

StackQL models providers as relational data sources, and relational databases have no native polymorphism - the oneOf / anyOf / allOf composition must be lowered to concrete schemas before generation. Only the column-producing sites matter: the request-body and 200-response schema roots and their direct properties (a resource's columns). Polymorphism nested deeper lives inside object columns and is addressed with json_extract, so it is intentionally left alone. The OpenAI source is openapi 3.1.0 and composition-heavy (across the split specs before normalize: 253 anyOf, 128 oneOf, 16 allOf); most anyOf sites are the 3.1 nullable idiom (anyOf: [{...}, {type: "null"}]), while oneOf carries genuine polymorphism (message content parts, tool configs, eval data sources).

Stage 3 runs in place on provider-dev/source:

node provider-dev/scripts/pre_normalize.mjs             # OpenAI-specific source edits
npm run normalize -- --api-dir provider-dev/source      # generic allOf flatten + oneOf/anyOf lowering
node provider-dev/scripts/lower_residual_variants.mjs   # lower any union left at a column site

pre_normalize.mjs applies the adjustments the generic normalizer cannot infer, before the flatten:

• Injects the optional org/project headers on every operation (200 parameters across all 100 operations) - OpenAI-Organization and OpenAI-Project as required: false header parameters (the spec declares neither). They surface as optional query parameters, never in required params.
• Carries the assistants-family deprecation driven by the endpoint inventory. The vendor flags only the five /assistants CRUD operations deprecated: true, but the whole family (threads, messages, runs, run steps) carries the migration-to-Responses deprecation. The inventory's deprecated column records that decision per operation; pre_normalize stamps deprecated: true on the 18 family operations the vendor left unflagged (23 total), so the label is uniform and flows through generate into the provider and docs.
• Guards against the openapi 3.1.0 array-type nullable form (type: ["string", "null"]). This form is absent in the current pin, and the anyOf: [{realType}, {type: "null"}] idiom flattens safely under the normalizer's first-wins merge (the real type is always the first member), so no rewrite is needed here. The guard fails the run if a future spec refresh introduces the array-type form, so the downgrade is written deliberately rather than discovered during generate.

npm run normalize (the normalize function in @stackql/provider-utils) renames oneOf / anyOf to allOf at the column-producing sites and flattens all allOf into single merged schemas (569 flattened, 300 variants renamed against this pin), resolving the nullable idiom to the real type in passing. Deep configuration blocks - hyperparameters and method on fine-tuning jobs, chunking_strategy on vector store files, tool configs on assistants - lower to object columns queried with json_extract, preserving the wire shape without exploding into hundreds of scalar columns. The list envelope is an object ({object, data, ...}), not a bare array, so no bare-array wrapping is needed and the $.data object key set during mapping carries through unchanged.

lower_residual_variants.mjs finishes the job at the boundary the generic normalizer leaves shallow. After the flatten, nullable wrappers at column sites are already resolved, so any variant keyword remaining on a request/response property is an irreducible union (for example a conversation item's environment, a discriminated oneOf of a local environment or a container reference). A relational column cannot be a union, so it is lowered to a JSON-blob string column addressed with json_extract - the same posture the normalizer applies to structureless objects. A variant left at a schema root (which would collapse a whole resource into one blob column) is a defect and fails the run rather than being auto-lowered. Against this pin exactly one residual is lowered; the pass is idempotent.

The three steps are deterministic and re-runnable from a fresh split. Re-running generate-mappings and map_operations.mjs against the normalized specs produces a byte-identical mapping (filename, path, operationId, resource, method, verb, object_key) - normalization changes schemas only, never paths, verbs, or the resource/method mapping.

4. Generate the Provider

Transform the normalized service specs into a StackQL provider using the mappings:

rm -rf provider-dev/openapi/*
npm run generate-provider -- \
  --provider-name openai \
  --input-dir provider-dev/source \
  --output-dir provider-dev/openapi/src/openai \
  --config-path provider-dev/config/all_services.csv \
  --servers '[{"url": "https://api.openai.com/v1"}]' \
  --provider-config '{"auth": {"type": "bearer", "credentialsenvvar": "OPENAI_API_KEY"}}' \
  --service-config '{"pagination": {"requestToken": {"key": "after", "location": "query"}, "responseToken": {"key": "$.last_id", "location": "body"}}, "queryParamPushdown": {"top": {"paramName": "limit", "maxValue": 100}}}' \
  --naive-req-body-translate \
  --overwrite

• Fixed server - https://api.openai.com/v1 is a literal host with no server variables, so there are no WHERE server parameters and no host-routing configuration.
• --service-config injects the derived-cursor pagination at the service level (provider-level inheritance is broken in any-sdk). The OpenAI list contract has no dedicated next-token field: after on the next request is fed from the previous page's $.last_id, and traversal ends when the token is absent on the final empty page. Two fine_tuning lists (jobs, events) omit last_id and models is unpaginated - these ship as documented first-page-plus-parameters reads under the same config (the token simply misses and the loop stops after page 1). See NOTES.md section 3 for the engine analysis and the has_more one-request overshoot.
• --naive-req-body-translate makes INSERT / UPDATE body columns the native wire property names (model, training_file, metadata), replacing the v1 data__ prefix (data__model). The request-body-column change is a breaking change beyond the resource and method renames the phase 1 disposition table captures; it is folded into the generated Breaking Changes section.

--service-config also enables LIMIT pushdown: top maps a SQL LIMIT n clause to the wire limit parameter (capped at OpenAI's max of 100), so SELECT ... LIMIT 10 sends limit=10 and users never hand-write WHERE limit = 10. ORDER BY is not pushed down - any-sdk's orderBy renderer is OData-only and emits <column> <direction>, while OpenAI's order takes a bare asc/desc; order therefore stays an ordinary parameter (WHERE "order" = 'desc'). See NOTES.md section 10.

The optional org/project scoping headers are declared with their wire-valid lowercase kebab names (openai-organization / openai-project) - HTTP field names are case-insensitive (RFC 7230 s3.2), so these are the vendor's OpenAI-Organization / OpenAI-Project headers. They are addressed quoted in SQL (WHERE "openai-organization" = 'org-...'), since any-sdk's snake_case aliasing cannot reach a hyphenated identifier (NOTES.md section 10).

No post-generate rewriting is needed. The two corrections a generated provider commonly requires are both already clean in the OpenAI output, verified after generation:

• Response media type - all 99 methods resolve to application/json; the spec has no competing content types that would need rewriting.
• Update / EXEC request binding - the update-POST methods (modify*, update*) and the body-carrying EXEC methods (vector_stores.search, uploads.complete, runs.submit_tool_outputs) all carry the naive request-body translation, so their columns bind without intervention.

The one build-input adjustment - the assistants family deprecation labelling - is applied upstream in pre_normalize.mjs (driven by the endpoint inventory's deprecated column), so it flows through generate into the provider and docs rather than being patched onto generated output.

5. Test the Provider

Four layers.

Validate offline - resolves the provider with no network:

REG_PATH="$(pwd)/provider-dev/openapi"
REG="{\"url\":\"file://${REG_PATH}\",\"localDocRoot\":\"${REG_PATH}\",\"verifyConfig\":{\"nopVerify\":true}}"

stackql --registry="$REG" exec "SHOW SERVICES IN openai"
stackql --registry="$REG" exec "SHOW RESOURCES IN openai.vector_stores"
stackql --registry="$REG" exec "SHOW METHODS IN openai.fine_tuning.jobs"
stackql --registry="$REG" exec "DESCRIBE EXTENDED openai.vector_stores.vector_stores"

Meta-route suite - walks every service, resource and method, asserting each resource has methods, no two methods on one SQL verb share a required-params signature, and every selectable resource yields non-empty DESCRIBE EXTENDED:

PROVIDER_REGISTRY_ROOT_DIR="$(pwd)/provider-dev/openapi"
npm run start-server -- --provider openai --registry $PROVIDER_REGISTRY_ROOT_DIR
npm run test-meta-routes -- openai --verbose
npm run stop-server

Integration tests (mock API server - no key required) - a mock api.openai.com serving real OpenAI wire shapes, asserting row-level results for each archetype: $.data list unwrapping, the derived-cursor traversal (after = prior last_id, terminating on the empty final page), a vector store lifecycle with file membership (create -> get -> file attach -> list files -> delete), a fine-tuning cancel EXEC, the deprecation labels present on the assistants family, and the bearer plus optional org/project headers on every request. Run after every regeneration.

Smoke tests (tests/smoke_test.py, pystackql) - live against the real API, stackql-smoke-<stamp> naming, breadcrumbs swept first, never against a production project. Auth is the provider's declared bearer config on OPENAI_API_KEY, so the key is read from the environment (no credential on the command line). Runs against the local generated provider (default) or the published provider (--registry public, via registry pull openai).

Set up an isolated virtual environment and install the test dependencies (tests/requirements.txt pins pystackql):

python3 -m venv .venv

# activate the venv:
source .venv/bin/activate          # macOS / Linux
# .venv\Scripts\Activate.ps1       # Windows PowerShell
# .venv\Scripts\activate.bat       # Windows cmd

pip install -r tests/requirements.txt

Then run the smokes (.venv active, OPENAI_API_KEY in the environment):

export OPENAI_API_KEY='sk-...'

python3 tests/smoke_test.py --timeout 60          # cap the vector store readiness poll (default 120s)
python3 tests/smoke_test.py --timeout 60 --with-completions    # also run the gated completions demo
python3 tests/smoke_test.py --timeout 60 --registry public     # published provider (doubles as post-publish verification)
python3 tests/smoke_test.py --cleanup-only        # just sweep stackql-smoke breadcrumbs

The .venv/ directory is gitignored. pystackql downloads the stackql binary on first use if one is not already on PATH.

• Ungated (cost-free, run by default) - resolution (SHOW SERVICES), reads (models, files metadata, a limit-bounded read), and the vector store lifecycle: create -> poll the list until the store is listable (--timeout/--poll-interval, the create/SELECT-poll pattern the async resources use) -> get by id -> update name -> delete -> confirm gone. No files attached and no embeddings billed. A valid key always returns the base model list, so an empty models read is treated as an auth/connectivity failure.
• Gated (--with-completions, opt-in, consumes tokens) - a completions call with the prompt "explain how StackQL works". Chat/completions is inference (the data plane) and is deliberately not part of this provider, so this step calls the OpenAI API directly (same key, cheap model, small token cap), separately from the provider - it proves the key works end to end and returns a real answer without smuggling inference into the provider surface.

Without OPENAI_API_KEY the resolution checks still pass and the live steps report BLOCKED, so the script is safe to run in any environment.

Authentication - bearer token from OPENAI_API_KEY, matching the v1 provider:

export OPENAI_API_KEY='sk-...'
stackql shell   # OPENAI_API_KEY is the default credential env var

Optional organization and project scoping is set per query via the injected header parameters (SELECT ... WHERE "OpenAI-Organization" = 'org-...'), or globally through the runtime auth override.

6. Publish the Provider

Cutover replaces the v1 provider (see the archive and acceptance plan in NOTES.md section 7): the v1 doc artifacts move to legacy/ in one commit (history preserved), the new provider version replaces the v1 version in the stackql-provider-registry per the registry release flow (the old version stays pullable for pinning), and the v1 documented example queries re-run against the new provider - each passes unchanged or is covered by a Breaking Changes entry.

Pull and verify from the dev registry:

export DEV_REG="{ \"url\": \"https://registry-dev.stackql.app/providers\" }"
./stackql --registry="${DEV_REG}" shell

registry pull openai;

7. Generate Web Docs

The doc microsite (website/, Docusaurus 3.10, served at openai-provider.stackql.io) is regenerated from the new provider output. It follows the shared-config pattern used across the provider microsites: the navbar/footer/theme/plugin configuration lives in stackql/docusaurus-config, vendored into .shared-config/ at build time (the vendor-config script runs automatically on prestart/prebuild). Site-local files are the provider identity (website/provider.js), thin wrappers (docusaurus.config.js, sidebars.js), the shared components/theme under src/, and static assets (including static/CNAME). The provider identity is already set:

// website/provider.js
export const providerName = 'openai';
export const providerTitle = 'OpenAI';

The landing-page installation/authentication content is authored in headerContent1.txt / headerContent2.txt under provider-dev/docgen/provider-data/. Generate, sanitize, then build:

npm run generate-docs -- \
  --provider-name openai \
  --provider-dir ./provider-dev/openapi/src/openai/v00.00.00000 \
  --output-dir ./website \
  --provider-data-dir ./provider-dev/docgen/provider-data

node provider-dev/docgen/sanitize_docs.mjs   # fix OpenAI doc links (see below)

cd website
yarn install
yarn build      # vendor-config clones the shared config first; network to GitHub required
yarn serve

sanitize_docs.mjs does two things the doc generator cannot:

• Doc links - rewrites the relative OpenAI doc links carried through from the spec descriptions ([Files API](/docs/api-reference/...), /docs/guides/..., /docs/models) by prefixing them with https://platform.openai.com. OpenAI's docs have moved to developers.openai.com with renamed paths that cannot be computed deterministically, but platform.openai.com serves a 301 redirect from every old /docs/... path to its current home, so the host prefix lands users on the right page and stays correct as OpenAI reorganises. Without this step the links resolve against the microsite and 404 (the shared config's onBrokenLinks: 'warn' keeps the build passing, but the links are dead).
• SQL examples - the generator emits every parameter into the example WHERE clause and INSERT column list verbatim, which produces SQL that does not parse: order is a reserved word (syntax error ... near 'order') and the header parameters are hyphenated (unexpected: openai - organization). Both are double-quoted. limit is dropped from the examples entirely, since a SQL LIMIT clause supplies it via the top pushdown; the params table still documents it, annotated by pre_normalize.mjs.

The regenerated docs carry the openai_admin sibling pointer and note the generation change once, per the cutover plan.

Service Coverage

11 services, 26 resources, 97 mapped operations (select 43, insert 17, delete 16, exec 12, update 9).

Service	Resources	Notes
models	models	list / get / delete
files	files	metadata (list / get / delete); content upload and download out of scope
fine_tuning	jobs, events, checkpoints, checkpoint_permissions	async-job archetype: create / poll / cancel / pause / resume
batches	batches	in-scope async job control: create / poll / cancel
vector_stores	vector_stores, files, file_batches, file_batch_files	CRUD flagship with file membership; search is EXEC (gated)
assistants	assistants, threads, messages, runs, run_steps	deprecation-labelled (vendor migration to Responses)
evals	evals, runs, run_output_items	eval definitions and runs
conversations	conversations, items	Responses-family state surface
uploads	uploads	metadata lifecycle: create / complete / cancel
containers	containers, files	code-interpreter container metadata; files created via file_id reference (binary upload out of scope)
skills	skills, versions	versioned skill metadata (list/get/delete, default-version update); skill creation is a multipart file upload, out of scope (binary)

The organization/admin surface (/organization/...) is the sibling openai_admin provider. See the Breaking Changes section above for the full v1 disposition.

License

MIT

Contributing

Contributions are welcome. Please open an issue or pull request.