bug: chkit pull drops PROJECTIONs; core DSL can't represent index-only (TYPE basic) projections

[alvarogar4]

Bug Description

Two related gaps around table PROJECTIONs:

1. chkit pull silently omits projections. A pulled table(...) has no projections field even when the live table defines one. (The pull docs state projections are captured.)
2. @chkit/core's DSL cannot represent index-only projections. ProjectionDefinition is { name, query } and the SQL renderer emits PROJECTION `name` (query) — always parenthesized, SELECT-style only. An index-only projection such as INDEX (col_a, col_b) TYPE basic has no SELECT body and cannot be expressed; forcing the INDEX clause into query renders invalid SQL: PROJECTION `name` (INDEX (...) TYPE basic).

So even the manual workaround for (1) is blocked by (2) for index-only projections.

Reproduction

Live table (ClickHouse 26.3.x / ObsessionDB) with an index-only projection:

CREATE TABLE solana.address_counterparts (
    sender    String,
    receiver  String,
    -- ... aggregate columns ...
    PROJECTION by_receiver INDEX (receiver, sender) TYPE basic
)
ENGINE = SharedAggregatingMergeTree
ORDER BY (sender, receiver)
SETTINGS deduplicate_merge_projection_mode = 'rebuild';

1. Point a chkit project at the cluster and run chkit pull --database solana.
2. Open the generated address_counterparts table(...) → there is no projections field. (The string projection only shows up via the unrelated deduplicate_merge_projection_mode setting.)

Impact

• Broken recreate: generate + migrate from the pulled schema creates the table without the projection, silently dropping reverse-lookup pruning — WHERE receiver = … degrades to a full scan of the largest table.
• False drift: chkit drift reports the live projection as an extra object absent from the schema, so drift never reads clean.

Investigation Notes

Confirmed against installed 0.1.2-beta.1 (@chkit/core, @chkit/plugin-pull):

• Pull (packages/plugin-pull/src/index.ts): introspected objects are rendered without a projections field for tables; index-only projections appear not to be introspected at all.
• Core DSL (packages/core):
  • ProjectionDefinition = { name: string; query: string } (model-types.ts) — no way to encode INDEX (...) TYPE <type>.
  • Renderer wraps unconditionally: PROJECTION `${p.name}` (${p.query}) in both toCreateSQL and renderAlterAddProjection (sql.ts).

Expected Behavior

• chkit pull captures a table's PROJECTIONs (both SELECT-style and index-only TYPE …).
• @chkit/core can express index-only projections and render them correctly — PROJECTION `by_receiver` INDEX (receiver, sender) TYPE basic (no wrapping parens).
• Round-trip pull → generate → migrate reproduces the projection exactly, and drift reads clean.

Proposed Fix

• Core: make ProjectionDefinition a discriminated union and branch the renderer:

  type ProjectionDefinition =
    | { name: string; query: string }                 // SELECT   → PROJECTION `name` (query)
    | { name: string; index: string; type: string };  // index-only → PROJECTION `name` INDEX (index) TYPE type

  Emit the index-only form without the wrapping (...).
• Pull: introspect projections (system.projections / SHOW CREATE TABLE) and emit them, mapping index-only projections to the new form.

Verification

• Core: renderer unit test for both kinds — assert the index-only case renders PROJECTION `by_receiver` INDEX (receiver, sender) TYPE basic (no parens).
• Pull: regression test where the introspector returns a table with an index-only projection; assert the rendered table(...) includes it.
• E2E: round-trip pull → generate → migrate reproduces by_receiver; drift reads clean.

---

Found in the Numia × Range Solana schema (address_counterparts).