# Helix warehouse export

## Contract

- Canonical tables: `gcp-digital-bi.helix.cloud_accounts` and `gcp-digital-bi.helix.pingcap_users`
- Source of truth: Helix `cmdb_cloud_scopes` for accounts and `identity_persons` for users, joined only to non-secret integration and directory metadata
- Stable primary keys: `scope_id` and `user_id`
- Snapshot behavior: full replacement semantics through a primary-key merge plus deletion of rows absent from the latest successful snapshot
- Source soft deletion: deleted cloud scopes and persons remain in their snapshots and are marked by `is_deleted` and `deleted_at`
- Account and billing-directory boundary: `aws_account`, `gcp_project`, `gcp_billing_account`, `azure_subscription`, `aliyun_account`, `standalone_iam`, and `legacy_billing_unit`; child resources such as `azure_resource_group` are excluded. `legacy_billing_unit` rows are exported for billing attribution only and remain outside Helix access-control account pickers.
- User boundary: every `identity_persons` row is included, including `active`, `leaver_pending`, `left`, `terminated`, and HRIS-purged/deleted records
- Privacy boundary: work-directory identity, organization, manager, location, employment status, and lifecycle timestamps are included; password hashes, personal email, mobile phone, birthday, gender, email aliases, leave balances, and other private HR fields are excluded
- Naming boundary: the PingCAP employee table uses the `pingcap_` prefix so it is unambiguous beside the separate Pingkai Feishu snapshot table `gcp-digital-bi.helix.pingkai_users`

The first 13 columns retain the names used by `gcp-digital-bi.dp.full-projects`.
Helix does not own an authoritative `management_type`, so that field is nullable.
Helix also does not own an account-level authentication classification, so
`auth_type` is explicitly `unknown` rather than inferred.

## Runtime

`pingcap-helix-cloud-accounts` is a combined Cloud Run Job in `us-west2`. It runs
daily at `23:15 UTC`, after the Helix cloud inventory sync window. One invocation
queries both managed projections through one database connection, writes one
Parquet object per table and one immutable multi-table manifest to the managed
GCS bucket, loads raw history, merges both final tables, and records terminal
status in `helix_control`.

The job reads a single Secret Manager secret,
`pingcap-helix-cloud-accounts-tidb-source-dsn`, as `TIDB_SOURCE_DSN`. The value is
not stored in source control, Terraform state, images, manifests, or logs.
The credential is a dedicated TiDB user with `SELECT` only on the six source
tables named by the queries; `identity_persons` was already included in that
allowlist. It has no database-wide or write privileges.

## Acceptance checks

For every deployment or query change, compare both projections on the source
and target sides:

1. total rows and distinct primary keys for each table;
2. cloud accounts grouped by `provider`, `scope_type`, and `is_deleted`, including exact `legacy_billing_unit` source-to-target parity;
3. users grouped by `employment_status`, `hris_record_state`, `is_active`, and `is_deleted`;
4. zero sensitive excluded columns in the final user schema;
5. latest terminal run and per-table load state in `helix_control`.

A Cloud Run execution in a terminal success state is necessary but not
sufficient; the BigQuery row-count and deletion-count readbacks are required.