Skip to content

feat: add ALTER TABLE diff mode to generateSQL for existing production schemas #18

Description

@stackbilt-admin

Problem

generateSQL currently emits only CREATE TABLE DDL. Running this output against any table that already exists in production will fail with a duplicate table error.

FoodFiles has 14 migrations already applied to its production D1 database. Adding contract-generated DDL to an established schema requires either:

  • ALTER TABLE ... ADD COLUMN statements for new fields
  • A diff mode that computes the delta between the current contract definition and an existing schema snapshot

Without this, generateSQL is unusable for any phase-0 or later consumer whose tables are already live. The only workaround is to manually translate contract changes into migration files, which defeats the purpose of the package.

Acceptance Criteria

  • generateSQL(contract, { mode: 'diff', baseline: SchemaSnapshot }) emits only ALTER TABLE ADD COLUMN statements for columns present in the contract but absent from the baseline
  • SchemaSnapshot can be produced from a D1 PRAGMA table_info() result (or equivalent JSON shape)
  • Diff mode never emits DROP COLUMN statements without an explicit { destructive: true } opt-in flag
  • Default (no baseline) behavior remains CREATE TABLE for backwards compatibility
  • A generateMigration(contract, baseline) convenience export wraps diff mode and returns a migration file string with a timestamped filename
  • Unit tests cover: new table (no baseline), additive column, renamed column warning, type-change warning

Context

FoodFiles is the designated phase-0 consumer. Its 14-migration history is the concrete stress-test for this feature. The pantry and organization contracts are the first candidates for diff-mode adoption once this lands.

Ref: CONTRACT-2 gap analysis (phase-0 integration audit)

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions