# Multi-Database Support via Hexagonal Architecture

## Motivation

Support DuckDB, SQLite, and future PostgreSQL to give users a choice at project creation time and remove adoption barriers for users who can't or won't install DuckDB.

Each hatk project commits to one database engine — no runtime switching.

## Configuration

Users set `database: 'duckdb' | 'sqlite'` in `hatk.config.ts`. At startup, hatk dynamically imports the matching adapter. Users only need the driver for their chosen database installed.

## Architecture

### Ports

Two interfaces define the hexagonal boundary:

**DatabasePort** — low-level SQL execution:

```typescript
interface DatabasePort {
  open(path: string): Promise<void>
  close(): Promise<void>

  query<T>(sql: string, params?: unknown[]): Promise<T[]>
  execute(sql: string, params?: unknown[]): Promise<void>

  beginTransaction(): Promise<void>
  commit(): Promise<void>
  rollback(): Promise<void>

  createBulkInserter(table: string, columns: string[]): Promise<BulkInserter>

  dialect: Dialect
}

interface BulkInserter {
  append(values: unknown[]): void
  flush(): Promise<void>
  close(): Promise<void>
}

type Dialect = 'duckdb' | 'sqlite' | 'postgres'
```

**SearchPort** — optional FTS capability:

```typescript
interface SearchPort {
  createIndex(table: string, columns: string[]): Promise<void>
  search(table: string, query: string, opts: SearchOpts): Promise<SearchResult[]>
}
```

Adapters declare whether they implement `SearchPort`. When unavailable, hatk falls back to `LIKE` matching.

### Dialect-Aware SQL Generation

A `SqlDialect` helper provides per-engine variations so the shared layer avoids scattered conditionals:

```typescript
interface SqlDialect {
  typeMap: Record<string, string>
  param(index: number): string       // $1 vs ?
  supportsAppender: boolean
  returningClause: boolean
  upsertSyntax: 'on_conflict' | 'insert_or_replace'
  jsonExtract(column: string, path: string): string
}
```

Type mappings used by `schema.ts`:

| Lexicon type | DuckDB       | SQLite    | Postgres      |
|-------------|-------------|-----------|---------------|
| `string`    | `TEXT`      | `TEXT`    | `TEXT`        |
| `integer`   | `BIGINT`   | `INTEGER` | `BIGINT`      |
| `boolean`   | `BOOLEAN`  | `INTEGER` | `BOOLEAN`     |
| `bytes`     | `BLOB`     | `BLOB`   | `BYTEA`       |
| `datetime`  | `TIMESTAMPTZ` | `TEXT` | `TIMESTAMPTZ` |

SQLite stores booleans as integers and datetimes as text. The shared layer handles conversion at the binding/reading boundary.

### Adapters

**DuckDBAdapter** (~200-300 lines)
- Wraps `@duckdb/node-api`
- `BulkInserter` maps to DuckDB's native appender
- Implements `SearchPort` using DuckDB's FTS extension
- Read/write connection separation

**SQLiteAdapter** (~200-300 lines)
- Wraps `better-sqlite3`
- `BulkInserter` batches rows into multi-row `INSERT` within a transaction
- No `SearchPort` — falls back to `LIKE`
- WAL mode for concurrent reads

**PostgresAdapter** (future, ~200-300 lines)
- Wraps `pg` (node-postgres)
- `BulkInserter` uses `COPY FROM`
- Implements `SearchPort` using `tsvector`/`tsquery`
- Connection pooling

### Adapter Loading

```typescript
async function createAdapter(config: HatkConfig): Promise<DatabasePort> {
  switch (config.database) {
    case 'duckdb': {
      const { DuckDBAdapter } = await import('./adapters/duckdb.js')
      return new DuckDBAdapter()
    }
    case 'sqlite': {
      const { SQLiteAdapter } = await import('./adapters/sqlite.js')
      return new SQLiteAdapter()
    }
  }
}
```

### OAuth

OAuth operations (sessions, tokens, keys, DPoP) go through the same `DatabasePort`. No separate database or port needed — the queries are simple CRUD.

## File Structure

All database code moves to `src/database/`:

```
src/database/
  ports.ts          # DatabasePort, BulkInserter, SearchPort interfaces
  dialect.ts        # SqlDialect interface + per-engine dialect configs
  db.ts             # Shared data access layer (refactored from current db.ts)
  schema.ts         # DDL generation (refactored from current schema.ts)
  fts.ts            # FTS dispatcher with LIKE fallback
  adapters/
    duckdb.ts       # DuckDB adapter + SearchPort
    sqlite.ts       # SQLite adapter
```

The rest of the codebase (`server.ts`, `indexer.ts`, `main.ts`, etc.) imports from `database/db.ts` instead of `db.ts` — same API surface, different path.

## Implementation

Done as a single pass, not phased:

1. Create `src/database/` with `ports.ts` and `dialect.ts`
2. Extract DuckDB-specific code from current `db.ts` into `adapters/duckdb.ts`
3. Refactor `db.ts` into `database/db.ts`, calling through `DatabasePort`
4. Refactor `schema.ts` into `database/schema.ts`, using `SqlDialect.typeMap`
5. Extract DuckDB FTS from `fts.ts` into adapter's `SearchPort`, add `LIKE` fallback
6. Implement `SQLiteAdapter` in `adapters/sqlite.ts`
7. Add `database` config option and dynamic adapter loading in startup
8. Update all imports across the codebase
9. Update `hatk new` scaffolding to include database choice