GCP Deployment

Audience: Deployment Administrator Related: Application Updates · Reverse Proxy And Tls · Background Jobs And Schedules · Sqlite And Postgresql · Deployment

The supported GCP path uses a Compute Engine application host, Cloud SQL PostgreSQL, Cloud SQL Auth Proxy on localhost, Gunicorn, Nginx, HTTPS certificates, systemd-managed services, and /etc/cron.d/blackcap for managed schedules. Runtime data defaults to /var/lib/blackcap, while the application checkout normally lives at /opt/Blackcap and the virtual environment at /opt/blackcap_env.

tools/deploy_gcp.sh installs or repairs the base packages, Cloud SQL Auth Proxy, Playwright dependencies, Nginx, Certbot, the application service, and scheduled operations. It can configure private-IP Cloud SQL and DNS-01 certificate renewal.

Standard installation

cd /opt/Blackcap
sudo ./tools/deploy_gcp.sh

The script installs dependencies from requirements-cloud.txt, creates /opt/blackcap_env, installs Cloud SQL Auth Proxy, writes /etc/blackcap/blackcap.env, configures /var/lib/blackcap, installs Playwright Chromium, writes the GCP application unit, optionally configures Nginx and DNS-01 Certbot renewal, installs schedules, and starts the services.

Service checks

sudo systemctl status cloud-sql-proxy.service --no-pager
sudo systemctl status inky_admin.service --no-pager
sudo nginx -t
sudo certbot renew --dry-run
sudo cat /etc/cron.d/blackcap

Nginx and certificate renewal

Blackcap's direct GCP deployment uses this trust boundary:

Internet client → Nginx :443 → Gunicorn 127.0.0.1:8080 → Blackcap

Gunicorn remains bound to loopback. The GCP firewall exposes TCP 443 only; TCP 80 is not required by this deployment pattern.

Why DNS-01 is used

Let's Encrypt HTTP-01 validation requires inbound TCP 80. Blackcap's HTTPS-only GCP setup instead uses automated DNS-01 validation. Certbot proves control of the hostname by creating and removing an _acme-challenge TXT record through the DNS provider's API.

The focused setup supports three automation modes:

• godaddy: native Blackcap hooks that use GoDaddy's production DNS API. This is the default for the current Blackcap deployment.
• google: Google Cloud DNS through Certbot's dns-google plugin.
• hooks: provider-specific executable authentication and cleanup hooks for other DNS providers.

Interactive manual DNS entry is not considered auto-renewal and is not used by this setup.

Configure an existing GCP instance

From the current checkout:

cd /opt/Blackcap
sudo ./tools/configure_gcp_frontend.sh

The script:

• installs Nginx and Certbot when missing;
• installs python3-certbot-dns-google when Google Cloud DNS mode is selected;
• installs root-owned GoDaddy DNS credentials and Certbot hook wrappers when GoDaddy mode is selected;
• obtains or converts the hostname certificate to unattended DNS-01 renewal;
• writes an Nginx site that listens on TCP 443 only;
• enables the package-provided certbot.timer, or installs a Blackcap fallback timer when the package has no timer;
• installs /etc/letsencrypt/renewal-hooks/deploy/blackcap-nginx-reload;
• writes trusted-local-proxy settings to /etc/blackcap/blackcap.env; and
• restarts Blackcap so host-level proxy settings take effect.

GCP firewall requirements

Allow inbound TCP:

• 443 for Blackcap HTTPS traffic.

Do not expose:

• 80; DNS-01 validation does not use it.
• 8080; Gunicorn listens only on loopback.
• 5432; the Cloud SQL Auth Proxy listens only on loopback.

Certbot needs outbound HTTPS access to the ACME service, and the selected DNS automation method must be able to update the authoritative DNS zone.

GoDaddy DNS automation

Choose godaddy when the hostname's authoritative DNS records are managed in GoDaddy. Before running the setup, create a production GoDaddy API key and secret for the same GoDaddy account that controls the domain. GoDaddy currently limits production Management and DNS API access to eligible accounts with at least one domain or an active Discount Domain Club Domain Pro plan.

The setup prompts for:

GoDaddy-managed root domain
Root-only GoDaddy credentials file
GoDaddy production API key
GoDaddy production API secret
DNS propagation wait in seconds

For a hostname such as blackcap.microwavepie.com, the GoDaddy-managed root domain is normally:

microwavepie.com

Credentials are stored outside the repository and outside Blackcap's application environment:

/etc/blackcap/godaddy-dns.ini

The file is owned by root and mode 0600. The API secret is never written to /etc/blackcap/blackcap.env, command-line arguments, Nginx configuration, or the PostgreSQL database.

Blackcap installs these Certbot hook commands:

/usr/local/sbin/blackcap-godaddy-dns-auth
/usr/local/sbin/blackcap-godaddy-dns-cleanup

The hooks use GoDaddy's production Domains API to read the existing TXT records, add only the current Let's Encrypt value, and remove only that same value after validation. This preserves unrelated TXT values at the same _acme-challenge record name. The hook waits for the configured propagation period before Certbot asks Let's Encrypt to validate the record.

Verify API access without changing DNS:

sudo /usr/bin/python3 /opt/Blackcap/tools/godaddy_dns_hook.py verify \
  --credentials /etc/blackcap/godaddy-dns.ini

Inspect the saved Certbot renewal hooks:

sudo grep -E \
  '^[[:space:]]*(authenticator|manual_auth_hook|manual_cleanup_hook)[[:space:]]*=' \
  /etc/letsencrypt/renewal/<hostname>.conf

Expected GoDaddy renewal configuration:

authenticator = manual
manual_auth_hook = /usr/local/sbin/blackcap-godaddy-dns-auth
manual_cleanup_hook = /usr/local/sbin/blackcap-godaddy-dns-cleanup

A manual authenticator is unattended when both saved hook commands are present. A plain interactive manual certificate without hooks is not renewable automatically.

Google Cloud DNS permissions

For google mode, Certbot uses Application Default Credentials by default. On a Compute Engine VM, that normally means the VM's attached service account.

The service account must be allowed to discover the relevant managed zone and create/delete challenge TXT records. Prefer zone-scoped, least-privilege roles. The setup also accepts a service-account JSON file, but the VM service account is preferable because it avoids a long-lived key file on disk.

The script prompts for:

Google Cloud project containing the public DNS zone
Google DNS service-account JSON path; leave blank to use the VM service account
DNS propagation wait in seconds

If the public zone is in another project, enter that DNS project rather than the Compute Engine project.

Non-Google DNS providers

Choose hooks mode and provide:

Executable DNS-01 authentication hook
Executable DNS-01 cleanup hook

The authentication hook must create the requested TXT record and wait until it is resolvable before returning successfully. The cleanup hook must remove the record. Certbot stores these hook paths in the renewal configuration, allowing the scheduled renewal command to run unattended.

Protect API tokens used by hook scripts. Keep credential files root-owned and mode 0600.

Client IP trust model

The Nginx site deliberately overwrites incoming forwarding headers:

proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Forwarded-Port $server_port;
proxy_set_header Forwarded "";

Using $remote_addr instead of $proxy_add_x_forwarded_for prevents an Internet client from injecting a fake first address into the forwarded chain. Blackcap accepts those headers only when the immediate socket peer is in:

BLACKCAP_TRUSTED_PROXY_HEADERS_ENABLED=1
BLACKCAP_TRUSTED_PROXY_CIDRS=127.0.0.1/32, ::1/128
BLACKCAP_ENFORCE_HTTPS_EXTERNAL=1
BLACKCAP_TRUST_EXTERNAL_BASE_URL_HTTPS=1

These environment settings are applied after DB-backed configuration because the proxy topology belongs to the host, not to the shared PostgreSQL database.

Renewal behavior

The Debian/Ubuntu Certbot package normally provides certbot.timer. Blackcap enables it when present. Successful renewals execute:

/usr/sbin/nginx -t
/bin/systemctl reload nginx

The reload happens only after Nginx configuration validation succeeds.

Check the schedule and certificate:

sudo systemctl list-timers --all | grep -E 'certbot|blackcap-certbot'
sudo certbot certificates
sudo certbot renew --dry-run

Inspect the renewal authenticator:

sudo grep -E '^[[:space:]]*(authenticator|manual_auth_hook|manual_cleanup_hook)[[:space:]]*=' \
  /etc/letsencrypt/renewal/<hostname>.conf

For GoDaddy, the authenticator is manual with both Blackcap GoDaddy hook paths saved. For Google Cloud DNS, the authenticator should be:

authenticator = dns-google

For custom hooks, it should be manual and include both saved hook paths. A plain interactive manual certificate without hooks is not unattended.

Verify HTTPS-only exposure

sudo nginx -t
sudo systemctl status nginx --no-pager
sudo ss -ltnp | egrep ':80|:443|:8080'

Expected:

• Nginx listens on 0.0.0.0:443 and/or [::]:443.
• Nothing listens publicly on port 80.
• Gunicorn listens on 127.0.0.1:8080.

The GCP firewall should also contain no inbound HTTP rule for this VM.

Validate the real client address

After an external browser request, use Blackcap's access activity or GeoIP diagnostics. The request should show the public client IP rather than 127.0.0.1.

You can also inspect the Nginx access log:

sudo tail -n 50 /var/log/nginx/access.log

Blackcap's security and access services use the validated client address for activity records, GeoIP lookup, rate-limit keys, and local-versus-external request decisions.

Troubleshooting DNS-01

If issuance or the renewal dry run fails:

1. Confirm the hostname's public DNS zone is managed by the provider selected in the script.

2. For GoDaddy, verify /etc/blackcap/godaddy-dns.ini is root-owned, mode 0600, contains a production API key, and names the correct root domain. Run the GoDaddy hook's verify command shown above.

3. For Google Cloud DNS, confirm the VM service account can list the managed zone and create/delete record changes.

4. Confirm the Certbot plugin is installed when using Google Cloud DNS:

   sudo certbot plugins | grep -E 'dns-google|dns_google'
   

5. Confirm the public authoritative nameservers can resolve TXT records under _acme-challenge.<hostname>.

6. Increase BLACKCAP_CERTBOT_DNS_PROPAGATION_SECONDS when the DNS provider publishes changes slowly.

7. Inspect Certbot logs:

   sudo journalctl -u certbot.service -n 100 --no-pager
   sudo tail -n 100 /var/log/letsencrypt/letsencrypt.log
   

8. Validate Nginx separately:

   sudo nginx -t
   sudo systemctl status nginx --no-pager
   

Real client IP inside Flask and Gunicorn logs

For the direct GCP topology, Nginx is the only proxy and Gunicorn remains bound to 127.0.0.1:8080. The deployment environment enables trusted proxy handling only for loopback CIDRs. Blackcap applies Werkzeug ProxyFix for exactly one proxy hop so Flask's standard request.remote_addr, scheme, host, and port use the values Nginx overwrites.

Gunicorn's default %(h)s access-log token intentionally reports its socket peer, which is local Nginx. The repository gunicorn.conf.py switches the GCP access log to the Nginx-provided X-Forwarded-For value when the loopback-only trusted proxy environment is enabled. No systemd ExecStart change is required; Gunicorn loads gunicorn.conf.py from /opt/Blackcap because that is the service working directory.

After updating these files, restart Blackcap:

sudo systemctl restart inky_admin.service

New access lines should begin with the client's public address rather than 127.0.0.1. Existing Access Activity rows are not rewritten.

PostgreSQL deployment behavior

Blackcap supports two first-class database deployments:

• SQLite for Raspberry Pi and other local appliance-style installations.
• PostgreSQL for GCP deployments, normally through Cloud SQL and the Cloud SQL Auth Proxy.

PostgreSQL support is additive. SQLite remains fully supported and keeps SQLite-specific connection, maintenance, WAL, and local-file behavior rather than inheriting unnecessary cloud-database logic.

Current status

The GCP/PostgreSQL deployment is supported for normal Blackcap operation and regression testing. The application uses a centralized database service/adapter layer so routes and feature services do not need scattered backend conditionals.

Implemented behavior includes:

• Startup backend selection through [database].backend and host environment settings.
• Split BLACKCAP_POSTGRES_* connection variables or a single PostgreSQL URL/DSN.
• Cloud SQL Auth Proxy connectivity, including private-IP mode.
• PostgreSQL connection pooling for GCP.
• SQLite/PostgreSQL placeholder, row, transaction, and metadata adapters.
• Current-schema PostgreSQL bootstrap for an empty database.
• Cross-backend Python migrations for changes after PostgreSQL support was introduced.
• Provider-aware Database Admin health, maintenance, resource browsing, SQL Console behavior, and schema export.
• Live PostgreSQL type guidance for integer flags, native booleans, timestamps, JSON, joins, and UNION ALL compatibility.
• PostgreSQL-compatible regression execution alongside the SQLite regression environment.

Backend selection

SQLite remains the default when no PostgreSQL configuration is present:

[database]
backend = sqlite
database_path = data/blackcap.db
sqlite_wal_mode = true
enable_fts5 = true

PostgreSQL uses:

[database]
backend = postgres
postgres_url_env = BLACKCAP_DATABASE_URL

For systemd and cloud deployments, split environment variables are preferred because passwords do not need URL encoding:

BLACKCAP_DATABASE_BACKEND=postgres
BLACKCAP_POSTGRES_HOST=127.0.0.1
BLACKCAP_POSTGRES_PORT=5432
BLACKCAP_POSTGRES_DB=blackcap
BLACKCAP_POSTGRES_USER=blackcap
BLACKCAP_POSTGRES_PASSWORD=...
BLACKCAP_POSTGRES_SSLMODE=disable

A single URL is also supported:

BLACKCAP_DATABASE_URL=postgresql://blackcap:password@127.0.0.1:5432/blackcap?sslmode=disable

When split variables and a URL are both present, the split variables take precedence. Keep credentials in /etc/blackcap/blackcap.env or another protected service environment file rather than in the repository or INI file.

Backend selection is a startup-level host decision. It is intentionally not controlled by DB-backed settings because the application must know how to connect before it can read those settings.

GCP / Cloud SQL architecture

The normal GCP path is:

Blackcap / Gunicorn
    -> PostgreSQL connection pool
    -> Cloud SQL Auth Proxy on 127.0.0.1
    -> Cloud SQL PostgreSQL

When Cloud SQL is private-IP-only, the proxy service must include --private-ip, and the VM must be attached to a network that can reach the Cloud SQL private address.

Use requirements-cloud.txt on GCP so Raspberry Pi GPIO/e-ink packages are not installed:

cd /opt/Blackcap
/opt/blackcap_env/bin/python -m pip install -r requirements-cloud.txt

See raspberry-pi.md for the complete GCP setup and service layout.

Connection pooling

The GCP deployment normally enables the PostgreSQL pool:

BLACKCAP_POSTGRES_POOL_ENABLED=1
BLACKCAP_POSTGRES_POOL_MIN_SIZE=1
BLACKCAP_POSTGRES_POOL_MAX_SIZE=4
BLACKCAP_POSTGRES_POOL_TIMEOUT=10
BLACKCAP_POSTGRES_POOL_CLOSE_TIMEOUT=15

Pool sizing should stay conservative unless the Cloud SQL instance and application concurrency justify increasing it. Each Gunicorn worker is a separate process, so pool limits apply per worker rather than once for the entire VM.

SQLite does not use this PostgreSQL pool and keeps its short-lived local connection model.

Schema bootstrap and migrations

A fresh PostgreSQL database is initialized from the current PostgreSQL bootstrap rather than replaying every historical SQLite migration. Historical SQL migrations remain part of the SQLite upgrade path.

New cross-backend changes should use Python migrations under database/migrations:

199_example_change.py
200_next_change.py

Each migration exposes:

def apply(context) -> None:
    context.execute("...")

The migration context provides the active backend, connection, placeholder style, execution helpers, and app metadata helpers.

Migration rules:

• Migration version numbers must be unique.
• APP_DB_SCHEMA_VERSION must equal the highest migration version expected by the application.
• A normal application restart runs pending migrations.
• Do not edit an already-released migration to repair one backend; add a new migration.
• PostgreSQL startup must verify the current migration is recorded rather than trusting only a cached schema-version value.

Live PostgreSQL types

Do not assume a field uses PostgreSQL's most idiomatic native type merely because the backend is PostgreSQL. Some Blackcap columns intentionally preserve compatible physical types from the SQLite lineage.

Examples:

• An integer logical flag must use = 1 / = 0, not IS TRUE / IS FALSE.
• A native boolean can use TRUE, FALSE, IS TRUE, or IS FALSE.
• A text-backed timestamp may need NULLIF(BTRIM(column::text), '')::timestamptz.
• Text-backed JSON must be validated/cast before PostgreSQL JSON operators are used.
• UNION / UNION ALL branches must return compatible types in every output position.
• Do not cast matching join keys to text without a reason; unnecessary casts can prevent index use.

The SQL Console Export Schema · PostgreSQL output is generated from the live database and should be used as the source of truth when asking an LLM to write PostgreSQL queries.

Database Admin behavior

Database Admin identifies the active provider in its overview and SQL Console.

PostgreSQL Maintenance currently offers:

• Verify PostgreSQL Connection
• Analyze Database

PostgreSQL health can include database/schema identity, server version, database and table sizes, active connections, live/dead tuple estimates, analyze/vacuum timestamps, pool configuration, and schema version.

SQLite-only actions such as integrity checks, WAL checkpointing, and file vacuuming are not shown on PostgreSQL.

The SQL Console remains read-only and provider-aware. PostgreSQL queries must use PostgreSQL syntax, while SQLite-only PRAGMA statements are unavailable. Protected columns remain excluded from autocomplete and schema exports.

Search behavior

SQLite can use its local FTS5 search tables when enabled. PostgreSQL does not create SQLite virtual FTS tables. Feature services must use the centralized search/repository abstractions so each backend can use its supported implementation or fallback behavior.

Do not add direct FTS-table SQL to routes or general services.

Backup and recovery

Blackcap's application backup features and database-engine disaster recovery are separate concerns.

For SQLite:

• The local database file and Blackcap backup workflows can be used for appliance recovery.
• Restore testing should be performed periodically on a separate copy.

For PostgreSQL / Cloud SQL:

• Use Cloud SQL automated backups and point-in-time recovery for full database recovery.
• Test restoration into a separate Cloud SQL instance before relying on the recovery plan.
• Organization export/import features remain useful for scoped application data, but they are not a substitute for a full PostgreSQL backup.
• Do not treat a copied SQLite database file as a PostgreSQL backup format.

Database parity audit

The System page includes System Actions → Database Parity Audit. The audit runs as a monitored background job, so the page shows queued/running/completed/failed state, percentage progress, the current phase, duration, and the latest report.

The target database connection remains technically read-only:

• SQLite uses URI mode=ro plus PRAGMA query_only;
• PostgreSQL starts a READ ONLY transaction and rolls it back before returning the connection to the pool;
• the fresh SQLite reference is created in an isolated child process and temporary directory;
• initialization and repair are disabled during the audit.

Operational job status and the completed report are stored in background_jobs so monitoring survives page refreshes. That operational tracking is separate from the audited read-only connection.

The report can:

• identify the active database and schema;
• report the latest applied migration;
• compare the active schema with a freshly migrated SQLite reference;
• identify missing tables or columns required by runtime code;
• check required display catalog rows;
• check required grocery department and canonical ingredient mapping seeds;
• report customized or absent Noun Project rule sets as informational differences rather than parity failures;
• provide guidance and identify findings eligible for safe automatic repair.

Safe parity repair

When the report contains registered additive gaps, the System page offers Repair Safe Database Gaps as a separate confirmed background action. The repair action is intentionally narrower than a migration or generic schema synchronization. It may:

• add provider-aware columns from the explicit safe-repair registry;
• restore required display type/content mode catalog rows;
• restore required grocery department and canonical ingredient mapping seed rows.

It never drops or renames tables/columns, deletes rows, rebuilds tables, changes primary keys, or changes Noun Project rules. Findings outside the safe registry remain manual and include migration guidance.

The same diagnostic remains available from the command line:

cd /opt/Blackcap
sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py

Useful options include:

sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py --json
sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py --strict
sudo /opt/blackcap_env/bin/python tools/audit_postgres_parity.py --no-sqlite-reference

The command-line tool is diagnostic and read-only unless --initialize is explicitly supplied. Routine application startup already handles pending migrations, so that option should not be needed for ordinary deployments.

Operational verification

Useful GCP checks:

sudo systemctl status cloud-sql-proxy.service --no-pager
sudo systemctl status inky_admin.service --no-pager
sudo journalctl -u inky_admin.service -n 500 --no-pager | grep -Ei 'PostgreSQL|pool|slow|traceback|error'

Verify the proxy connection directly when needed:

PGPASSWORD="$BLACKCAP_POSTGRES_PASSWORD" psql \
  --host="${BLACKCAP_POSTGRES_HOST:-127.0.0.1}" \
  --port="${BLACKCAP_POSTGRES_PORT:-5432}" \
  --username="$BLACKCAP_POSTGRES_USER" \
  --dbname="$BLACKCAP_POSTGRES_DB" \
  --command='SELECT current_database(), current_schema(), current_user;'

Development rules

• Keep database access behind the centralized service/adapter/repository boundaries.
• Do not scatter backend checks through Flask routes.
• Do not use SELECT * in runtime application SQL; select only the required columns.
• Preserve SQLite-specific lightweight behavior instead of forcing every PostgreSQL compatibility step onto SQLite.
• Add provider-specific behavior at the provider/adapter boundary.
• Validate changes against both SQLite and PostgreSQL when they touch shared database code.