# Bilko Stage Environment — Cloud SQL & IAM (Phase 1)

## Summary

MC #10177 Phase 1 (FlowForge, 2026-04-29): `bilko-staging-db` Cloud SQL instance brought under Flyway management. Pre-existing instance (2026-04-15, Prisma-managed). V1+V2+V4+V5 baselined, V3 actually executed. IAM SA created. Phase 2 (Cloud Run) pending.

## Instance Details

<table id="bkmrk-fieldvalueinstance-n"><tr><th>Field</th><th>Value</th></tr><tr><td>Instance name</td><td>`bilko-staging-db`</td></tr><tr><td>Connection name</td><td>`tribal-sign-487920-k0:europe-north1:bilko-staging-db`</td></tr><tr><td>IP</td><td>35.228.33.112</td></tr><tr><td>Tier</td><td>db-g1-small</td></tr><tr><td>Version</td><td>POSTGRES\_16</td></tr><tr><td>State</td><td>RUNNABLE (pre-existing since 2026-04-15; reused)</td></tr><tr><td>Database</td><td>`bilko`</td></tr><tr><td>App user</td><td>`bilko`</td></tr><tr><td>Migration admin</td><td>`migration_admin`</td></tr><tr><td>Secret</td><td>`bilko-staging-db-password` (Secret Manager, 2026-04-15)</td></tr><tr><td>IAM SA</td><td>`bilko-api-stage-sa@tribal-sign-487920-k0.iam.gserviceaccount.com`</td></tr><tr><td>IAM SA roles</td><td>roles/cloudsql.client + roles/secretmanager.secretAccessor</td></tr><tr><td>Total tables</td><td>24 (public schema)</td></tr></table>

## Flyway State (2026-04-29)

<table id="bkmrk-versionscriptstatusv"><tr><th>Version</th><th>Script</th><th>Status</th></tr><tr><td>V1</td><td>V1\_\_initial\_schema.sql</td><td>Baselined (DDL existed via Prisma)</td></tr><tr><td>V2</td><td>V2\_\_add\_missing\_prisma\_columns.sql</td><td>Baselined (DDL existed via Prisma)</td></tr><tr><td>V3</td><td>V3\_\_add\_jmbg\_oib\_encryption.sql</td><td>**EXECUTED LIVE** — jmbg/jmbg\_hash/oib/oib\_hash + 2 indexes added to contacts (ADR-014)</td></tr><tr><td>V4</td><td>V4\_\_add\_supplementary\_tables.sql</td><td>Baselined (DDL existed via Prisma)</td></tr><tr><td>V5</td><td>V5\_\_add\_logo\_url\_to\_organizations.sql</td><td>Baselined (DDL existed via Prisma)</td></tr></table>

## Open Risks

- **V3 prod gap:** Prisma migrations never included V3. Production DB may be missing jmbg/oib columns on contacts. Audit required before Kotlin cutover (separate MC pending).
- **Prod topology unknown:** bilko-staging-db is the only documented Cloud SQL instance. Whether a separate prod instance exists is unconfirmed. Audit required before Phase 2 prod deploy.
- **MC #10187:** gradle flywayMigrate broken (Flyway plugin 10.22.0 + Gradle 9.3.1 incompatibility). Workaround: psql sequential apply.

## Phase Status

- Phase 1 (Cloud SQL + IAM + Flyway baseline): COMPLETE
- Phase 1.5 (Proveo validation): pending
- Phase 2 (Cloud Run bilko-api-stage + bilko-web-stage): Mehanik gate next

## References

- MC #10177 (parent), MC #10183 (Flyway verify), MC #10187 (gradle fix)
- ADR-014 (field encryption), ADR-021 (blueprint reorg)
- DEPLOY-MAP.md — Cloud SQL Instances section
- RUNBOOK.md — Section 7g
- Evidence: /tmp/bilko-stage-phase1-evidence.json (FlowForge)