Schema generation

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

updated 2 Sept 20268 min readv0.3.8View as Markdown

Generation pipeline

When the tool runs it follows these steps:

Load the assembly. It opens the compiled DLL in a metadata-only reflection context (MetadataLoadContext).
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.
Map C# types to SQL types using the built-in type mapping (see Column types).
Emit DDL. It writes CREATE TABLE, CREATE SEQUENCE, constraint clauses, and seed INSERT statements.
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:MANUAL]`	A change the generator cannot express in SQL at all	The migration is still written, so the change has a schema artefact to hang on and shows up in review, but it carries no DDL — an application-level pass is required. Currently emitted for a change of encryption profile.

-- [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.

Changes the generator refuses

A marker says "this SQL is correct but consequential — read it before applying". Some changes have no correct SQL at all, and for those the generator refuses: it prints what it cannot express and what to do instead, and exits without writing the migration file or advancing structure.json. Fixing the model and re-running therefore produces one migration, not a second one competing with a half-written first.

Encrypting an existing column is refused

Adding [Encrypted] to a populated text column is a text → bytea change. The generic in-place cast for that is USING "col"::bytea, which cannot do the job in either direction:

text::bytea is an I/O-conversion cast, so PostgreSQL hands the text to byteain. Any value containing a backslash aborts the whole migration with invalid input syntax for type bytea.
Where it does succeed it stores readable plaintext in a column the model thereafter reports as encrypted. Nothing fails until the first typed read, which can be months later.

SQL cannot encrypt: only the application holds the key. So the generator stops and describes the two-phase shape instead:

Cannot encrypt the existing column "notes"."body" in a migration: text -> bytea has no valid in-place
cast, and SQL cannot encrypt (only the application holds the key). Do it in two phases instead:
(1) rename "body" to a temporary plaintext column and add the new [Encrypted] "body" as nullable;
(2) backfill by reading each row and re-writing it through the typed row set, so the ciphertext carries
the right profile, key id and table:column context; (3) once every row is written, drop the temporary
column and set "body" NOT NULL. Hand-author that migration; this tool will not guess at it.

Backfill through the typed row set rather than by calling the encryptor by hand — that is what makes the ciphertext carry the right profile, key id, and table:column associated data.

Removing [Encrypted] is refused for the same reason in reverse: casting raw ciphertext bytes to text produces unreadable values, not the original plaintext.

NOTE

This applies only to columns the model marks [Encrypted]. An ordinary byte[] column changing to or from bytea is unaffected and still generates as before.

Changing a column's encryption profile

Moving a column from the default profile to a named one — or between two named ones — changes which key and which code path the data lives under. Both sides are bytea, so there is no type change for the diff to see.

The generator records the profile in structure.json and emits a [SOCIGY:MANUAL] comment in both directions rather than DDL:

-- [SOCIGY:MANUAL] "notes"."body" moves from encryption profile the default to "highsec". Existing rows
-- still hold ciphertext the new profile cannot read, and no SQL can fix that — the key is only reachable
-- from the application. Run a re-encryption pass (read and re-write each row through the typed row set)
-- before or immediately after this migration.

The migration is still generated, so the change is visible in review and has a place in the chain. Running the re-encryption pass is yours; see Encrypted columns → per-column profiles.

NOTE

Snapshots written before 0.3.8 record no encryption information, and an absent value means "not recorded" rather than "not encrypted". The first generation after upgrading therefore records the profiles without reporting a change, so upgrading does not produce a [SOCIGY:MANUAL] for every encrypted column you already have.

A migration that would fork the chain is refused

Each migration records the id of the one before it, taken from structure.json, and the snapshot only advances after the file is written. So an abandoned attempt left in Socigy/Migrations/ still claims the parent the next attempt will take, and because the filename carries a fresh timestamp the tool cannot overwrite its own earlier emission either. The result is two migrations with the same parent — a fork, which fails at apply time and takes the whole module's schema with it.

Before writing, the generator now reads the migrations already on disk and refuses if one already follows the same parent, naming the file:

A migration already follows '20260731120000_Initial':
20260731155951_AddObligations.g.cs. Writing another would fork the chain, which fails at apply time and
takes the whole module's schema with it. If that file was an abandoned attempt at this same change, delete
it and re-run. If it was applied, the schema snapshot (Socigy/structure.json) has fallen behind it —
restore the snapshot that file was generated against rather than regenerating.

It also validates the existing chain as a whole, so a fork already on disk is reported here rather than at apply time — appending to a chain that cannot be applied is not progress.

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)
);

← previousCLI tool

next →Applying migrations