Add Amazon Redshift connector

[luca-martial]

Scope

Read-only Amazon Redshift connector: schema introspection, table/column
sampling, read-only query execution, and (phase 2) query-history ingestion.

Key design

• Where it lives. Connectors are modules inside packages/cli, not
  packages/connector-* packages (that layout was retired; the leftover folders
  on disk are stale build output). Add packages/cli/src/connectors/redshift/,
  modeled on packages/cli/src/connectors/postgres/ (Redshift uses the Postgres
  wire protocol and the same pg driver). Register 'redshift' starting from
  KtxConnectionDriver in context/scan/types.ts; the typed driverRegistrations
  record in context/connections/drivers.ts will compile-error you through the
  rest (dialect factory, driver-schemas.ts, sql-analysis dialect map, setup
  wizard).
• Reuse, don't copy. Extract the shared Postgres-wire plumbing (pool,
  read-only enforcement, param prep, env:/file: resolution) so postgres
  and redshift share one implementation. Fork only the Redshift-specific parts.
• Introspection. Use SVV_TABLES / SVV_COLUMNS / SVV_TABLE_INFO (not
  pg_catalog, which misses external/late-binding tables and has stale row
  counts). Redshift has no TABLESAMPLE and no pg_stats, and declared PK/FK
  are unenforced hints.
• Auth. Password (same as Postgres) and IAM. IAM mints temporary credentials
  via the AWS SDK and differs by deployment: redshift:GetClusterCredentials
  (provisioned) vs redshift-serverless:GetCredentials (serverless). Refresh on
  expiry; load the AWS SDK only on the IAM path.
• Query history (phase 2). Lives in the historic-sql ingest adapter as a
  per-dialect reader (redshift-query-history-reader.ts) modeled on the
  Snowflake/BigQuery readers, not in the connector. Read SYS_QUERY_HISTORY,
  not STL_QUERY / SVL_QLOG (legacy, truncated, absent on serverless).

Acceptance criteria

• Builds, type-checks, tests, and dead-code pass
• Same KtxScanConnector surface and capabilities as Postgres, mock-pool
  tests in the postgres/connector.test.ts shape
• Auth: password and IAM (provisioned and serverless)
• Query history reads SYS_QUERY_HISTORY behind context.queryHistory
• Docs in docs-site/content/docs/integrations/primary-sources.mdx with
  password and IAM examples

Suggested phasing: PR 1 connector, PR 2 query history.

[samuelayanshina]

I'd like to claim this one. Plan: model the connector on connectors/postgres/ (Redshift speaks the Postgres wire protocol), swapping introspection over to Redshift system tables, SVV_TABLES, SVV_COLUMNS, and SVV_TABLE_INFO for schema/metadata, and STL_QUERY for query history ingestion. I'll support both IAM and password-based auth, keep execution read-only, match the existing connector conformance pattern with mock-based tests, and add the connection-config docs. I recently landed the MySQL columnStats work (#233), so I'm familiar with the connector structure.

[andreybavt]

Thanks @samuelayanshina, I'll assign it to you then 🙏

Heads up: I rewrote the issue, since it pointed you at the old
packages/connector-* layout. Connectors now live in
packages/cli/src/connectors/, so model on
packages/cli/src/connectors/postgres/ and reuse its wire layer rather than
copying it. The other gotcha is query history: it goes in the historic-sql
adapter (not the connector) and should read SYS_QUERY_HISTORY, not
STL_QUERY, which doesn't exist on serverless. Details are in the updated issue.
Two PRs (connector, then query history) would be easiest to review.

[samuelayanshina]

Thanks @andreybavt, appreciate the assign and the heads-up. I read the rewritten issue, the packages/cli/src/connectors/ layout and the historic-sql adapter / SYS_QUERY_HISTORY note are clear, and splitting connector from query history into two PRs makes sense.

PR #304 is already up with the connector, but it currently copies the Postgres connector rather than reusing its wire layer. I'll rework it to share the Postgres wire/connection layer instead of duplicating it, then push to the same branch. Query history I'll do as a separate follow-up PR against the historic-sql adapter using SYS_QUERY_HISTORY so it works on serverless too.

Will ping here once #304 is updated.

[samuelayanshina]

Rebased onto main and started the wire-layer reuse. Quick architecture check before I go further:

The Postgres connector keeps its pool/config/queryRaw/URL-parsing plus its introspection all in one class, with the pg_catalog queries inlined in a private loadSchemaTables. To reuse rather than copy, I'm planning to extract the Postgres wire layer (pool factory, pool-config builder, read-only query prep, queryRaw) into a shared module that both Postgres and Redshift import, while each connector keeps its own introspection (Postgres pg_catalog, Redshift SVV_*). That keeps the Postgres connector's behavior identical and avoids duplicating the connection plumbing.

Does that match what you had in mind, or would you rather I keep the shared surface smaller (e.g. Redshift reusing a couple of exported helpers without a new shared module)? Happy to go either way, just want to land it the way you'd prefer to maintain it.