# 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.2/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. |

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

---

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