# Deployment paradigms

Every Snowflake database in `theorchard/database`'s `snowflake/` folder follows
one of three shapes. Get this right first — it determines the db/role/service-user
names you derive, the schema in `db.properties`, and whether the pom.xml needs a
`<systemProperties>` block. When in doubt, look at a sibling folder under
`snowflake/` for a database with a similar shape rather than guessing (e.g.
`CONFLICT_REPORTING` is paradigm B, `PERMISSIONS_PLATFORM` is paradigm A).

## Paradigm A — one database, one schema per environment

The physical Snowflake database name is constant across environments; each
environment is a schema within it.

Example: `PERMISSIONS_PLATFORM` database, with `PROD` and `QA` schemas.

- Dedicated db-deploy service user **per schema**, each scoped to its own schema
  (not the whole database):
  - `<DB>_DB_<ENV>_SCHEMA_DB_DEPLOY`, e.g. `PERMISSIONS_PLATFORM_DB_PROD_SCHEMA_DB_DEPLOY`
  - `<DB>_DB_<ENV>_SCHEMA_DB_DEPLOY`, e.g. `PERMISSIONS_PLATFORM_DB_QA_SCHEMA_DB_DEPLOY`
- `db.properties` `schema=` is the environment name (`PROD` / `QA`), `db=` is
  always the same physical database name regardless of environment.
- pom.xml does **not** need a `<systemProperties><db>...</db></systemProperties>`
  block — the database name never varies, so there's nothing environment-specific
  to inject into the changelogs via a system property. (Verified: absent from
  `PERMISSIONS_PLATFORM/build/pom.xml`.)

## Paradigm B — separate database per environment

Each environment is a genuinely separate Snowflake database; there's no shared
physical database.

Example: `COMMON_REPORTING` (prod) / `QA_COMMON_REPORTING` (qa).

- Dedicated db-deploy service user **per environment**, each with full access to
  its own database:
  - `<ENV_PREFIX><DB>_DB_DEPLOY`, e.g. `QA_COMMON_REPORTING_DB_DEPLOY` for qa,
    `COMMON_REPORTING_DB_DEPLOY` for prod. Follow whatever the naming already is
    for the specific pair of databases — usually the qa database is prefixed
    with `QA_` and the prod one has no prefix.
- `db.properties` `db=` is the actual per-environment database name
  (`QA_COMMON_REPORTING`, `COMMON_REPORTING`, ...); `schema=` is typically
  `PUBLIC` unless told otherwise.
- pom.xml **does** need the `<systemProperties><db>${db}</db></systemProperties>`
  block inside the `liquibase-maven-plugin` configuration, so the changelogs can
  reference which physical database they're running against via the `db` system
  property. (Verified: present in `CONFLICT_REPORTING/build/pom.xml`.)

## Single-environment databases

Some databases only ever have a prod environment (e.g. `GRPS_APP_REPORTING_NOCONF`).
Treat this as paradigm B with exactly one environment — do steps 1–4 of the main
workflow once, for prod only, and there is nothing to compare/differ so the
`<systemProperties>` question is moot (only one database ever, no cross-env
variance) — check a comparable single-env sibling if one exists, otherwise
default to including it only if the folder name and the `db=` value actually
differ (they usually don't for single-env databases).

## Naming derivation — shared rules

Whichever paradigm applies, the **database repo folder** under `snowflake/`
(`snowflake/<DB_FOLDER>/`) is always the environment-agnostic name — e.g.
`COMMON_REPORTING`, never `QA_COMMON_REPORTING` — because one Liquibase project
serves all environments; it's the `db.properties` pulled from S3 at deploy time
that points it at the right physical database/schema. This applies even under
paradigm B, where the folder name doesn't literally match either environment's
real database name one-for-one (it matches the prod one, conventionally).

The pom.xml `artifactId` should be `<db_folder_lowercased>.build`, e.g.
`common_reporting.build` for folder `COMMON_REPORTING` — don't leave it as
whatever a copied template's artifactId happened to be.

## Scope boundary: this skill assumes the Snowflake database already exists

This skill sets up the *deployment process* (service user, role, Liquibase
project, connection test) for a Snowflake database that already exists, or
whose creation is being handled separately. It does not create the database
object itself. Some databases (e.g. `PERMISSIONS_PLATFORM`) were provisioned via
a separate `terraform-snowflake` self-service module rather than by hand — if
the user's ticket is actually asking to create a brand-new database from
scratch (not just onboard it to the deploy process), flag that this is a
different, earlier step and point them at that module / their team's Snowflake
provisioning process before continuing with this skill.