# meowtrace — Data Import Plan (DATA-IMPORT.md)

> ↩ **Back to canonical entry:** [SPEC.md](./SPEC.md)
> Import/ETL plan for the IP→geolocation dataset ([SPEC.md §6](./SPEC.md#6-data-sources))
> into the `geo_ranges` store ([SPEC.md §5.3](./SPEC.md#53-data-schema-plan)).
> Keep linked from SPEC.md's document table; no orphaned docs.

**Last updated:** 2026-07-20
**Status:** Plan (Phase 1 — Data & lookup core)

---

## 1. Source dataset — actual shape

The example datasource is a **headerless, quoted CSV** with **six columns** and
**integer-encoded IPv4 range bounds**. This is the IP2Location-DB / db5-lite shape
(`ip_from, ip_to, country_code, country_name, region_name, city_name`).

Sample: [`../reference/data-sample/geo-ranges-sample.csv`](../reference/data-sample/geo-ranges-sample.csv)

```
"17367040","17367167","MY","Malaysia","Kelantan","Peringat"
"17367168","17367199","MY","Malaysia","Selangor","Subang Jaya"
"17367200","17367551","MY","Malaysia","Wilayah Persekutuan Kuala Lumpur","Kuala Lumpur"
```

### Column mapping

| # | Raw | Type | Maps to `geo_ranges` | Notes |
|---|---|---|---|---|
| 1 | `17367040` | uint32 | `ip_start_int` | **Integer** IPv4 (`a·2²⁴+b·2¹⁶+c·2⁸+d`), **not** dotted-quad |
| 2 | `17367167` | uint32 | `ip_end_int` | inclusive upper bound |
| 3 | `MY` | char(2) | `country_code` | ISO 3166-1 alpha-2 |
| 4 | `Malaysia` | text | `country` | country name (denormalizable) |
| 5 | `Kelantan` | text | `region` | state/region; may be empty in full set |
| 6 | `Peringat` | text | `city` | city; may be empty / `-` in full set |

**Decoding check (verified):** `17367040` → `1.9.0.0`, `17367167` → `1.9.0.127`
(a 128-IP block). Ranges are contiguous and integer-encoded.

### What the sample does **NOT** contain
- ❌ No **latitude/longitude** (SPEC §6 illustrative schema shows these → **drift**, see §5)
- ❌ No **ASN / ISP / org / timezone / postal / accuracy** metadata
- ❌ No **header row**
- ❌ No **IPv6** rows (all IPv4 in sample; full 800 MB set: confirm)
- Fields are **`"`-quoted**; region/city may be empty strings in the full dataset.

---

## 2. Volume & format

> **Import policy (decided 2026-07-20):** the full ~800 MB CSV is imported in
> **production only** (Phase 6). Dev/staging work against the **sample fixture**
> (`reference/data-sample/geo-ranges-sample.csv`) — same shape, fast to iterate. The
> full file is **not** kept on this host.

- **Size:** ~800 MB CSV (full set) — *production-only import; dev uses the sample fixture.*
- **Rows:** unknown until full file lands (order 10⁶–10⁷ ranges typical for db5).
- **Encoding:** assume UTF-8; verify (IP2Location ships UTF-8). Confirm line endings.
- **Order:** appears **sorted ascending by `ip_start_int`** (needed for range queries — verify across full file).

---

## 3. Target schema (import-facing)

Refines [SPEC.md §5.3 `geo_ranges`](./SPEC.md#53-data-schema-plan) to the **actual** source.
Engine still TBD (§5.2); DDL below is Postgres-leaning illustration.

```sql
CREATE TABLE geo_ranges (
  id            BIGSERIAL PRIMARY KEY,
  ip_start_int  BIGINT NOT NULL,        -- uint32 IPv4, integer form
  ip_end_int    BIGINT NOT NULL,        -- inclusive
  country_code  CHAR(2),
  country       TEXT,
  region        TEXT,
  city          TEXT,
  ip_version    SMALLINT NOT NULL DEFAULT 4,
  dataset_version TEXT NOT NULL         -- which import this row belongs to
);
-- v1 (decision “a”): NO latitude/longitude, NO ASN/ISP metadata columns.
```

**Index for the hot path (range containment):**
```sql
-- Lookup: WHERE ip_start_int <= $ip AND ip_end_int >= $ip  (non-overlapping ranges)
CREATE INDEX idx_geo_ranges_start ON geo_ranges (ip_start_int);
-- Optionally a range/GiST index (int8range) for containment — evaluate in Phase 1:
-- ALTER TABLE geo_ranges ADD COLUMN ip_range int8range
--   GENERATED ALWAYS AS (int8range(ip_start_int, ip_end_int, '[]')) STORED;
-- CREATE INDEX idx_geo_ranges_gist ON geo_ranges USING gist (ip_range);
```

> Because ranges are non-overlapping & sorted, the lookup is:
> `SELECT ... FROM geo_ranges WHERE ip_start_int <= :ip ORDER BY ip_start_int DESC LIMIT 1`
> then confirm `:ip <= ip_end_int`.
>
> **Benchmark (Phase 1, 306-row sample, 5k random lookups):** btree DESC-LIMIT-1
> ≈ 0.32 ms/lookup; GiST `int8range @>` ≈ 0.30 ms/lookup — GiST marginally ahead, both
> sub-ms at this size. **Decision:** keep the btree `ip_start_int` index as the default;
> **re-benchmark both at full ~800 MB scale** before locking the hot-path index.

---

## 4. Import pipeline (plan)

Staged ETL — idempotent, versioned, no live-table downtime.

1. **Land** — place full CSV outside the repo (it's 800 MB — do **not** commit; add to
   `.gitignore`). Verify checksum + row count.
2. **Validate (sample-then-full)**
   - Column count = 6 on every row; strip quotes; assert `ip_start_int ≤ ip_end_int`.
   - Assert non-overlap + ascending order (flag gaps/overlaps as data bugs).
   - `country_code` ∈ ISO-3166 alpha-2; empty region/city allowed → store `NULL`.
3. **Bulk load** — stream into a **staging table** using the engine's fast path
   (Postgres `COPY FROM`; or `LOAD DATA` / columnar bulk import) — **never** row-by-row
   INSERTs. Target: full 800 MB load in minutes, not hours.
4. **Index after load** — build indexes **post-COPY** (faster than maintaining during insert).
5. **Version + swap** — tag rows with `dataset_version` (e.g. `ip2location-2026-07`);
   atomically promote staging → live (table rename / partition swap / view flip) so
   lookups never see a half-loaded table.
6. **Verify** — spot-check known IPs (e.g. `1.9.0.5` → MY/Selangor…), row counts, min/max
   bounds, coverage %; record in changelog.
7. **Refresh cadence** *(TBD, SPEC §6)* — re-run pipeline on new dataset drops; keep the
   prior `dataset_version` for rollback.

### Tooling (proposal)
- One import script (Node or Python) — stream-parse CSV, no full-file buffering.
- Dockerized DB for local Phase-1 dev; same script targets staging/prod.
- Dry-run mode (validate + report, no write) for new dataset drops.

---

## 5. Spec alignment & drift (bugs to reconcile)

Per workspace **Rule 2** (spec↔impl drift = bug):

| # | Drift | SPEC says | Actual data | Resolution |
|---|---|---|---|---|
| D1 | **lat/long** | §6 & §5.3 list `latitude`/`longitude` | **absent** in this dataset | **RESOLVED (2026-07-20, decision “a”):** v1 ships **country/region/city only** — lat/long dropped from the schema, API response, and map UI. If coordinates are wanted later, upgrade the dataset (db11+) as a separate change. |
| D2 | **IP form** | §6 example shows dotted-quad (`1.2.3.0`) / CIDR | **integer uint32** | Store integer bounds (`ip_start_int`/`ip_end_int`); dotted-quad is a display concern only. SPEC §6 example updated to note integer form. |
| D3 | **metadata** (ASN/ISP/tz/postal) | §5.3 `metadata` column, §6 "TBD which" | **none** in this set | v1 core = country/region/city only; enrichment stays out-of-core (consistent with §2 Non-Goals). |
| D4 | **header** | — | headerless | Importer must **not** expect a header; column order is the contract. |
| D5 | **importer swap index-name collision** | atomic swap should promote staging → live | **BUG (2026-07-26, FIXED):** the first full-DB import (10.1M rows) **rolled back** at the swap — `relation "geo_ranges_start_idx" already exists`. Renaming the live table to `geo_ranges_old` does **not** rename its indexes (Postgres keeps index names on table rename), so the fixed global names `geo_ranges_{start,end}_idx` stayed occupied and the staging-index rename collided. Compounded by `main()` printing `all done ✔` even after a rolled-back run (a failure masked as success). | **FIXED:** swap now frees the old names first — `ALTER INDEX IF EXISTS geo_ranges_{start,end}_idx RENAME TO geo_ranges_old_*` — before promoting staging, idempotently. `main()` now checks `process.exitCode` and reports failure instead of `all done ✔`. Re-import verified: 10,141,528 rows, full coverage, `88.216.56.221 → SG/Singapore`. |
| D6 | **migrations not inline (fresh DB failed to build)** | fresh DB builds from `scripts/schema_*.sql` alone | **BUG (2026-07-27, FIXED):** a throwaway fresh-DB apply failed — (a) `processed_webhooks` existed live but was in **no** schema file; (b) `usage_rollup_minute` (added to `schema_accounts.sql`) FK-references `organizations`, which is created later in `schema_orgs.sql`, so `schema_accounts.sql` errored `relation "organizations" does not exist`. | **FIXED:** captured `processed_webhooks` in `schema_accounts.sql` (matched to live, Rule 2.5); moved `usage_rollup_minute` → `schema_orgs.sql` (after orgs exist); split the role-aware `GRANT` per file. Fresh-DB build now clean (13 tables, 0 errors). Apply order + verify-fresh procedure in [DEVOPS.md](./DEVOPS.md). |
| D10 | **`subscriptions.account_id` NOT-NULL constraint drift** (the D7 pass missed this table) | app writes `subscriptions` with `account_id` NULL (org-scoped) → column must be nullable everywhere | **BUG (2026-07-27, FIXED):** a real customer completed a **paid** Stripe checkout (org 6, `cus_UxQU…`, subscription `active`) but returned to the app still on **FREE**. The `checkout.session.completed` webhook crashed **3×** (Stripe retried) with `null value in column "account_id" of relation "subscriptions" violates not-null constraint` (23502) at `billing.ts:122`. In the **D7** pass I dropped the stale `account_id NOT NULL` on `api_keys`/`credit_ledger`/`usage_records` but **missed `subscriptions`**, which is org-rescoped too — the handler INSERTs it with `account_id=NULL`, prod still had `NOT NULL`, so the row (and tier/credit provisioning) never landed. **The schema-diff guard did NOT catch it** because a pure column-*presence* diff sees `account_id` on both sides; it wasn't checking *nullability*. | **FIXED:** (1) `schema_orgs.sql` now `ALTER TABLE subscriptions ALTER COLUMN account_id DROP NOT NULL` (mirrors the D7 block); applied the same to prod RDS. (2) Provisioned org 6 to `hobby` + subscription row from the live Stripe sub; the customer re-fires the webhook so the *fixed* handler grants the 200k hobby allowance natively. (3) **Hardened `verify-schema.sh`** to fold `nullable=YES/NO` into the diff line so NOT-NULL constraint drift is caught, not just column presence — self-tested: flipping `subscriptions.account_id` back to NOT NULL now flags. **Systemic lesson:** drift isn't only missing columns — it's missing *constraints* too; the guard must compare the full column signature (type + nullability), and every org-rescope must drop `account_id NOT NULL` on **all** affected tables in one pass. |
| D9 | **prod RDS missing `processed_webhooks` table** (caught by the new schema-diff guard) | prod RDS == schema files (all tables) | **BUG (2026-07-27, FIXED):** the first run of the new `scripts/verify-schema.sh` guard diffed a fresh-built column set against prod RDS and found prod **missing the entire `processed_webhooks` table** (77 cols/11 tables vs schema's 79/12). I had *assumed* (commit `219231a`) it "was live from Phase 4, so re-applying is a no-op" — **wrong**: prod RDS was built fresh from the scripts *before* `processed_webhooks` was captured, so it never got the table. **Live risk:** the Stripe webhook handler INSERTs into `processed_webhooks` for idempotency — `checkout.session.completed`/`invoice.paid` deliveries would 500 and fail to provision paid credits. | **FIXED:** created `processed_webhooks` on prod RDS + granted `mt_api`; re-diff = **NO DRIFT** (13 tables, 79 cols, prod == staging == schema). **This is exactly what the guard is for** — it distrusted an eyeball assumption and found what a human diff missed. `scripts/verify-schema.sh` now guards against this class; run it against staging + prod after any schema change (see [DEVOPS.md](./DEVOPS.md)). |
| D8 | **staging↔prod missing-column drift (`accounts.suspended`)** | schema files == every live column | **BUG (2026-07-27, FIXED):** prod **lookup** API 500'd on every authed call — `column "a.suspended" does not exist` (code 42703) in `resolveApiKey` (routes.ts:60), which joins `accounts` and checks `a.suspended` (admin flag/suspend). `accounts.suspended` was added to **staging** ad-hoc during the admin build-out and **never captured in any schema file**; prod RDS (built cleanly from scripts) had no such column. Third instance of this drift class (after D7 and the org-PATCH shape) — the through-line: **staging accumulated ad-hoc ALTERs that were never written back to the schema, so prod (the honest fresh build) exposed each gap at runtime.** | **FIXED:** added `suspended BOOLEAN NOT NULL DEFAULT false` to the `accounts` CREATE + an idempotent `ADD COLUMN IF NOT EXISTS` backfill in `schema_accounts.sql`; applied the same ALTER to prod RDS. Live prod lookup now **200** (`8.8.8.8 → US`, `88.216.56.221 → SG`). Fresh-build re-verified (13 tables, `accounts.suspended` present, 0 errors). **Systemic lesson:** when a live DB and the schema files disagree, **the schema files are the bug** — periodically diff a fresh-built DB's full column set against staging/prod to catch un-captured ad-hoc ALTERs before prod does. |
| D7 | **staging↔prod column-constraint drift (`account_id NOT NULL`)** | schema files == the live org-scoped reality | **BUG (2026-07-27, FIXED):** prod signup 500'd — `null value in column account_id of credit_ledger violates not-null constraint`. The app is org-scoped (`grantSignup` inserts `credit_ledger` with `org_id` only, `account_id` NULL), but `schema_accounts.sql` declares `account_id NOT NULL`. **Staging worked only because its `account_id` had been manually `ALTER`ed to nullable during Phase 5b — an ALTER never captured in the schema files.** Prod RDS, built cleanly from the scripts, kept `NOT NULL` and rejected every signup. The D6 "migrations inline" pass checked table **existence + ordering** but not **column constraints**, so a structurally-clean fresh build still failed at runtime on first signup. | **FIXED:** `schema_orgs.sql` now `ALTER COLUMN account_id DROP NOT NULL` on `credit_ledger`, `api_keys`, `usage_records` as part of the account→org re-scope (`subscriptions` stays account-scoped). Applied the same idempotent ALTERs to prod RDS; live signup → **201**. **Lesson:** fresh-build verification must include a **runtime smoke test** (signup/login) against the fresh DB, not just DDL success. |

> **D1 — CLOSED:** v1 ships **without** lat/long (decision “a”). The `geo_ranges` schema
> (§3), the importer, and the API response carry country/region/city only. Any map UI
> in the frontend renders from country/region/city (e.g. country-centroid), not per-IP
> coordinates. Revisit only if a richer dataset is adopted.

## 5a. Import script (TypeScript — canonical)

**[`../src/backend/src/import_geo_ranges.ts`](../src/backend/src/import_geo_ranges.ts)**
— the runnable importer (Node + `pg`, `COPY` fast path). Implements this plan:
validate → `COPY` into staging → index-after → atomic swap → verify. Postgres target.

> The earlier Python importer was **retired** to `scripts/retired/` after the
> “TypeScript everywhere” stack decision (2026-07-20). TS is now canonical.

```bash
cd src/backend && npm install      # one-time

# 1) Validate a new drop first (no writes):
npm run import -- --file /path/to/data.csv --validate-only

# 2) Full import into Postgres (atomic staging → live swap):
npm run import -- --file /path/to/data.csv --dataset-version ip2location-2026-07

# Dry run (build+verify staging, do NOT promote):
npm run import -- --file /path/to/data.csv --no-swap

# Look up an IP against the live table:
npm run lookup -- 1.2.3.4
```

Connection via `MEOWTRACE_DSN` env (default local dev DSN). Flags: `--validate-only`,
`--no-swap`, `--no-strict-order`, `--dataset-version <tag>`. Previous live table kept
as `geo_ranges_old` after a swap, for rollback. Schema owned by the importer's swap;
reference DDL: [`../scripts/schema.sql`](../scripts/schema.sql).

**Verified against the sample** (`reference/data-sample/geo-ranges-sample.csv`):
**306 rows, 5 countries (AU/CN/JP/PL/TH), 0 errors**, every row 6 cols, no empty
region/city, all country codes 2-char; coverage `1.0.148.0 .. 1.4.135.255`. Shape
identical to the first sample — **no script/schema change needed**. This is the working
dev fixture.

**Verified against the FULL dataset (2026-07-26):** `/home/ubuntu/IP-COUNTRY-REGION-CITY.CSV`
(776.9 MB, IP2Location DB) — **10,141,528 rows, 0 errors**, coverage `0.0.0.0 .. 255.255.255.255`;
COPY ~379 s + index build, atomic swap promoted live. `dataset_version = ip2location-2026-07`.
Spot-check `88.216.56.221 → SG/Singapore/Singapore/Singapore`. First full import hit the
swap bug **D5** (above) and rolled back; fixed and re-run clean. Previous table retained as
`geo_ranges_old` for rollback.

> **Swap safety note (from D5):** the staging→live promotion renames both the live table
> **and its indexes** out of the way (`geo_ranges_old` / `geo_ranges_old_{start,end}_idx`)
> before claiming the canonical names. It is idempotent and re-runnable; a failed run rolls
> back fully and reports the failure (no false `all done ✔`).

---

## 6. Open questions
- Full 800 MB file: exact row count, IPv6 presence, empty-field convention (`""` vs `-`)?
- Licensing/attribution of the dataset (IP2Location LITE requires attribution)?
- Refresh cadence + rollback retention (how many `dataset_version`s kept)?
- D1: lat/long — drop from v1, or upgrade dataset?
- Final DB engine (drives `COPY` vs `LOAD DATA` vs columnar) — SPEC §5.2 TBD.

---

## Changelog
- **2026-07-26** — **Full DB imported + importer swap bug (D5) fixed.** First full import
  (10,141,528 rows, 776.9 MB IP2Location CSV) **rolled back at the atomic swap** —
  `relation "geo_ranges_start_idx" already exists`: renaming the live table to
  `geo_ranges_old` does not rename its indexes, so the fixed global index names stayed
  occupied and the staging-index rename collided (masked by a false `all done ✔`). **Fix:**
  swap frees the old index names first (`ALTER INDEX IF EXISTS … RENAME TO geo_ranges_old_*`,
  idempotent); `main()` now honours `process.exitCode` and reports failure instead of a
  false success. Re-import verified: 10.1M rows, coverage `0.0.0.0..255.255.255.255`,
  `dataset_version = ip2location-2026-07`, `88.216.56.221 → SG/Singapore`. Logged as **D5** (§5).
- **2026-07-20** — **Import policy:** full ~800 MB CSV is **production-only** (Phase 6);
  dev/staging work against the sample fixture. Full file not kept on host.
- **2026-07-20** — **Phase 1 built.** Postgres 18 installed; importer **rewritten in
  TypeScript** (`src/backend/`, Node+`pg` COPY+atomic swap) — Python version retired to
  `scripts/retired/`. `geo_ranges` loaded from the 306-row sample; lookup module + CLI
  proven with real IPs; btree-vs-GiST benchmark recorded (§3). `scripts/schema.sql`
  noted as reference DDL (importer owns creation).
- **2026-07-20** — **Second sample checked** (`/home/ubuntu/sample.csv`, 306 rows, 5
  countries): same 6-col integer-IP shape, validated clean — no script/doc change
  required. Promoted it to the canonical fixture (`reference/data-sample/`).
- **2026-07-20** — **D1 resolved (decision “a”):** v1 drops lat/long — country/region/city
  only. Added runnable importer (§5a; originally Python, later rewritten in TypeScript),
  verified against the sample.
- **2026-07-20** — Created. Aligned to **actual** datasource shape (6-col headerless
  integer-IP CSV, IP2Location-db5 style); mapped columns, drafted staged import
  pipeline + range-index strategy; logged spec drift D1–D4 (esp. **no lat/long**).