Tutorial: databases

Two walkthroughs for a source that is not a CSV:

1. One database — a working copy of a production database

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:

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.

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.

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:

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 — 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

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.

# 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

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