migrations
Skill0 starsChanged 7 months agoMITView on GitHub
Create and manage SQL migrations for the Platform healthcare SaaS backend (Azure SQL Server). Handles tenant and platform schema changes, permission inserts, table creation, and tables.py sync. USE FOR: create migration, add column, alter table, create table, drop column, add index, add permission, new permission, schema change, database change, modify table, rename column, add foreign key, migration file, SQL migration, tenant table, platform table. DO NOT USE FOR: writing application code (use copilot-instructions.md), creating mockups (use mockup-guidelines skill), code review (use code-review skill).
8 other projects use a version of this.
Adopted by
Most recently taken up by
- theopenco/llmgatewaytheir own version, first seen Nov 15, 2026
- AnyOrgName/php-skillstheir own version, first seen Nov 8, 2026
- butterbase-ai/butterbase-plugintheir own version, first seen Oct 8, 2026
- orangehrm/orangehrmtheir own version, first seen Oct 8, 2026
- dotnet/efcoretheir own version, first seen Oct 8, 2026
- nahid-sparktales/agent-dispatchertheir own version, first seen Sep 21, 2026
Used with
Projects with it also use schema-design.
Other files people use alongside this one.
Files found in the same public projects as a version of this one, more often than they turn up anywhere else. Each project counts once, large collections count for less, and this file's own repository is left out.
HamzaSheikh768Skillschema-design
Designs normalized, performant, and future-proof PostgreSQL database schemas with proper normalization, indexing strategy, constraints, partitioning considerations, and scalability planning.
Also in 3 projects that use this
Other versions8 projects use a version of this.
Word for word · 0
None found.
Edited · 0
None found.
Wrote their own version · 8
- dotnet/efcore★ 15ksame name
- theopenco/llmgateway★ 1.7ksame name
- orangehrm/orangehrm★ 1.1ksame name
- butterbase-ai/butterbase-plugin★ 535same name
- butterbase-ai/butterbase-skills★ 535same name
- nahid-sparktales/agent-dispatcher★ 59same name
- AnyOrgName/php-skills★ 4same name
- hallucinaut/skills★ 0same name
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository8 other files it gives its agents.
- .github/copilot-instructions.mdCopilot instructions
- ai-service-repo-task-generatorSkill
- api-patternsSkill
- code-reviewSkill
- frontend-repo-task-generatorSkill
- mockup-guidelinesSkill
- new-moduleSkill
- update-knowledgeSkill
The file
Show its rules and text
We split this skill into the rules it gives an agent
19 rules. 0 are common, 6 are distinctive, and 13 are very specific to this project.
0
Common
Most of the 5 edited versions we read keep these.
6
Distinctive
This author's own choices. Worth a look.
13
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 22
New permissions needed?: If so, which roles should get them?
- Very specific: line 23
Does this affect an existing table?: If so, check the current DDL in app/tenant/tables.py or existing migrations.
- Very specific: line 50
Place new migrations in _migrations/ (root level, NOT in _migrations/2025_and_older/)
- Distinctive: line 56
Every migration file should follow this structure:
- Distinctive: line 69
If there are only platform changes, omit the tenant section (and vice versa).
- Very specific: line 107
Always include: CREATED_BY, CREATED_AT, UPDATED_BY, UPDATED_AT
- Very specific: line 108
Use NVARCHAR for text (Unicode support), VARCHAR only for ASCII-only fields like emails
- Very specific: line 109
Use DECIMAL(18,4) for money/percentage values, never FLOAT
- Very specific: line 110
Use DATETIME2 for timestamps, not DATETIME
- Very specific: line 111
Use BIT for booleans with DEFAULT 0 or DEFAULT 1
- Very specific: line 139
If you need a NOT NULL column, add it as NULL first, backfill data, then alter to NOT NULL
- Distinctive: line 159
Which roles?: Check with the user. Common patterns:
- Very specific: line 189
When creating multiple tables with FK dependencies in a single migration, the referenced (parent) table must be created before the …
- Very specific: line 222
When you create or modify a tenant table in a migration, you MUST also update app/tenant/tables.py.
- Very specific: line 226
When a new tenant is provisioned, the system runs get_tenant_tables_sql(schema_name) from tables.py to create all tables in the new tenant …
- Very specific: line 232
Add your new table's CREATE TABLE and CREATE INDEX statements
- Distinctive: line 245
After writing the migration, verify:
- Distinctive: line 263
Don't edit applied migrations: Always create a new file.
- Very specific: line 268
Don't hardcode schema names: Use {schema_name} for tenant, PLATFORM.
“Common” is measured: kept by at least 6 in 10 of the 5 edited versions of this file we could read. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Migrations — Platform Backend
CRITICAL: Getting migrations wrong can silently break tenant provisioning, corrupt data, or cause production failures. Follow every step carefully.
0. Before You Start — Clarifying Questions
Always confirm before writing a migration:
- What is the change? New table, new column, new index, new permission, alter existing column?
- Tenant-scoped or platform-scoped? This determines whether you use
{schema_name}placeholder orPLATFORM.prefix. - Which app? (ValueIQ/RebateIQ/AccessIQ) — affects permission role assignments.
- New permissions needed? If so, which roles should get them?
- Does this affect an existing table? If so, check the current DDL in
app/tenant/tables.pyor existing migrations.
1. Migration File Naming
Convention: YYYY_N_SEQ_REV.sql
| Part | Meaning | Example |
YYYY | Year | 2026 |
N | Major version | 2 (current) |
SEQ | Sequence number (increments globally) | 49 |
REV | Revision (usually 1) | 1 |
Finding the Next Sequence Number
Always check _migrations/ for the highest current SEQ before creating a new file.
ls -1 _migrations/*.sql | sort -t_ -k3 -n | tail -3Increment the highest SEQ by 1. For example, if the latest is 2026_2_48_1.sql, the next file is 2026_2_49_1.sql.
File Location
- Place new migrations in
_migrations/(root level, NOT in_migrations/2025_and_older/)
2. Migration File Structure
Every migration file should follow this structure:
-- ========== PLATFORM MIGRATIONS ==========
-- [Platform-scoped changes here, if any]
-- ========== TENANT MIGRATIONS ==========
-- NOTE: Make sure app/tenant/tables.py is in sync
-- [Tenant-scoped changes here, if any]If there are only platform changes, omit the tenant section (and vice versa). But always include the section header comments for clarity.
3. Platform vs. Tenant — Decision Guide
| Scope | Schema Prefix | When to Use | Examples |
| Platform | PLATFORM.TABLE_NAME | Shared across all tenants: users, roles, orgs, audit logs, system config | PLATFORM.USERS, PLATFORM.ROLES, PLATFORM.ROLE_PERMISSION |
| Tenant | {schema_name}.TABLE_NAME | Per-tenant data isolation: business entities, app-specific data | {schema_name}.VBC_PARTICIPANT, {schema_name}.REBATE_CONTRACT |
Key rule: Tenant tables use {schema_name} placeholder — this gets replaced at runtime with the actual tenant schema name (e.g., TENANT_ABC123).
4. Common Migration Patterns
4.1 Create a New Tenant Table
CREATE TABLE {schema_name}.[NEW_TABLE_NAME] (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TENANT_APP_ID INT NOT NULL,
NAME NVARCHAR(255) NOT NULL,
STATUS NVARCHAR(50) NOT NULL DEFAULT 'Active',
CREATED_BY NVARCHAR(100) NOT NULL,
CREATED_AT DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
UPDATED_BY NVARCHAR(100) NULL,
UPDATED_AT DATETIME2 NULL
);
CREATE INDEX IDX_NEW_TABLE_NAME_TENANT_APP_ID ON {schema_name}.[NEW_TABLE_NAME](TENANT_APP_ID);
CREATE INDEX IDX_NEW_TABLE_NAME_STATUS ON {schema_name}.[NEW_TABLE_NAME](STATUS);Conventions:
- Table names:
UPPER_SNAKE_CASE, wrapped in[] - Primary key:
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY - Always include:
CREATED_BY,CREATED_AT,UPDATED_BY,UPDATED_AT - Use
NVARCHARfor text (Unicode support),VARCHARonly for ASCII-only fields like emails - Use
DECIMAL(18,4)for money/percentage values, neverFLOAT - Use
DATETIME2for timestamps, notDATETIME - Use
BITfor booleans withDEFAULT 0orDEFAULT 1 - Index naming:
IDX_TABLE_NAME_COLUMN_NAME
4.2 Create a New Platform Table
CREATE TABLE PLATFORM.[NEW_TABLE_NAME] (
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
-- columns...
CREATED_AT DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);No {schema_name} placeholder — use PLATFORM. directly.
4.3 Add Column to Existing Table
-- Tenant table
ALTER TABLE {schema_name}.[EXISTING_TABLE] ADD NEW_COLUMN NVARCHAR(255) NULL;
-- Platform table
ALTER TABLE PLATFORM.[EXISTING_TABLE] ADD NEW_COLUMN NVARCHAR(255) NULL;Rules:
- New columns on existing tables should almost always be
NULL(existing rows won't have the value) - If you need a
NOT NULLcolumn, add it asNULLfirst, backfill data, then alter toNOT NULL
4.4 Add Permissions
Permissions are inserted directly into PLATFORM.ROLE_PERMISSION. There is NO separate permissions table.
INSERT INTO PLATFORM.ROLE_PERMISSION (ROLE_ID, PERMISSION_NAME) VALUES
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Super Admin'), 'MODULE_NAME.VIEW'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Super Admin'), 'MODULE_NAME.CREATE'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Super Admin'), 'MODULE_NAME.EDIT'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Super Admin'), 'MODULE_NAME.DELETE'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Security Admin'), 'MODULE_NAME.VIEW'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Security Admin'), 'MODULE_NAME.CREATE'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Security Admin'), 'MODULE_NAME.EDIT'),
((SELECT ID FROM PLATFORM.ROLES WHERE NAME = 'Security Admin'), 'MODULE_NAME.DELETE');Permission naming: ENTITY_NAME.ACTION with dot separator (e.g., REBATE_GTN_MODELLING.VIEW, HEALTH_PLAN.EDIT). A few legacy permissions exist without dots (e.g., VBC_CONFIGURATION, VIEW_ALL_ORGANIZATIONS) — do not follow that pattern for new permissions.
Which roles? Check with the user. Common patterns:
- Super Admin and Security Admin — almost always get all permissions
- App-specific admin (e.g.,
Manufacturer RebateIQ Admin) — gets permissions for that app - Consult
app/role/constants.py→BuiltInSystemRolesfor the full role list
After adding permissions: Also add the new permission values to BuiltInPermissions enum in app/role/constants.py.
4.5 Add Index
CREATE INDEX IDX_TABLE_COLUMN ON {schema_name}.[TABLE_NAME](COLUMN_NAME);
-- With covering columns for query optimization
CREATE INDEX IDX_TABLE_COLUMN ON {schema_name}.[TABLE_NAME](COLUMN_NAME)
INCLUDE (OTHER_COL1, OTHER_COL2);4.6 Add Foreign Key
ALTER TABLE {schema_name}.[CHILD_TABLE]
ADD CONSTRAINT FK_CHILD_PARENT
FOREIGN KEY (PARENT_ID) REFERENCES {schema_name}.[PARENT_TABLE](ID);For cascading deletes: ON DELETE CASCADE
4.8 Foreign Key Ordering in Migrations
When creating multiple tables with FK dependencies in a single migration, the referenced (parent) table must be created before the referencing (child) table. Plan the CREATE TABLE order based on the FK dependency graph.
-- CORRECT: IW_GAP created before IW_PAYER_REQUEST (which references it via GAP_ID)
CREATE TABLE {schema_name}.[IW_GAP] ( ... );
CREATE TABLE {schema_name}.[IW_PAYER_REQUEST] (
...
GAP_ID INT NULL FOREIGN KEY REFERENCES {schema_name}.[IW_GAP](ID),
...
);
-- WRONG: Would fail because IW_GAP doesn't exist yet
CREATE TABLE {schema_name}.[IW_PAYER_REQUEST] (
GAP_ID INT NULL FOREIGN KEY REFERENCES {schema_name}.[IW_GAP](ID)
);
CREATE TABLE {schema_name}.[IW_GAP] ( ... );Also applies to tables.py: The table creation order in get_tenant_tables_sql() must respect the same FK dependency order.
4.7 Drop with Safety
DROP INDEX IF EXISTS IDX_OLD_INDEX ON {schema_name}.[TABLE_NAME];
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('{schema_name}.TABLE_NAME') AND name = 'OLD_COLUMN')
ALTER TABLE {schema_name}.[TABLE_NAME] DROP COLUMN OLD_COLUMN;5. The tables.py Sync Rule (CRITICAL)
When you create or modify a tenant table in a migration, you MUST also update app/tenant/tables.py.
Why?
When a new tenant is provisioned, the system runs get_tenant_tables_sql(schema_name) from tables.py to create all tables in the new tenant schema. If tables.py doesn't include your new table, new tenants will be missing it — and this fails silently.
What to Do
- Open
app/tenant/tables.py - Find the appropriate location (tables are roughly grouped by module)
- Add your new table's
CREATE TABLEandCREATE INDEXstatements - For column additions (ALTER TABLE in migration): Do NOT add a separate
ALTER TABLEintables.py. Instead, modify the originalCREATE TABLEstatement inline to include the new column.tables.pyrepresents the final schema for new tenants — it should read as cleanCREATE TABLEDDL, not a series of migrations. - The SQL in
tables.pyuses{schema_name}f-string interpolation (same as migrations)
Does NOT Apply To
- Platform tables (
PLATFORM.*) — these are not per-tenant - Index-only changes on existing tables (indexes are not always in
tables.py, but new tables must be)
6. Post-Migration Checklist
After writing the migration, verify:
- File named correctly (
YYYY_N_SEQ_REV.sql) with correct next sequence number - Placed in
_migrations/(not in2025_and_older/) - Platform tables use
PLATFORM.TABLE_NAME - Tenant tables use
{schema_name}.TABLE_NAME - Section header comments present (
-- ========== PLATFORM MIGRATIONS ==========) - New tenant tables/columns also added to
app/tenant/tables.py - New permissions also added to
BuiltInPermissionsinapp/role/constants.py - New columns on existing tables default to
NULL DATETIME2used (notDATETIME),NVARCHARused for Unicode textDECIMAL(18,4)used for money/percentages (notFLOAT)- Indexes created for foreign keys and frequently-queried columns
7. Don'ts
- Don't edit applied migrations. Always create a new file.
- Don't skip the sequence number check. Duplicate or gap sequences cause confusion.
- Don't forget
tables.py. This is the #1 migration mistake — new tenants silently miss tables. - Don't use
FLOATfor money. Precision loss. UseDECIMAL(18,4). - Don't add
NOT NULLcolumns to existing tables without a default or backfill plan. - Don't hardcode schema names. Use
{schema_name}for tenant,PLATFORM.for platform. - Don't create a separate PERMISSIONS table. Permissions go directly into
ROLE_PERMISSION.
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.

