# Schema generation

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

## Generation pipeline

When the CLI tool runs it follows these steps:

1. **Load the assembly** — reflects over the compiled DLL and finds all types that implement `IDbTable`
2. **Read column metadata** — calls `GetTableName()`, `GetColumns()`, and reads attribute annotations via `AssemblyAnalyzer`
3. **Map C# types to SQL types** — uses the built-in type mapping (see [Column types](/database/0.3.0/defining-models/columns))
4. **Emit DDL** — writes `CREATE TABLE`, `CREATE SEQUENCE`, constraint clauses, and seed `INSERT` statements
5. **Diff against baseline** — if a previous migration exists, computes the delta and emits `ALTER TABLE` statements

---

## 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 START 1;
```

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