# database repo: key, S3 config, Liquibase project, PR

Repo: `theorchard/database` (usually the repo the user already has open).

## 1. Generate a key pair, once per environment

```
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```

Generate into a scratch location outside the repo working tree (e.g. a temp
dir), one pair per environment — **do not reuse a key pair across
environments.** `rsa_key.p8` is the private key; treat it as a secret for the
rest of this process:

- Never display, print, or echo its contents.
- Never `git add` it (it does briefly live inside the repo's `build/` folder
  before upload — see below — so add files individually by name, never
  `git add build/` or `git add .`, and double check `git status`/`git diff`
  before any commit in this repo to make sure it isn't staged).
- Delete the local copy once it's uploaded to S3 and no longer needed on disk,
  unless the user wants to keep it for their own records somewhere secure.

## 2. Create `db.properties` and upload to S3, once per environment

Fill in `assets/db.properties.template` for the environment
(`username`/`role` = the environment's service user name, `db` = that
environment's real Snowflake database name, `schema` = `PUBLIC` for paradigm B
or the environment name for paradigm A, `private_key_file` path uses the
env-agnostic `{{DB_FOLDER}}`).

Upload both the filled-in `db.properties` and that environment's `rsa_key.p8`:

```
aws s3 cp db.properties s3://nickelback/db-deploy/<environment>/snowflake/<DB_FOLDER>/db.properties
aws s3 cp rsa_key.p8    s3://nickelback/db-deploy/<environment>/snowflake/<DB_FOLDER>/rsa_key.p8
```

`<environment>` is lowercase (`qa`, `prod`, ...). `<DB_FOLDER>` is the
environment-agnostic folder name (see `references/paradigms.md`) — **not**
whichever real per-environment database name appears inside `db.properties`.
Get this mixed up and the deploy job will pull the wrong file for the wrong
folder.

Neither `db.properties` (the real one, with real role/db values) nor
`rsa_key.p8` are ever committed to git — they only exist locally, transiently,
and in S3.

## 3. Add the Liquibase project folder

Under `snowflake/<DB_FOLDER>/build/`, using the paradigm-appropriate template
from `assets/`:

- `pom.xml` — copy `assets/pom.xml.paradigm-a.template` or
  `assets/pom.xml.paradigm-b.template` (per `references/paradigms.md`),
  substituting `{{DB_FOLDER_LOWER}}` for the `artifactId`
  (`<db_folder_lowercase>.build`).
- `db.properties.shadow` — copy `assets/db.properties.shadow.template`,
  substituting `{{DB_FOLDER}}` for the two `{{DB_FOLDER}}` placeholders. This
  file is a checked-in template with literal placeholder values
  (`username`/`password`/`ROLE`/`SCHEMA`/`WAREHOUSE`) — it is not real
  configuration and is safe to commit as-is.

Sanity-check against a sibling folder of the same paradigm (e.g.
`snowflake/CONFLICT_REPORTING/build/` for paradigm B,
`snowflake/PERMISSIONS_PLATFORM/build/` for paradigm A) if anything about the
target repo's conventions looks like it's drifted since these templates were
written.

## 4. Add the connection-check changeset

`snowflake/<DB_FOLDER>/build/changelog/dml/<JIRA-NUMBER>-check-connection.sql`,
from `assets/check-connection.sql.template`, substituting `{{AUTHOR}}` with
whoever is running the skill (e.g. their git user name), not a hardcoded name.

Create `snowflake/<DB_FOLDER>/build/changelog/ddl/` too (empty, with a
`.placeholder` file) to match the sibling-folder convention, even though
nothing needs to go in it yet.

## 5. Open the PR

Bundles the `build/` folder contents (`pom.xml`, `db.properties.shadow`,
`changelog/dml/<...>-check-connection.sql`, `changelog/ddl/.placeholder`).
Get explicit user confirmation before pushing the branch or opening the PR.

Open this PR **in parallel with** the terraform-infra PR
(`references/terraform-infra.md` step 3) — there's no ordering dependency
between them, so don't wait on one to finish before starting the other.

`execute_db_deploy_jenkinsfile`'s first stage will refuse to run against this
PR until it's approved (see `references/jenkins-deploy.md`) — mention this to
the user so they know to get a review before trying to deploy.