add cloud gateway+drizzle+zod

[mabels]

do not forget --- the way to better software.

[jchris]

PR #655: Add Zod and Drizzle ORM Migration

Overview

This PR makes significant changes to the Fireproof codebase, transitioning from a custom SQL abstraction layer to using Drizzle ORM with LibSQL, and implementing Zod for schema validation. The PR is currently WIP (Work In Progress) and tests are failing.

Key Changes

1. Database Layer Migration

• Removed Custom SQL Abstraction:

  • Removed abstract-sql.ts and bettersql-abstract-sql.ts
  • Replaced with Drizzle ORM's LibSQL integration

• Schema Definition Changes:

  • Migrated from manual SQL CREATE TABLE statements to Drizzle ORM's declarative schema definitions
  • Added SQLite tables using sqliteTable with proper foreign keys and indexes
  • Example: sqlKeyByTenantLedger and sqlMetaByTenantLedger tables with relationships

2. Zod Schema Validation

• Added Zod for runtime type validation in src/protocols/cloud/msg-types.ts
• Replaced several interfaces with Zod schemas and their inferred types:
  • zodBaseTokenParam, zodJWTPayload, zodFPCloudClaim
  • zodError, zodUcanAuth
  • Various message type definitions

3. Configuration and Setup

• Added drizzle.cloud.config.ts for Drizzle ORM configuration
• Applied a large patch to Drizzle Kit (1909 lines in patches/drizzle-kit.patch)
• Updated CI workflow and testing infrastructure

4. Dependencies

• Added new dependencies:
  • @libsql/client (v0.15.0)
  • drizzle-orm (v0.40.1)
  • zod (v3.24.2)

Current Issues

The tests are failing with a LibSQL error:

LibsqlError: URL_INVALID: Invalid host in file URL: "."

This appears to be related to an incorrect database URL format in test-helper.ts:

sql: drizzle(createClient({ url: `file://./dist/node-meta.sqlite` }))

The error suggests the file URL format is incorrect and should either be:

• file:/absolute/path.db
• file:///absolute/path.db

Next Steps

1. Fix the database URL format in test setup
2. Ensure proper LibSQL client initialization
3. Complete the migration of remaining SQL queries to Drizzle ORM
4. Update tests to work with the new database layer
5. Clean up/remove old SQL code that's no longer needed

Impact

This migration brings several benefits:

• Type-safe database operations with Drizzle ORM
• Runtime type validation with Zod
• Improved maintainability with declarative schema definitions
• Better compatibility with modern tooling and practices