# Database-first scaffolding

Generate a schema manifest and annotated C# classes from an existing PostgreSQL database.

## Overview

The library is code-first by default: you annotate C# classes and the generator produces the data layer and [migrations](/database/0.3.3/migration/cli-tool). For an **existing** database, the CLI tool can work in the other direction — reading a live schema and scaffolding it back into the same model the analyzer understands.

> **NEW in 0.3.3**

Two sub-commands of the migration tool cover this:

| Command | Input | Output |
|---------|-------|--------|
| `scaffold schema` | A live PostgreSQL connection | A `structure.json` schema manifest |
| `scaffold classes` | A live connection **or** a `structure.json` | Annotated `[Table]` C# classes |

Both reuse the same schema model and SQL generator as the code-first pipeline, so the result round-trips: scaffolded classes, recompiled and run back through [`generate`](/database/0.3.3/migration/cli-tool), reproduce the same schema.

## scaffold schema

Read a database and write a `structure.json`:

```bash
socigydb scaffold schema \
  --connection "Host=localhost;Username=postgres;Password=...;Database=app" \
  --schema public \
  --output ./structure.json
```

| Option | Description |
|--------|-------------|
| `--connection` | PostgreSQL connection string (required) |
| `--schema` | Database schema to read (default `public`) |
| `--output` | Output path (default `./structure.json`) |

## scaffold classes

Generate annotated C# classes, either directly from a database or from a previously-written `structure.json`:

```bash
# From a live database
socigydb scaffold classes \
  --connection "Host=localhost;Username=postgres;Password=...;Database=app" \
  --output ./Models \
  --namespace MyApp.Data

# From an existing schema manifest
socigydb scaffold classes \
  --from-schema ./structure.json \
  --output ./Models \
  --namespace MyApp.Data
```

| Option | Description |
|--------|-------------|
| `--connection` | PostgreSQL connection string (omit when using `--from-schema`) |
| `--from-schema` | Read from an existing `structure.json` instead of a live database |
| `--schema` | Database schema to read (default `public`) |
| `--output` | Output directory for the generated classes (required) |
| `--namespace` | Namespace for the generated classes (default: derived from the output directory) |

One `{ClassName}.cs` file is written per table.

## What is recovered

The reader maps PostgreSQL metadata back to the attributes the analyzer reads, including:

- Tables and columns, with CLR types inferred from the SQL types.
- Primary keys (`[PrimaryKey]`) and auto-increment / identity columns (`[AutoIncrement]`).
- `NOT NULL` → non-nullable C# types; nullable columns → `T?`.
- `varchar(n)` length → `[StringLength(n)]`.
- Recognized defaults mapped back to their `DbDefaults` token — for example `gen_random_uuid()` → `[Default(DbDefaults.Guid.Random)]`, `timezone('utc', now())` → `[Default(DbDefaults.Time.Now)]`. Unrecognized defaults are preserved verbatim.
- `jsonb` columns → `[RawJsonColumn]`.
- Foreign keys → `[ForeignKey(typeof(Target), Keys = [...], TargetKeys = [...])]`, including the `ON DELETE` action.
- A `[Column("…")]` override is emitted only when the database column name does not match the default snake_case of the property, keeping the output clean.

## Limitations

Some information cannot be reconstructed from the database alone and is scaffolded conservatively:

- **`[Encrypted]` columns** are stored as `bytea`, indistinguishable from a genuine `byte[]` column. They scaffold as `byte[]`; re-apply `[Encrypted]` by hand where appropriate.
- **Enum-backed columns** scaffold as their underlying primitive.
- **`CHECK` constraints** and database enum types are not yet scaffolded.

Treat scaffolding as a starting point: review the generated classes, then continue code-first from there.