/DB

Schema generation

How the migration tool derives DDL from annotated C# models, and what each attribute produces.

updated 6 Jun 20264 min readv0.3.2View as Markdown

Generation pipeline

When the tool runs it follows these steps:

  1. Load the assembly. It opens the compiled DLL in a metadata-only reflection context (MetadataLoadContext).
  2. Find the tables. It selects every concrete class and enum carrying a [Table] or [FlagTable] attribute, then reads each column's metadata from its attributes.
  3. Map C# types to SQL types using the built-in type mapping (see Column types).
  4. Emit DDL. It writes CREATE TABLE, CREATE SEQUENCE, constraint clauses, and seed INSERT statements.
  5. Diff against the baseline. If a previous structure.json exists, it computes the delta and emits ALTER TABLE statements for the changed parts only.

Destructive and lossy changes

Data-losing statements are flagged in the generated migration so they are obvious in code review, and the CLI prints a summary of them at generation time.

Marker Emitted for Notes
-- [SOCIGY:DESTRUCTIVE] DROP TABLE (removed table), DROP COLUMN (removed column) The DOWN script restores schema and seed data only — runtime rows are not recoverable. DROP TABLE uses CASCADE, which also drops dependent objects.
-- [SOCIGY:LOSSY] ALTER COLUMN ... TYPE that is not a known-safe widening The in-place USING col::newtype cast may fail or truncate on existing rows. Safe widenings (e.g. smallint→integer→bigint, real→double precision, any→text) are not flagged. The marker is emitted per direction, so a safe UP may still flag its narrowing DOWN. 
-- [SOCIGY:DESTRUCTIVE] Drops column "users"."legacy_note"; its data cannot be recovered by the DOWN script.
ALTER TABLE "users" DROP COLUMN "legacy_note";

Search a migration for [SOCIGY: before applying it to review every data-losing step. These markers are SQL comments and have no runtime effect.

TIP
Grep migrations in CI (grep -R "\[SOCIGY:DESTRUCTIVE\]" Socigy/Migrations) to require human sign-off on data-losing changes.

Column DDL rules

Condition DDL emitted
Property with no attributes "col_name" TYPE NOT NULL (no DEFAULT)
T? nullable annotation ... NULL instead of NOT NULL
[Default(DbDefaults.Guid.Random)] DEFAULT gen_random_uuid()
[Default(DbDefaults.Time.Now)] DEFAULT timezone('utc', now())
[Default("expr")] DEFAULT expr
[Default] (no argument) No DEFAULT clause; ExcludeAutoFields() still skips it
C# initializer = "value" (non-zero/non-empty) DEFAULT 'value'
[AutoIncrement] DEFAULT nextval('table_col_seq')

Primary key DDL

PRIMARY KEY ("id")

-- Composite
PRIMARY KEY ("user_id", "course_id")

Foreign key DDL

FOREIGN KEY ("user_id") REFERENCES "users"("id")

-- With referential action
FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE

Constraint DDL

-- [Unique]
UNIQUE ("email")

-- [Unique(nameof(Col1), nameof(Col2), Name = ...)]
CONSTRAINT "uq_post_slug" UNIQUE ("author_id", "slug")

-- [Check("expr")]
CHECK (LENGTH("email") < 100)

-- [StringLength(200, MinLength = 1)]
CHECK (LENGTH("title") <= 200)
CHECK (LENGTH("title") >= 1)

-- [Min(0)]
CHECK ("quantity" >= 0)

Enum reference table DDL

For a [Table]-annotated enum the CLI creates a three-column table: id (the underlying integer), value (the member name), and description (from [Description("...")] attributes, or NULL):

CREATE TABLE IF NOT EXISTS "roles" (
    "id"          INTEGER NOT NULL,
    "value"       TEXT    NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);
INSERT INTO "roles" ("id", "value", "description") VALUES
    (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL)
ON CONFLICT ("id") DO UPDATE SET "value" = EXCLUDED."value", "description" = EXCLUDED."description";

FlaggedEnum junction table DDL

For a [FlaggedEnum] property on a model:

CREATE TABLE IF NOT EXISTS "users_roles" (
    "users_id"  UUID    NOT NULL,
    "roles_id"  INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("roles_id") REFERENCES "roles"("id")
);

Sequence DDL

For an [AutoIncrement] column:

CREATE SEQUENCE IF NOT EXISTS "table_col_seq" AS INTEGER;

The sequence is always created before the table that references it.

Full example

Given this model:

[Table("posts")]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_post_slug")]
public partial class Post
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid AuthorId { get; set; }

    [StringLength(200, MinLength = 1)]
    public string Title { get; set; }

    [Column("slug"), Unique]
    public string Slug { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime PublishedAt { get; set; }

    public DateTime? DeletedAt { get; set; }
}

The tool emits:

CREATE TABLE IF NOT EXISTS "posts" (
    "id"           UUID      NOT NULL DEFAULT gen_random_uuid(),
    "author_id"    UUID      NOT NULL,
    "title"        VARCHAR(200) NOT NULL,
    "slug"         VARCHAR(200) NOT NULL,
    "published_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    "deleted_at"   TIMESTAMP,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE CASCADE,
    UNIQUE ("slug"),
    CONSTRAINT "uq_post_slug" UNIQUE ("author_id", "slug"),
    CONSTRAINT "chk_posts_title" CHECK (LENGTH("title") <= 200),
    CONSTRAINT "chk_posts_title_1" CHECK (LENGTH("title") >= 1)
);