---
name: onboard-snowflake-database
description: >
  Sets up the end-to-end deployment process for a new Snowflake database at
  The Orchard: a service role + service user in terraform-infra, a Liquibase
  project + connection-test changeset in theorchard/database, PRs in both
  repos, and running/verifying the db-deploy-pipeline Jenkins job per
  environment. Use this whenever the user wants to onboard a new Snowflake
  database for deployment, set up db-deploy (or "the deployment process") for
  a new database, add a database to the db-deploy pipeline, or is working a
  Jira ticket about standing up deployment for a new Snowflake database (e.g.
  a "SYS-"-style ticket) — even if they only describe one piece of it, like
  "I need a db-deploy service user for a new database" or "how do I add a
  Liquibase project for a new Snowflake db", since that's a step inside this
  larger workflow and the whole thing is easy to get partially wrong if done
  piecemeal. Always gathers the required details (Jira ticket, database name,
  environments) up front, and always gets explicit confirmation before
  opening a PR or triggering a Jenkins deploy — this touches production
  Snowflake IAM grants and real CI/CD pipelines, not just local files.
---

# Onboard a new Snowflake database for deployment

This sets up everything needed for a new Snowflake database to be deployable
via the standard process: a dedicated service role + service user per
environment (`terraform-infra`), a Liquibase project in `theorchard/database`
that uses them, and a first deploy run that proves the connection works.

**The hard rule underlying every step here: gather info and plan first, then
do local/reversible work, then get explicit confirmation before anything
that leaves your machine (opening a PR, running a Jenkins job).** This touches
real Snowflake access grants and two repos other people work in — treat PRs
and deploy runs as things the user signs off on, not things you do
autonomously just because the local prep looks right.

**Private key handling, non-negotiable throughout:** never print, log, or
otherwise display the contents of an `rsa_key.p8` private key, and never
`git add` one — only the corresponding public key and only the `.shadow`
placeholder file are meant to reach git. See `references/database-repo.md`
step 1 for the specifics.

## Prerequisites

- Local checkouts of both `theorchard/terraform-infra` and
  `theorchard/database`. Ask the user for paths if you don't already know
  them — don't guess or clone without asking.
- This skill assumes the target Snowflake database (and, for paradigm A, its
  schema) already exists. It sets up the *deployment process* for it, not the
  database itself. See the "Scope boundary" note in
  `references/paradigms.md` if that assumption doesn't hold.

## Workflow

### 0. Gather required information up front

Before touching anything, ask the user (via `AskUserQuestion` where the
answers are genuinely multiple-choice, plain questions otherwise — don't
make them guess what you need):

- Jira ticket number (used in the connection-check changeset filename and
  commit/PR messages)
- The database's name(s): the environment-agnostic name if there's one
  obvious answer, or each environment's real Snowflake database name if not
- Which environments this database has (prod only? prod + qa? something
  else like stage/sme/uat?)
- Whether this is one database with a schema per environment, or a separate
  database per environment (phrase this in plain terms, not "paradigm A/B" —
  see `references/paradigms.md` for the two shapes and how to explain them
  concisely; if the user isn't sure, look for how a similar existing sibling
  database under `snowflake/` is set up rather than pushing the decision back
  on them unanswered)
- Local paths to their `terraform-infra` and `theorchard/database` checkouts

### 1. Derive names and confirm the paradigm

Read `references/paradigms.md` and work out, from the answers above: the
database repo folder name, each environment's service user/role name, each
environment's real database name and schema, and whether the pom.xml needs
the `<systemProperties>` block. If anything is ambiguous, check a comparable
sibling folder under `snowflake/` rather than guessing — this repo has many
examples of both shapes already.

### 2. Present the full plan, then confirm before starting

Summarize concretely, before creating anything: every file to be created or
edited in each repo, the S3 upload paths, both PRs, and the Jenkins run(s) —
one per environment. Get the user's go-ahead on this plan before starting
step 3. This is the point to catch a wrong paradigm guess or a naming typo
cheaply, before it's spread across two repos and a Terraform state.

### 3. Do the local + S3 work

Once the plan is confirmed:

1. Generate the key pair(s) and set up `db.properties` + S3 upload — follow
   `references/database-repo.md` steps 1–2. This repeats once per
   environment; each environment gets its own key pair, never a shared one.
2. Add the Terraform role + service user block(s) — follow
   `references/terraform-infra.md` steps 1–2. Also once per environment.
3. Add the Liquibase project folder and connection-check changeset in
   `theorchard/database` — follow `references/database-repo.md` steps 3–4,
   using the templates in `assets/`.

None of this leaves your machine except the S3 upload, which just puts config
where the deploy job already expects to find it — no confirmation gate needed
per individual file, but stop and flag anything that doesn't match the plan
from step 2 rather than silently improvising.

### 4. Open both PRs — confirm first, and do them in parallel

Follow `references/terraform-infra.md` step 3 for the terraform-infra PR and
`references/database-repo.md` step 5 for the database-repo PR. Get explicit
confirmation before pushing either branch or opening either PR — these are
real PRs against production Terraform and a shared repo that other people
will see, and the terraform one, once applied, grants real Snowflake access.

There's no ordering dependency between them — independent files in
independent repos — so open both in the same pass rather than waiting for
one to merge before starting the other.

### 5. Run the deploy — confirm first, and check the ordering gate

Follow `references/jenkins-deploy.md`. Before triggering anything, verify
(by asking the user, don't assume) that the terraform-infra PR from step 4 is
not just merged but **applied** — the service user has to actually exist in
Snowflake before a deploy run against it can succeed. Then get explicit
confirmation covering all environments together, and trigger
`db-deploy-pipeline` (`jobFullName: "db-deploy-pipeline"`) for every
environment **in parallel** — the job name and parameters are already known,
so there's no need to look them up first, and each environment's run is
independent of the others.

### 6. Verify and report

Use the Jenkins MCP tools to check both the outer pipeline build and the
downstream `db-deploy` build it triggers, for every environment — in
parallel, same as triggering them — per `references/jenkins-deploy.md`'s
verification section. Report pass/fail per environment with enough detail
(from the build log, on failure) that the user knows what to fix, rather than
a bare "it failed."