Tibsfox/gsd-skill-creator / examples/skills/gsd-meta/portable-schema-generator
portable-schema-generator
VerifiedEmit Postgres.sql and SQLite.sql from a single schema spec so tools work across both drivers without duplicating DDL by hand. Use when designing a schema that needs to support both shared Postgres deployments and zero-config SQLite. Reduces two-file sync burden to a single source edit.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 4.2 KB |
SKILL.md
View on GitHub---
name: portable-schema-generator
description: "Emit Postgres.sql and SQLite.sql from a single schema spec so tools work across both drivers without duplicating DDL by hand. Use when designing a schema that needs to support both shared Postgres deployments and zero-config SQLite. Reduces two-file sync burden to a single source edit."
format: 2025-10-02
version: 1.0.0
status: stable
updated: 2026-04-17
type: skill
category: gsd-meta
origin: tibsfox
modified: false
first_seen: 2026-04-17
first_path: examples/skills/gsd-meta/portable-schema-generator/SKILL.md
superseded_by: null
---
# Portable Schema Generator
Two-driver schemas mean two migration files. Hand-keeping them in sync
drifts fast. This skill captures the translation rules and the minimal
tooling that keeps them aligned.
## When to Use
- Your tool needs to work on **both** Postgres and SQLite
- You want fresh-project onboarding to be zero-config (SQLite default)
while shared-infra deployments keep Postgres
- A single schema change should land in both driver files with no manual
diff-copying
## Core Differences
| Concern | Postgres | SQLite |
|---------|----------|--------|
| Schemas | `CREATE SCHEMA foo; SET search_path TO foo` | Namespace-flat — prefix names instead |
| Autoincrement | `BIGSERIAL PRIMARY KEY` | `INTEGER PRIMARY KEY AUTOINCREMENT` |
| Timestamps | `TIMESTAMPTZ DEFAULT now()` | `TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))` |
| Booleans | `BOOLEAN` | `INTEGER` (0/1) |
| Enums via CHECK | `CHECK (col IN ('a','b'))` | Same — both support inline CHECK |
| Foreign keys | On by default | `PRAGMA foreign_keys = ON` required at connect |
| Updated-at trigger | `CREATE TRIGGER ... EXECUTE FUNCTION touch_updated_at()` | `CREATE TRIGGER ... BEGIN UPDATE ... END` |
| Cascading drops | `ON DELETE CASCADE` | Same, but only enforced with `foreign_keys = ON` |
| Reserved-word casing | Case-insensitive | Case-insensitive, but be consistent |
## Translation Rules
When authoring the Postgres version first:
1. **Strip schema prefix** for SQLite — `CREATE SCHEMA IF NOT EXISTS foo;`
and `SET search_path` are removed. Keep bare table names.
2. **Replace `BIGSERIAL`** with `INTEGER PRIMARY KEY AUTOINCREMENT`.
3. **Replace `TIMESTAMPTZ`** with `TEXT`; default becomes
`(strftime('%Y-%m-%dT%H:%M:%fZ','now'))`.
4. **Replace `BOOLEAN`** with `INTEGER` (values 0/1 at insert time — use an
adapter coercion).
5. **Drop `RESTART IDENTITY`** on truncates (SQLite uses
`DELETE FROM sqlite_sequence WHERE name = 'table'` instead).
6. **Rewrite function-style triggers** to inline `BEGIN ... END` trigger
bodies.
## File Layout
```
migrations/<feature>/
001-init.postgres.sql
001-init.sqlite.sql
002-next.postgres.sql
002-next.sqlite.sql
```
Numbered pairs. The adapter picks the right one based on `cfg.db.driver`.
## Adapter Runtime Responsibilities
The query adapter at runtime should:
- Rewrite `$N` placeholders → `?` for SQLite
- Strip `release_history.` schema prefix for SQLite
- Translate `now()` → `strftime(...)`, `::type` casts → drop
- Coerce JS booleans → 0/1 on param bindings
- Provide a portable `truncate(table)` helper
Reference implementation: `tools/release-history/db.mjs` in this repo.
## Workflow
1. Author the Postgres version first (richer type system reduces ambiguity).
2. Translate row-by-row using the rules above into the SQLite version.
3. Test both: apply each migration to a disposable DB, run schema-check
script against the adapter.
4. Commit both files together; never commit one without the other.
## Anti-patterns to avoid
- Single mixed-syntax file — both drivers will try to parse it and one or
both will fail.
- Runtime translation of whole migration files — too many edge cases.
- Letting the Postgres file diverge "just for now" — alignment rots quickly.
## Example (from this repo)
See `migrations/release-history/001-init.postgres.sql` and
`migrations/release-history/001-init.sqlite.sql`. Six tables (release,
feature, metric, retrospective, lesson, publish_target). Same semantics,
driver-idiomatic DDL.
## Related
- `env-setup` — database credentials and `.env` conventions
- `file-operation-patterns` — migration file naming discipline
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deployment | Stars: 322k | Updated | Claude CodeCodex | Verified | Install |
| xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |