# Schema generation

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

## 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](/database/0.3.8/defining-models/columns)).
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: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](#changing-a-columns-encryption-profile). |

```sql
-- [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:

```plaintext
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:

```sql
-- [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](/database/0.3.8/defining-models/encrypted-columns#per-column-encryptor-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:

```plaintext
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

```sql
PRIMARY KEY ("id")

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

---

## Foreign key DDL

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

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

---

## Constraint DDL

```sql
-- [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`):

```sql
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:

```sql
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:

```sql
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:

```csharp
[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:

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