database-json-schema

Instruction file imported from Tiag0ss/project-management (.cursor/rules/database-json-schema.mdc). Copyright stays with the author.

Database JSON schema

Skill: .cursor/skills/db-schema-json/SKILL.md

Source of truth

All system tables: server/database/structure/systemtables/{TableName}.json

Startup (schemaBuilder.buildAllTables) syncs JSON → database (create table / add missing columns). No manual SQL migration files for add/alter column.

New table template

{
  "TableName": "MyEntities",
  "PrimaryKeyFields": "Id",
  "Fields": [
    {
      "FieldName": "Id",
      "DataType": "int",
      "NotNullable": true,
      "AutoIncrement": true,
      "Comment": "Primary key"
    },
    {
      "FieldName": "Name",
      "DataType": "varchar(255)",
      "NotNullable": true
    },
    {
      "FieldName": "CreatedAt",
      "DataType": "timestamp",
      "NotNullable": true,
      "DefaultValue": "CURRENT_TIMESTAMP"
    }
  ]
}

Critical rules

Common data types

Adding a column

1. Edit the table's JSON file — add field object to Fields array.
2. Restart server (or run sync) — schemaBuilder adds column if missing.
3. Update route handlers + Zod schemas + frontend forms if user-facing.
4. Never create a one-off .ts migration script to alter columns.

Manual DB fixes

If production needs a column drop or destructive change:

• Provide raw SQL for the developer to run manually.
• Do not commit ad-hoc fix scripts to the repo.

MSSQL mapping (automatic)

schemaBuilder maps MySQL types for MSSQL: varchar→nvarchar, text→nvarchar(max), timestamp→datetime2, enum(...)→bounded nvarchar, etc.

Write JSON in MySQL-oriented types; builder handles MSSQL.

Status / lookup tables

Org-scoped value tables (e.g. TaskStatusValues, ProjectStatusValues):

• Always include OrganizationId FK.
• IsDefault, Color, sort order as per sibling tables.
• Seed defaults in route or startup — follow existing statusValues.ts patterns.

Custom fields / tables

• CustomFields — EAV metadata; values in entity rows or related tables.
• CustomTables — user-defined lookup tables; not in systemtables JSON beyond base tables.

After schema change checklist

• JSON valid + PrimaryKeyFields correct
• Portable types (avoid MySQL-only enums without builder support)
• Backend routes handle new field
• Frontend form/display if exposed
• Cache invalidation if list endpoints cache the entity
• docs/FEATURES.md only if user asked for doc updates