design: provider-agnostic catalog architecture — short-term JSON, long-term tycoon-catalog + SQLite

[JesuFemi-O]

Overview

Part of #84 (Milestone 3 — Source catalog).

As tycoon grows to support multiple ingestion providers (dlt, Fivetran, Airbyte, Estuary) and their connector registries, we need a catalog architecture that scales without becoming unmaintainable. This issue documents the full long-term design and the immediate short-term decisions we are making to keep the current scope lean.

---

The problem

Tycoon needs to know about every connector it can add for a user — what credentials it needs, what config fields it requires, what schema it writes to. Right now that knowledge is hardcoded in the CLI source. As we add more providers and connectors, that approach breaks down:

• A single file listing every connector becomes impossible to review in PRs
• No validation — a typo in a credential name silently ships to users
• Provider registries like Fivetran and Airbyte publish hundreds of connectors and update them regularly — hand-maintaining those by hand is a non-starter
• The CLI has no way to update connector data without a full release

We need a catalog that is queryable, versioned, and manageable independently from the CLI itself.

---

Connector identity

Every connector is identified by two fields — source (the provider) and connector (the specific integration within that provider). In tycoon.yml this looks like:

sources:
  my_hubspot:
    source: dlt
    connector: hubspot
    schema: raw_hubspot
    config:
      api_key: ${HUBSPOT_API_KEY}

Keeping source and connector as separate fields (rather than a combined dlt/hubspot identifier) means both stay simple strings that validate and tab-complete cleanly:

tycoon sources add --source dlt --connector <TAB>
# github   hubspot   google_sheets   slack ...

tycoon sources add --source fivetran --connector <TAB>
# postgres   salesforce   stripe ...

When two providers support the same underlying service — postgres exists in dlt, Fivetran, and Airbyte — the user explicitly picks which integration layer they want. No ambiguity.

---

Tycoon-managed vs user's own dlt project

These are two distinct concepts that must not be conflated.

Tycoon-managed (sources: block) — tycoon owns the pipeline entirely. It knows the connector's credentials, generates the pipeline, and runs it. The user never writes dlt Python.

sources:
  my_hubspot:
    source: dlt
    connector: hubspot
    schema: raw_hubspot

User-managed (runtimes: block) — the user already has their own dlt project. Tycoon orchestrates and observes it but does not own the pipeline code.

runtimes:
  my_pipelines:
    type: dlt-project
    path: ./pipelines/

The migration path from user-managed → tycoon-managed is a tycoon sources adopt command (future). The reverse is a tycoon sources eject command (future).

---

Short-term decision (current scope)

Stephen's direction: keep it lean. The immediate priority is Hubspot and Google Sheets as the first fully-supported connectors. Everything else is discovery metadata — good enough to browse, not yet fully wired.

What we are doing now:

• Keep a single JSON file as the catalog store — no database, no separate repo yet
• The JSON is structured so each entry carries a source (provider) and a backend object with provider-specific metadata, making it ready for multiple providers even if we only have dlt today
• Hubspot and Google Sheets get full credential and config field specs so users can add them end-to-end from the CLI
• Other entries remain as minimal stubs — display name, category, description — sufficient for catalog browsing
• Wire the tycoon sources catalog and tycoon sources add commands to read from the JSON catalog instead of the current hardcoded list

What we are explicitly not doing yet:

• No separate tycoon-catalog repo
• No database
• No tycoon-catalog CLI tool
• No web catalog
• No automated scraping of Fivetran or Airbyte connector registries

---

Long-term architecture

The tycoon-catalog repo

When the catalog outgrows a single JSON file, it moves to a dedicated tycoon-catalog repo:

tycoon-catalog/
  providers/
    dlt.yaml          # provider-level metadata
    fivetran.yaml
    airbyte.yaml
  connectors/
    dlt/
      hubspot.yaml    # hand-curated
      google_sheets.yaml
      github.yaml
      ...
    fivetran/
      *.yaml          # auto-generated by tycoon-catalog sync fivetran
    airbyte/
      *.yaml          # auto-generated by tycoon-catalog sync airbyte
  schema/
    connector.schema.json   # validates every connector YAML
  catalog.db                # compiled artifact, published as a GitHub release

Each connector is one YAML file — easy to review in PRs, easy for contributors to add or fix a single entry without touching everything else.

Why not one YAML per connector for dlt too?

For dlt, the source of truth for connector metadata already exists — the dlt-hub/verified-sources GitHub repo. Rather than hand-authoring 40+ YAML files, tycoon-catalog sync dlt scrapes that repo and auto-generates stubs. Only the connectors that need full credential and config field specs (Hubspot, Google Sheets, etc.) require hand-curation, because that level of detail is not machine-readable from the dlt source code.

For Fivetran and Airbyte, both publish structured connector registries — Fivetran via REST API, Airbyte via their GitHub repo. Those are fully automated, no hand-authoring at all.

In practice the split looks like this:

Provider	Hand-curated	Auto-generated	Source
dlt	~8 (fully-specced connectors)	~32+ stubs	dlt-hub/verified-sources repo
Fivetran	0	300+	Fivetran connector API
Airbyte	0	280+	Airbyte connector registry
Estuary	0	100+	Estuary connector API

tycoon-catalog — the catalog management CLI

A separate CLI tool (pip install tycoon-catalog) that lives in the tycoon-catalog repo and is used by catalog maintainers, not end users:

# Provider management
tycoon-catalog provider add fivetran
tycoon-catalog provider list

# Individual connector management
tycoon-catalog connector add dlt/hubspot       # interactive scaffold
tycoon-catalog connector validate dlt/hubspot
tycoon-catalog connector list --provider dlt

# Sync from upstream registries
tycoon-catalog sync dlt        # scrapes github.com/dlt-hub/verified-sources
tycoon-catalog sync fivetran   # hits Fivetran's connector registry API
tycoon-catalog sync airbyte    # scrapes Airbyte's connector definitions

# Build and publish
tycoon-catalog build                   # YAML → catalog.db, validates schema
tycoon-catalog publish --bump minor    # tags release, uploads catalog.db artifact
tycoon-catalog schema bump major       # breaking change — increments schema version

CI on tycoon-catalog runs tycoon-catalog build and tycoon-catalog validate on every PR. Merging to main triggers tycoon-catalog publish --bump patch automatically. Sync commands open PRs with diffs for review rather than auto-merging.

Catalog storage: SQLite

SQLite is the right choice over DuckDB for this use case:

	SQLite	DuckDB
Dependency	Python stdlib — zero added weight	~30 MB pip package
CLI cold-start cost	none	~150 ms shared library load
Pre-installed tooling	sqlite3 ships everywhere	separate install
Full-text search	FTS5 built in	extension required
Suitable row count	millions	analytical/OLAP workloads

At 500–1000 catalog rows, DuckDB's analytical strengths don't apply. The decisive factor is zero dependency — sqlite3 is in the Python stdlib, so embedding the catalog adds nothing to the CLI's install footprint.

The catalog schema separates concerns into four tables — sources, credentials, config_fields, resources — with a virtual FTS5 table over id, display_name, and description to power tycoon sources catalog --search postgres.

Version compatibility between catalog and CLI

The catalog and CLI ship and update independently. Every catalog.db carries a metadata table:

CREATE TABLE catalog_meta (
  schema_version  INTEGER NOT NULL,   -- increments only on breaking changes
  catalog_version TEXT NOT NULL,      -- semver e.g. "1.4.2"
  built_at        TEXT NOT NULL
);

The CLI declares the range of schema versions it understands. On tycoon catalog sync, before writing the new file:

Fetching catalog v2.0.0 (schema v2)...
  CLI supports schema v1. This catalog requires schema v2.
  Upgrade tycoon-cli first: pip install --upgrade tycoon

The rule: schema version only increments on breaking changes (field removed, renamed, or type changed). Adding optional fields is never breaking — old CLI versions ignore unknown fields and keep working.

CLI UX at scale

With 500+ connectors a flat list is unusable. The catalog command becomes two-tier:

# Tier 1 — providers
tycoon sources catalog
  Provider    Connectors
  dlt         40
  fivetran    312
  airbyte     280

# Tier 2 — connectors within a provider, grouped by category
tycoon sources catalog dlt
  Category          Connector       Description
  CRM               hubspot         Contacts, deals, companies...
  Productivity      google_sheets   Sync sheets as tables
  Developer Tools   github          Issues, pull requests, commits...

# Cross-provider search
tycoon sources catalog --search postgres
  dlt        sql_database   Generic SQL (PostgreSQL, MySQL...)
  fivetran   postgres       PostgreSQL via Fivetran
  airbyte    postgres       PostgreSQL via Airbyte

# Detail view
tycoon sources catalog dlt hubspot

Shell completion on tycoon sources add handles the common case where a user roughly knows what they want.

Web catalog (future)

For browsing and comparing 500+ connectors the terminal is the wrong medium. A catalog.tycoon.dev web interface renders directly from the same catalog.db the CLI ships — one build artifact, two consumers. Deferred until we have more than one provider in production.

---

Proposed immediate next steps

• Enrich Hubspot and Google Sheets entries in the catalog JSON to full spec (credentials, config fields, hints) — priority connectors
• Wire tycoon sources catalog to read from the catalog JSON instead of the current hardcoded list
• Wire tycoon sources add to use the catalog JSON for config collection
• Remove the existing hardcoded connector list once both commands are fully wired
• Reserve the tycoon-catalog GitHub repo as a placeholder documenting the future intent

Future milestones

• tycoon.yml schema migration: add source field alongside connector, run migration for existing projects via tycoon init --upgrade
• tycoon-catalog repo with per-connector YAML files and tycoon-catalog build CLI
• Automated sync from Fivetran and Airbyte connector registries
• Migrate from JSON to SQLite catalog.db
• tycoon catalog sync command — pull the latest catalog without a CLI release
• Web catalog at catalog.tycoon.dev