add-db-table
Skill3 starsChanged 21 days agoMITView on GitHub
Add or change a Greenhouse database table end to end: core tables in packages/db (schema with _columns helpers + ids prefix, domain service, provider registration, drizzle-kit generate into packages/db/migrations, pnpm db:migrate, db-schema.md, *.db.test.ts) or plugin tables in plugins/<id> (own schema, service bag, NNNN_name.sql lane file). Use for: add a table / column / migration / service, 新增表, 加个表, 建张表, 加字段要迁移, 写个 service. Red lines: migrate never push, never edit an applied migration, no repository classes or interface mirrors, types are inferred, core never gets plugin tables or plugin FKs.
In this repository8 other files it gives its agents.
- AGENTS.mdAGENTS.md
- CLAUDE.mdCLAUDE.md
- add-agent-profileSkill
- add-agent-toolSkill
- add-api-routeSkill
- add-client-screenSkill
- full-featureSkill
- quality-gateSkill
The file
We split this skill into the rules it gives an agent
12 rules. About 0 are common, 7 are distinctive, and 5 are very specific to this project.
≈0
Common · estimate
Many files like this have a rule of this kind.
≈7
Distinctive · estimate
This author's own choices. Worth a look.
5
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Very specific: line 27
Every table declares its indexes.
- Distinctive: line 35
Update db-schema.md: purpose, key columns, FK table, logical links, diagram.
- Distinctive: line 40
No interface layers, repository classes or multi-backend abstractions.
- Very specific: line 43
Write timestamps with nowIso() (@greenhouse/utils/date).
- Distinctive: line 53
Never edit an applied lane file or create a core-owned table in a lane.
- Distinctive: line 56
Document the table in plugins/<id>/db-schema.md and the plugin README.
- Distinctive: line 60
Call initDatabase() in beforeEach, never in beforeAll or module scope.
- Very specific: line 61
Create members with tests/helpers/internal-user.ts.
- Very specific: line 77
Never edit 0000_v4_baseline.sql, migrations/archive-3x/ or any applied migration bytes.
- Distinctive: line 78
Never push a schema to a persistent or shared database.
- Very specific: line 79
Never run migrations from a worktree against the main database.
- Distinctive: line 80
No organizations table or org_id column.
≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Add a database table
Source of truth: packages/db/src/AGENTS.md (rules) and packages/db/src/db-schema.md (tables, FKs, logical links); plugin lanes: EXTENDING.md "Database ownership and migrations". On conflict those files win.
Decide first
- Core or plugin: only governance-core data (identity, conversations, runs, Files, notifications, usage, OAuth, settings…) goes in packages/db; everything else lives in plugins/
<id>/ and its lane. - Reuse first: check db-schema.md for an existing table to extend; a second table for the same job needs its reason in the relevant AGENTS.md.
- Relationships: FK with onDelete for strong ownership inside one domain; audit, usage, notification and cross-domain references stay logical (no FK), recorded in db-schema.md.
- Core never acquires an FK to a plugin table; plugin tables may reference core (users).
Core table
- Schema: packages/db/src/schema/
<domain>.ts (update the file's table list), exported from schema/index.ts; Row typetypeof table.$inferSelectat the bottom. - Columns only from schema/_columns.ts: id(), at(), day(), createdAt(), updatedAt(), json(name, fallback), jsonOrNull(), oneOf(), searchColumns().
- Enums:
text('col', { enum: VALUES })plusoneOf('chk_<table>_<col>', col, VALUES); floats doublePrecision(); counts and moneybigint({ mode: 'number' }). - Every table declares its indexes; names
idx_*,uq_*,chk_*; never order by id (use created_at, seq, position, sort_order). - Ids: register a three-letter prefix in ID_PREFIX (packages/db/src/ids.ts); services write
newId(kind), orstableId(kind, seed)when the id must be recomputable. - Service: packages/db/src/services/
<domain>.ts exportingcreateXxxService(db: Db)that returns an object literal (const service = {...}; return service;, no this). - Register it in packages/db/src/provider.ts and export input types from the package root (packages/db/src/index.ts); DatabaseProvider is inferred.
- Generate at the repo root:
pnpm drizzle-kit generate(drizzle.config.ts → packages/db/migrations/NNNN_*.sql plus meta snapshot). - Review the SQL: casts missing USING, NOT NULL without DEFAULT on populated tables, renames generated as drop + add (hand-edit to RENAME), backfills.
- Apply:
DATABASE_URL=<your own local db> pnpm db:migrate; never baredrizzle-kit migrate(the config falls back to the main DB) and never push to a persistent or shared DB. - Fast-growing tables get a window in RETENTION_POLICY (packages/db/src/services/retention.ts) and the db-schema.md retention section.
- Update db-schema.md: purpose, key columns, FK table, logical links, diagram.
Service rules
- Business code (routes, tools, CLI) never writes SQL; it calls
getDb().<domain>.<method>(). - Types are inferred: Row = $inferSelect, union aliases =
XxxRow['col'], parameter types beside the service; no hand-written mirrors. - No interface layers, repository classes or multi-backend abstractions.
- Query-builder jsonb reads are parsed, raw
db.execute(sql…)reads are text: read both with safeJsonParse (@greenhouse/utils/json); raw writes use${JSON.stringify(v)}::jsonb. - Raw COUNT/SUM/bigint results are strings: wrap in Number().
- Write timestamps with nowIso() (@greenhouse/utils/date); set updated_at explicitly on update.
- Secrets live in
_enccolumns (PROVIDER_TOKEN_ENCRYPTION_KEY); anything hashable stores only SHA-256.
Plugin table
- Schema in plugins/
<id>/api/schema.ts using the kit's column helpers (at, createdAt, id, json, oneOf, updatedAt, users from @greenhouse/api/extension-kit). - New plugins name tables
ext_<id>_*; official plugins keep their existing names. - Services: a factory passed as
services:in defineExtension and a typed accessor over getExtensionServices (plugins/memory/api/services.ts); plugin services are absent from DatabaseProvider. - Lane:
migrations: { dir: extensionPath(import.meta.url, '../migrations') }; files are plugins/<id>/migrations/NNNN_name.sql separated by--> statement-breakpoint, checksummed in extension_migrations. - Draft SQL with the plugin's own config (
pnpm drizzle-kit generate --config plugins/<id>/drizzle.config.ts), then keep only the reviewed delta as the next NNNN_name.sql; the lane runs every *.sql in the folder, so remove any other generated output. - Guard DDL with IF NOT EXISTS where a 3.x database may already hold the table (see plugins/*/migrations/0001_adopt.sql).
- Never edit an applied lane file or create a core-owned table in a lane.
- Register owned tables in resetTables; retention goes in the plugin's
retention: [{ table, column, days }]. - Table-backed data-source creates need an id field with a database-generated key or default.
- Document the table in plugins/
<id>/db-schema.md and the plugin README.
Tests
- DB tests are
*.db.test.ts(tests/db/ for core, plugins/<id>/api/tests/ for plugins); each test runs in a rolled-back transaction. - Call initDatabase() in beforeEach, never in beforeAll or module scope; never resetSchema().
- Create members with tests/helpers/internal-user.ts; narrow one with moveToRestrictedRole() (tests/helpers/restricted-role.ts).
- Assert only rows this test created; take ids from create's return value.
*.db-commit.test.tsonly for cross-connection visibility, DDL or locks, with@db-commit-reasonin the header; DDL and lifecycle fixtures use tests/helpers/isolated-db.ts.- tests/db/schema-equivalence.db-commit.test.ts must keep passing (fresh 4.0 + all official plugins = bridged 3.x); record intended differences in INTENTIONAL_DIFFERENCES.
Gate
pnpm typecheck
pnpm lint
TEST_DATABASE_URL=<loopback db named *test*> pnpm test:db # focused
TEST_DATABASE_URL=<loopback db named *test*> pnpm test # before handoff
./node_modules/.bin/drizzle-kit check # core journal and snapshots agreePlugin lanes also run pnpm plugins:sync --check and pnpm test:core (see quality-gate).
Red lines
- Never edit 0000_v4_baseline.sql, migrations/archive-3x/ or any applied migration bytes.
- Never push a schema to a persistent or shared database; migrations are the only source of truth.
- Never run migrations from a worktree against the main database; always set DATABASE_URL explicitly.
- No organizations table or org_id column; no columns "for later".
- Deleted features do not come back as compatibility tables.
Checklist
- Core vs plugin decided; reuse considered and recorded
- Schema with helpers, indexes and CHECKs; prefix registered (core)
- Service factory registered (provider.ts or plugin services)
- Migration generated or written, reviewed, applied to your own DB
- Retention decided; db-schema.md (core or plugin) updated
- DB tests at the right layer; schema-equivalence green; gate green
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

