# Tutorial: databases

Two walkthroughs for a source that is not a CSV:

1. [One database](#1-one-database) — a working copy of a production database
2. [Several databases](#2-several-databases) — one identity per person, across all of them

```{note}
**Availability.** `add_database()` and `add_databases()` need
`octopize-avatar-yaml >= 0.1.44`, which is where `Config.create_input_*` arrives.
An older one fails at the call with a message naming the version to install.
```

## The shape of both

Same three steps as any avatarization, with one addition:

```text
describe the source  →  review what the machine proposed  →  run  →  fetch
                        └── new, and mandatory
```

The review step is not advisory. A job carrying a machine's unconfirmed guess
about what a column holds **fails at start**, on purpose: an unread proposal
that silently pseudonymized a column is the failure this prevents.

---

## 1. One database

You want a working copy of a production database: same structure, same row
counts, same joins, no real people.

### Before you start

Two things the platform will not do for you, by design:

- **The target must already have the schema.** Nothing here invents DDL —
  getting types, constraints and indexes subtly wrong would produce a copy that
  looks complete and behaves differently. Create it first:
  `pg_dump --schema-only prod | psql test`.
- **The target must be empty.** A non-empty target is refused, never emptied:
  emptying it is the destructive act this tool does not perform.

And the target must be the **same engine** as the source. PostgreSQL into
PostgreSQL — nothing translates DDL between type systems.

### Describing the copy

Use a **named connection** your deployment holds, not a DSN. A resource
document is stored, exported and quoted back in errors, so a password pasted
into one is a password in a database row.

```python
runner = manager.create_runner("crm-test-estate")

runner.add_database(
    source={"backing": "sql", "location": "crm_prod"},   # a named connection
    target={"backing": "sql", "location": "crm_test"},
    tables=["customer", "order", "address"],             # omit for all of them
)
```

### The plan is proposed, not written

You do not hand-write this. **Schema discovery** reads the source and
**classification** proposes what each column holds — name rules first, then a
model for what no rule settles. A three-table CRM comes back with `email`,
`last_name` and `city` already proposed for pseudonymization, `order` and
`product` proposed for plain copy, and `ref_3` proposed for nothing because no
rule knows what it is.

Every suggestion arrives `reviewed: false`, and the run is refused until that
changes. Reviewing it is three kinds of edit: **confirm** what is right, **reject**
what is wrong (set the column's action to `keep` — a decision, recorded as one),
and **decide what a classifier cannot** — no amount of column-name reading tells
anyone that `audit_log` should be emptied.

```{warning}
The propose step has no job yet. Discovery and classification exist in the engine
and nothing runs them as a step you can call, so for now the plan is written by
hand. When `SCHEMA_DISCOVERY` lands you will fetch the proposal, edit it and send
it back.
```

Five treatments, of which the last two are always yours:

| Treatment      | The table in the copy                   |
| -------------- | --------------------------------------- |
| `copy`         | as-is. Right for a lookup table         |
| `pseudonymize` | same rows, personal values replaced     |
| `anonymize`    | avatarized                              |
| `purge`        | present, **no rows** — for an audit log |
| `ignore`       | **not there at all**                    |

`purge` and `ignore` are different on purpose: one leaves a table an application
can query and find empty, the other leaves nothing.

```python
plan = {
    "tables": [
        {
            "name": "customer",
            "treatment": "pseudonymize",
            "columns": [
                {
                    "name": "email",
                    "action": "pseudonymize",
                    "strategy": {"kind": "FAKER", "pii_type": "EMAIL"},
                    "provenance": {"suggested_by": "model", "reviewed": True},
                },
                # A SERIAL column: continue the sequence past the copied rows,
                # or the application's first insert collides with an id you
                # just loaded.
                {"name": "customer_id", "sequence": "to_max"},
            ],
        },
        {"name": "order", "treatment": "copy"},
        {"name": "audit_log", "treatment": "purge"},
    ]
}

runner.add_database(
    source={"backing": "sql", "location": "crm_prod"},
    target={"backing": "sql", "location": "crm_test"},
    plan=plan,
)
```

`reviewed: True` is the gate. Leave it `False` and the job is refused, naming
the columns to go and read.

```{warning}
Without a plan the run is a **plain copy** — every table, unchanged, nothing
anonymized. That is a legitimate thing to want and a surprise if you expected
otherwise, so the engine logs it loudly.
```

### One person, one identity

`customer.email` and `order.contact_email` are the same person. Pseudonymizing
them independently gives that person two identities and breaks every join a
test database exists to exercise. A **reference table** resolves the subject
once and applies the answer everywhere:

```python
runner.add_database(
    source={"backing": "sql", "location": "crm_prod"},
    target={"backing": "sql", "location": "crm_test"},
    plan=plan,
    reference_tables=[
        {
            "name": "rt_person",
            "join_key": "customer_id",
            "source_tables": ["customer", "order"],
            "columns": [
                {"name": "email", "strategy": {"kind": "FAKER", "pii_type": "EMAIL"}}
            ],
        }
    ],
)

runner.run()
```

### What you get

The target database itself — there is nothing to download. The job reports what
it wrote: tables, rows, any foreign key a purge left pointing at nothing, and
any table the plan never mentioned (those are copied as-is, which is right for a
lookup table and wrong for one nobody has looked at).

---

## 2. Several databases

The CRM and the billing system describe the **same people**. A test estate where
Alice is one fake person in one and a different one in the other is useless.

That is the only reason to use this. If your subjects live inside a single
database, use [section 1](#1-one-database) — it is simpler and does the same
thing.

### Why it must be one job

Not two runs sharing a seed. A pseudonym follows the order subjects are
**collected**, not the value they replace — so two separate jobs disagree about
every person, however carefully configured. Subjects have to be gathered across
every source and resolved **once**, which is what this does.

### Describing it

```python
runner = manager.create_runner("full-test-estate")

runner.add_databases(
    copies=[
        {
            "name": "crm",
            "source": {"backing": "sql", "location": "crm_prod"},
            "target": {"backing": "sql", "location": "crm_test"},
            "plan": crm_plan,
        },
        {
            "name": "billing",
            "source": {"backing": "sql", "location": "billing_prod"},
            "target": {"backing": "sql", "location": "billing_test"},
            "plan": billing_plan,
        },
    ],
    reference_tables=[
        {
            "name": "rt_person",
            "join_key": "person_id",
            "source_tables": [
                {"source": "crm", "table": "customer", "key": "person_id"},
                {"source": "billing", "table": "invoice", "key": "customer_ref"},
            ],
            "columns": [
                {"name": "email", "strategy": {"kind": "FAKER", "pii_type": "EMAIL"}}
            ],
        }
    ],
)
```

Two things to notice.

**Each copy is named**, so a reference table can say which database a table is
in. Two databases may both hold a `customer` table.

**The key is per source table.** The person is `person_id` in the CRM and
`customer_ref` in billing. One shared column name would make most real pairs
impossible to express — so each entry names its own.

### Checking the join before you commit

The single most useful thing to do first: look at the key values side by side.

```python
# person_id: 1001, 1002, 1003   ← the CRM
# customer_ref: 1001, 1002, 1003 ← billing, and they line up
```

If one side is a UUID and the other an integer, you have the wrong column, and
you want to know that now rather than after a forty-table copy.

### Running it

```python
runner.run()
```

Every plan is checked, every subject resolved, and **every target validated
before a single row is written**. A half-written estate cannot be repaired by
retrying the missing half: the identities of the half that landed followed the
order subjects were collected, and that order cannot be reproduced from a
subset. So it is all of them or none.

### The consequence nobody expects

Because identities come from collecting every subject and resolving once, **the
set of databases is part of the identity function**. Add a third database next
quarter and every fake name changes — in _all_ the copies.

That is correct: the alternative is inconsistency. But it means an estate
refreshed with an extra source shares no people with the previous one. Worth
knowing before you promise someone a stable test estate.

---

## What is refused, and why

Everything below fails at configuration time rather than part-way through a
copy. The messages are safe to read out — they name tables and columns, never
values.

| You did this                                              | You get                                                    |
| --------------------------------------------------------- | ---------------------------------------------------------- |
| left a proposal unreviewed                                | the columns to go and read                                 |
| pointed the target at the source                          | "a working copy must be written somewhere else"            |
| targeted a different engine                               | "written into the same kind of thing it was read from"     |
| targeted a database holding rows                          | "this copy only ever appends, so the target must be empty" |
| targeted a database with no schema                        | "create the schema there first"                            |
| gave two copies the same target                           | "each database needs its own destination"                  |
| named a database in a reference table but not in `copies` | the copies that do exist                                   |