cold_ahh_data

FreezerPro → BigQuery sync pipeline. A Python Cloud Function that pulls samples and freezers from the FreezerPro API on a schedule and appends them into BigQuery raw landing tables.

Architecture

Cloud Scheduler (hourly cron)
       │
       ▼
Cloud Function (gen2, Python 3.11)
       │
       ├─► FreezerPro API  ──► method=gen_token  ──► auth_token (10-min TTL)
       │                   ──► method=search_samples (paginated)
       │                   ──► method=freezers
       │
       └─► BigQuery
            ├── lab_freezerpro.samples_raw   (partitioned by ingestion_date)
            ├── lab_freezerpro.freezers_raw  (partitioned by ingestion_date)
            ├── lab_freezerpro.samples_current   (view: latest per sample_id)
            └── lab_freezerpro.freezers_current  (view: latest per freezer_id)

Auth flow

Uses the gen_token method to generate a short-lived auth_token (10-minute TTL, refreshed on each use) from username/password credentials stored in Secret Manager. All subsequent API calls use the token, which avoids "API Session Created/Removed" audit log entries.

Setup

1. Add secrets to Secret Manager

echo -n 'your_username' | gcloud secrets versions add freezerpro-username --data-file=- --project=$GCP_PROJECT_ID
echo -n 'your_password' | gcloud secrets versions add freezerpro-password --data-file=- --project=$GCP_PROJECT_ID

2. Create BigQuery tables

bq query --use_legacy_sql=false --project_id=$GCP_PROJECT_ID < bigquery_ddl.sql

3. Deploy

export GCP_PROJECT_ID="your-project"
export FREEZERPRO_BASE_URL="https://yourdomain.freezerpro.com"
./deploy.sh

4. Test

gcloud functions call freezerpro-sync --gen2 --region=us-central1 --project=$GCP_PROJECT_ID

Environment variables

Variable	Description	Source
FREEZERPRO_BASE_URL	FreezerPro instance URL (e.g. https://lab.example.com)	Deploy env var
FREEZERPRO_USERNAME	FreezerPro API username	Secret Manager
FREEZERPRO_PASSWORD	FreezerPro API password	Secret Manager
BQ_PROJECT_ID	GCP project ID	Deploy env var
BQ_DATASET	BigQuery dataset (default: lab_freezerpro)	Deploy env var
BQ_TABLE_SAMPLES	Samples table name (default: samples_raw)	Deploy env var
BQ_TABLE_FREEZERS	Freezers table name (default: freezers_raw)	Deploy env var
PAGE_SIZE	Pagination page size (default: 500)	Deploy env var

Extending

To add more entity types (boxes, vials, sensors), follow the same pattern:

1. Add a fetch_* function using the relevant FreezerPro API method
2. Add a normalize_* function mapping to your BQ schema
3. Add a CREATE TABLE to bigquery_ddl.sql
4. Wire it into the entrypoint

License

MIT