Database Migrations

MedRegia uses DbUp — a lightweight .NET library that tracks which SQL scripts have been applied and runs only the new ones on each startup.

Running Migrations

dotnet run --project backend/MedRegia.Migrations

DbUp is interactive — it shows which scripts will be applied and asks for confirmation before running.

Script Locations

MedRegia.Migrations/Scripts/
├── Schema/
│   ├── 01_Tables/      ← CREATE TABLE scripts
│   ├── 02_Indexes/     ← CREATE INDEX scripts
│   ├── 03_Views/
│   ├── 04_Functions/
│   ├── 05_StoredProcedures/
│   └── 06_Triggers/
├── Seed/
│   └── Demo/           ← Demo / seed data
└── Rollback/           ← One rollback script per forward script

Naming Convention

YYYY_MM_DD_NNN_Description.sql

Examples:

• 2026_03_01_001_CreateVendors.sql
• 2026_03_08_001_AddMetadataToAppSettings.sql
• 2026_03_17_002_AddCreatedByUpdatedBy.sql

Current Schema

The following tables exist (in creation order):

#	Table	Description
001	vendors	Pharmaceutical suppliers
002	invoices	Purchase invoices against vendors
003	transactions	Payment transactions against invoices
004	roles	RBAC roles
005	policies	Bundles of permissions
006	permissions	Individual collection + action pairs
007	users	User accounts
008	role_policies	Many-to-many: roles ↔ policies
009	user_policies	Many-to-many: users ↔ policies (direct assignment)
011	app_settings	Key-value configuration store

Standard Table Template

CREATE TABLE xxx (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Rollback Scripts

Every forward migration must have a matching rollback script in Scripts/Rollback/:

Forward	Rollback
2026_03_17_001_AddDeleteReasonToVendors.sql	2026_03_17_001_AddDeleteReasonToVendors.rollback.sql

Typical rollback:

-- Rollback for 2026_03_17_001_AddDeleteReasonToVendors.sql
ALTER TABLE vendors   DROP COLUMN IF EXISTS delete_reason;
ALTER TABLE invoices  DROP COLUMN IF EXISTS delete_reason;
ALTER TABLE transactions DROP COLUMN IF EXISTS delete_reason;

Never Edit Executed Scripts

Once a migration has been applied to any environment, never modify the script.
Always create a new script to amend the schema.

Adding a New Migration

1. Create the forward script in the appropriate Schema/ subfolder using the naming convention
2. Create the matching rollback script in Scripts/Rollback/
3. Run dotnet run --project backend/MedRegia.Migrations to apply
4. Commit both files together