wehub-resource-sync
59 lines
2.2 KiB
Markdown
59 lines
2.2 KiB
Markdown
# ClickHouse Package
|
|
|
|
`@internal/clickhouse` - ClickHouse client for analytics and observability data.
|
|
|
|
## Migrations
|
|
|
|
Goose-format SQL migrations live in `schema/`. Two rules below are load-bearing — both can block a deploy.
|
|
|
|
### Rule 1: number to `max + 1`, never slot in
|
|
|
|
Goose runs in strict mode in the deploy pipeline. If a migration file numbered *below* the version currently recorded in `goose_db_version` ever shows up, goose refuses to apply it and the deploy fails:
|
|
|
|
```
|
|
goose run: error: found 1 missing migrations before current version 30:
|
|
version 29: 029_add_task_kind_to_task_runs_v2.sql
|
|
```
|
|
|
|
When adding a migration:
|
|
|
|
1. Look at `schema/` and take the largest existing number, call it `N`.
|
|
2. Name your file `0(N+1)_descriptive_name.sql`.
|
|
3. If you've been on a branch while main added migrations, **rebase and renumber** before opening the PR — a file numbered below the new max will block the next deploy after your PR merges.
|
|
|
|
### Rule 2: DDL must be idempotent
|
|
|
|
Migrations can be applied out of order in some environments (`goose up --allow-missing` for local recovery, manual fixups, etc.) and may be retried. Always use idempotent forms so a re-apply is a no-op:
|
|
|
|
```sql
|
|
-- +goose Up
|
|
ALTER TABLE trigger_dev.your_table
|
|
ADD COLUMN IF NOT EXISTS new_column String DEFAULT '';
|
|
|
|
-- +goose Down
|
|
ALTER TABLE trigger_dev.your_table
|
|
DROP COLUMN IF EXISTS new_column;
|
|
```
|
|
|
|
Equivalent forms for other DDL:
|
|
|
|
- `CREATE TABLE IF NOT EXISTS …`
|
|
- `DROP TABLE IF EXISTS …`
|
|
- `ADD INDEX IF NOT EXISTS …` / `DROP INDEX IF EXISTS …`
|
|
- `CREATE MATERIALIZED VIEW IF NOT EXISTS …` / `DROP VIEW IF EXISTS …`
|
|
|
|
ClickHouse supports `IF [NOT] EXISTS` on all of the above. Older migrations in this directory predate the rule and are not idempotent — leave them as-is unless you're explicitly hardening one.
|
|
|
|
## Naming Conventions
|
|
|
|
- `raw_` prefix for input tables (where data lands first)
|
|
- `_v1`, `_v2` suffixes for table versioning
|
|
- `_mv_v1` suffix for materialized views
|
|
- `_per_day`, `_per_month` for aggregation tables
|
|
|
|
See `README.md` in this directory for full naming convention documentation.
|
|
|
|
## Purpose
|
|
|
|
Stores time-series data for task run analytics, event streams, and performance metrics. Separate from PostgreSQL to handle high-volume writes from task execution.
|