storage create-table: support native Snowflake types with length/precision

[padak]

Problem

kbagent storage create-table currently accepts only the basic Keboola types:

--column col:TYPE   # STRING | INTEGER | NUMERIC | FLOAT | BOOLEAN | DATE | TIMESTAMP

There is no way to specify native backend types (e.g. Snowflake VARCHAR(40), NUMBER(10,0), TIMESTAMP_TZ, VARCHAR(80) etc.) from the CLI. All string columns end up as the default wide VARCHAR, and numeric columns cannot carry precision/scale. This loses information that is important for storage footprint, query performance, and downstream schema validation.

Use case that exposed this

Retyping an existing Storage table (out.c-adaptive.out, 128k Slack messages) after profiling in a workspace. Profiling produced precise bounds:

• pkey — SHA-1, exactly 40 chars → want VARCHAR(40) not default VARCHAR(16777216)
• channel_id, user, u_id, team_id, ch_id, creator, ... — Slack IDs, max 9 chars → VARCHAR(20)
• tz_offset ∈ ⟨-25200, 25200⟩ → NUMBER(6,0) fits; no reason for default NUMBER(38,0)
• num_members ∈ ⟨0, 51⟩ → NUMBER(3,0)
• ts (RFC 822) → TIMESTAMP_TZ
• ch_name max 21 → VARCHAR(80) (Slack channel limit)

With the current CLI these all collapse to STRING / INTEGER with no way to communicate the tighter bounds. The workaround is to call the Storage API directly or go through the MCP create_table tool, which defeats the purpose of having a first-class CLI flag.

Proposed solution

Extend the --column flag to accept an optional native-type suffix. Backward compatible — existing col:STRING keeps working.

Suggested syntax (parenthesised, matches SQL):

--column pkey:VARCHAR(40)
--column tz_offset:NUMBER(6,0)
--column num_members:NUMBER(3,0)
--column ts:TIMESTAMP_TZ
--column is_admin:BOOLEAN
--column text:VARCHAR                 # no length = backend default
--column created:NUMBER(10,0)

Under the hood this maps to the Storage API columnsDefinition[] payload:

{
  "name": "pkey",
  "definition": { "type": "VARCHAR", "length": "40", "nullable": false },
  "basetype": "STRING"
}

Accepted native types (Snowflake first, since that is the primary backend)

• VARCHAR(n), CHAR(n), STRING, TEXT
• NUMBER(p,s), INTEGER, FLOAT, DOUBLE
• BOOLEAN
• DATE, TIMESTAMP, TIMESTAMP_NTZ, TIMESTAMP_LTZ, TIMESTAMP_TZ, TIME
• VARIANT, OBJECT, ARRAY

For other backends (BigQuery, Redshift, Synapse) the parser should accept their native type names and pass them through, so this stays a single unified flag.

Additional per-column attributes

It would also be useful to express:

• NOT NULL / nullability — e.g. --column pkey:VARCHAR(40):NOT_NULL or --column pkey:VARCHAR(40) --not-null pkey
• DEFAULT value

Open to whatever syntax the maintainer prefers — what matters is that length, precision, and nullability are expressible from the CLI.

Why this matters

1. Storage cost & perf — VARCHAR(40) vs the default max VARCHAR(16777216) affects Snowflake micro-partition pruning and metadata.
2. Schema contract — downstream components (transformations, writers) rely on types; native types prevent silent coercions.
3. Closing the gap with MCP/API — the Storage API already supports columnsDefinition; the CLI is the only layer that hides it.
4. Common workflow — "profile a table in a workspace, then recreate it with tight types" is a recurring AI-agent task and currently requires dropping down to raw API calls.

Acceptance criteria

• --column name:VARCHAR(40) creates a Snowflake column with VARCHAR(40) (verified via storage table-detail).
• --column name:NUMBER(10,2) works with precision + scale.
• All currently-accepted basic types (STRING, INTEGER, ...) keep working unchanged.
• Unknown/malformed type string yields a clear error before the API call.
• --help documents the new syntax with at least three examples.
• README / kbagent context updated.

Related

• Storage API reference for columnsDefinition: https://keboola.docs.apiary.io/#reference/tables/create-table-asynchronously/create-new-table-from-csv-file-asynchronously
• Current CLI signature: kbagent storage create-table --help