# meowtrace — Project Specification (SPEC.md)

> **Canonical project entry file.** This is the single source of truth for meowtrace.
> All other project docs must link back here, and be linked from here. No orphaned docs.

**Status:** Planning
**Domain:** [meowtrace.com](https://meowtrace.com) (acquired)
**Last updated:** 2026-07-19

---

## Linked Documents

| Doc | Purpose |
|---|---|
| **SPEC.md** *(this file)* | Canonical spec — product, scope, phases, decisions |
| [DEVOPS.md](./DEVOPS.md) | Deployment, hosting, infrastructure, CI/CD notes (meowtrace product) |
| [DATA-IMPORT.md](./DATA-IMPORT.md) | IP→geo dataset shape, ETL/import pipeline, spec-drift log (Phase 1) |
| [API.md](./API.md) | Lookup API reference — endpoints, request/response, error codes (Phase 2) |
| [VIEWER-APP.md](./VIEWER-APP.md) | Standalone Markdown viewer tool — spec + deployment (separate from product) |

_When a new doc is added, register it here **and** ensure it back-links to this SPEC._

---

## 1. Overview

**meowtrace** is a SaaS directory service for IP address → geolocation lookup.
Given an IP address (or domain/ASN), it returns geographic origin, network
provenance, and confidence — the ground-truth on *where* a signal originates.

### Vision
A fast, developer-friendly **IP address to geolocation** service: give it an IP,
get back a location and its metadata, over a clean web UI and a simple API.
Honest about resolution and confidence; simple to integrate.

## 2. Goals & Non-Goals

### Goals
- **Provide an IP address to geolocation service** — the core purpose: IP in,
  location + metadata out.
- Fast, accurate lookups over both a **web UI** and a **developer API**.
- Simple, well-documented developer experience (keys, examples, predictable responses).
- Honest resolution/confidence reporting (city vs. country; never fake certainty).

### Non-Goals
- **Sophisticated fraud tracking** — meowtrace is not a fraud-detection / risk-scoring
  platform. It reports geolocation, not fraud verdicts.
- Person-level tracking / targeting or identifying private individuals.
- (Deferred, not core) heavy enrichment such as VPN/proxy detection or WHOIS —
  may appear later as optional metadata, but not part of the core offering.

## 3. Target Users

**Developers** — the primary and defining audience. Engineers integrating IP
geolocation into their own apps, backends, analytics, or tooling. Design decisions
(API-first, credits, keys, clear docs) serve this audience first. Non-technical
web-UI users are a secondary convenience, not the design center.

## 4. Core Features

Three core features define v1. Detailed below; enrichment/extras are explicitly out.

### 4.1 Single IP lookup
- Input: one IPv4 address _(IPv6: TBD, see §6)_.
- Output: location record + metadata for the range containing that IP (see §6 for
  fields — country, region, city, lat/long, plus metadata).
- Available via **web UI** (type an IP, see the result; auto-detect caller's own IP
  as a convenience) and via **API** (`GET` one IP).
- Cost: **1 credit** per successful lookup (§8). Invalid input is rejected without
  charge _(confirm — assumption)_.
- Clear, predictable JSON response shape; documented error responses (bad IP,
  not-found, out-of-credits → 429).

### 4.2 Bulk IP lookup
- Input: many IPs in one request (e.g. JSON array) _(batch size cap TBD)_.
- Output: array of per-IP results, each with its own status (found / not-found /
  invalid), so one bad entry doesn't fail the batch.
- Cost: **1 credit per IP** resolved (§8); partial success bills only the resolved ones.
  Invalid entries are not charged (per §4.1).
- Primarily an **API** feature; web UI may offer paste-a-list / CSV upload later _(TBD)_.
- **Batch exceeds remaining credits — partial-success behavior (decided):** the batch
  is **processed until credits are exhausted**, then the response returns **partial
  results with a partial-error status**. Resolved IPs (up to the credit limit) return
  their result and are billed; the remainder are marked as unresolved/insufficient-
  credit in the per-IP status. The overall response signals partial completion
  (e.g. HTTP **207 Multi-Status** or a `partial: true` flag with a clear per-IP
  breakdown) rather than rejecting the whole batch or a bare 429.

### 4.3 API access
- **API-key authentication** — per-account keys, multiple keys, revocable, created/
  managed in the User Portal (§5.1).
- RESTful **JSON API** over HTTPS: single-lookup and bulk-lookup endpoints.
- Every call debits credits from the account (§8); responses surface remaining
  balance _(e.g. header)_ so integrators can track usage.
- **Documentation** — reference docs + examples (a first-class deliverable for a
  developer audience).
- _(TBD)_ Response format options, versioning (`/v1/`), SDKs/snippets, OpenAPI spec.

### Out of scope for core (see Non-Goals §2)
- VPN/proxy/hosting detection, ASN/WHOIS/reverse-DNS enrichment, fraud/risk scoring.
  These may return later as **optional** metadata, but are not part of v1 core.

## 5. Architecture

### 5.1 Components

- **Frontend:** **ReactJS + Vite** (TypeScript), client-side **routed** (React Router).
  **No Next.js** — chosen for reduced complexity. Base = Minimal v5.4.0 `vite-ts`
  variant (MUI 5). Preview locally via nginx + Chrome; hosting target TBD (see [DEVOPS.md](./DEVOPS.md)).
  Two surfaces:
  - **User Portal** — public/authenticated app: sign-up/login, IP lookup UI, dashboard
    (credit balance + usage), API-key management, billing/invoices, pricing page.
  - **Admin Portal** — internal, role-gated back office: user/account management,
    credit & tier overrides, usage/billing oversight, dataset refresh controls,
    fair-usage/abuse monitoring, system health.
- **Backend / API:** stateless lookup + account/billing API — **Node.js + TypeScript**
  (TypeScript everywhere; framework e.g. Fastify, finalized in Phase 2). Serves both
  portals and external API-key traffic.
- **Data store:** self-hosted **IP → geolocation database** — **PostgreSQL** (see §6).
  Native integer/`int8range` range-containment for the lookup hot path.
- **Auth / billing:** accounts, API keys, credit ledger (see §8), **Stripe** for
  payments/invoicing (see §10).

### 5.2 Scalable architecture proposal (high-traffic provision)

The lookup path is read-heavy, cacheable, and latency-sensitive — designed to scale
horizontally. Proposal (cloud-agnostic, AWS-leaning per ALB decision):

**Main request path:**

```
Clients → CDN/edge cache → ALB/LB → Stateless API fleet → Cache → IP→Geo DB
```

**Billing path (async, off the hot path):**

```
API fleet → Credit ledger DB ↔ Stripe
```

Stage by stage:

- **Clients** — the React SPA (both portals) and external API-key traffic.
- **CDN / edge cache** — serves repeated hot IPs without touching origin.
- **ALB / LB** — TLS termination, req/sec rate limiting (§8), health checks.
- **Stateless API fleet** — autoscaling; runs the lookup, auth/credit, and billing
  services; holds no session state (tokens + shared cache instead).
- **Cache (Redis / in-memory)** — recent lookups and credit balances (write-behind to
  the ledger) to avoid a DB round-trip on every debit.
- **IP→Geo DB (read replicas)** — the range-containment lookup; the read-heavy hot path.
- **Credit ledger DB ↔ Stripe** — the billing service reconciles the credit ledger
  (source of truth for balance) with Stripe subscriptions/invoices via async webhooks,
  off the hot path.

**Principles:**
1. **Stateless API fleet** behind the **ALB** — autoscale on CPU/RPS; no session state
   in-process (tokens + shared cache instead).
2. **Read-optimized data tier** — the IP→geo table is largely static between refreshes;
   serve from **read replicas** and/or an in-memory/columnar store. Range-containment
   query is the hot path (§6) — index heavily.
3. **Caching layers** — CDN/edge for repeated hot IPs; a **Redis / in-memory cache**
   for recent lookups and for **credit balances** (write-behind to the ledger) to avoid
   a DB round-trip on every debit.
4. **Credit ledger integrity** — atomic debit (DB transaction or atomic counter);
   eventually-consistent cache reconciled against the source-of-truth ledger.
5. **Rate limiting at the ALB/edge** (req/sec) — not in app logic (§8).
6. **Async billing** — Stripe interactions (invoices, webhooks) processed off the hot
   path via a queue/worker (see §10).
7. **Separation of concerns** — lookup, auth/credit, and billing scale independently
   (modular services or clear internal boundaries).
8. **Observability** — metrics (RPS, latency, credit burn), logs, alerts; feeds the
   Admin Portal and fair-usage monitoring.

**Stack (ratified 2026-07-20):** **PostgreSQL** (data store) · **TypeScript everywhere**
(Node.js backend + React/Vite frontend) · **Stripe** (payments). Still TBD: cache
(Redis leaning), compute host, and queue vendor.

### 5.3 Data schema plan

The logical data model (engine-agnostic; concrete DB choice is TBD — §5.2). Entities
and their key fields — a plan, to be refined in Phase 1/3.

**`geo_ranges`** — the IP→geolocation dataset (§6). Read-heavy, largely static.
| Field | Notes |
|---|---|
| `id` | PK |
| `ip_start`, `ip_end` | range bounds (integer form of IP for fast comparison), or `cidr` |
| `ip_version` | 4 / 6 |
| `country`, `region`, `city` | location |
| ~~`latitude`, `longitude`~~ | **dropped in v1** (decision “a” — not in dataset; DATA-IMPORT.md §5) |
| `metadata` | ASN, ISP/org, timezone, postal, accuracy/confidence _(not in current dataset; enrichment out-of-core §2)_ |
| `dataset_version` | which import/refresh this row belongs to |
> Ranges stored as **integer** bounds (`ip_start_int`/`ip_end_int`); indexed on
> `ip_start_int` for containment lookup (the hot path). Concrete import: [DATA-IMPORT.md](./DATA-IMPORT.md).

**`accounts`** — a customer/organization.
| Field | Notes |
|---|---|
| `id` | PK |
| `email`, `auth_*` | login/identity |
| `tier` | free / hobby / enterprise (§8) |
| `stripe_customer_id` | link to Stripe (§10) |
| `role` | user / admin (for Admin Portal gating, §5.1) |
| `created_at` | |

**`api_keys`** — per-account keys (§4.3).
| Field | Notes |
|---|---|
| `id` | PK |
| `account_id` | FK → accounts |
| `key_hash` | store a hash, never the raw secret |
| `label`, `created_at`, `revoked_at` | management/lifecycle |

**`credit_ledger`** — source of truth for balance (§8, §9.1). Append-only.
| Field | Notes |
|---|---|
| `id` | PK |
| `account_id` | FK → accounts |
| `delta` | + (refill/reset/grant) / − (lookup debit) |
| `reason` | lookup / daily-refill / monthly-reset / stripe-grant / admin-adjust |
| `balance_after` | running balance _(or derive)_ |
| `ref_request_id` | correlate a debit to its HTTP log (§9.4) |
| `created_at` | |
> Current balance may be cached (write-behind) and reconciled to this ledger (§5.2).

**`usage_records` / `request_logs`** — per-request diagnostics (§9.1, §9.4).
| Field | Notes |
|---|---|
| `request_id` | PK; shared with ledger `ref_request_id` |
| `account_id`, `api_key_id` | who |
| `endpoint`, `method`, `status`, `latency_ms` | HTTP |
| `caller_ip` | source IP of the API caller |
| `queried_ip` / `batch_size` | what was looked up _(retention/PII policy TBD, §9.1)_ |
| `credits_charged` | billing correlation |
| `created_at` | |

**`subscriptions` / billing** — mirror of Stripe state (§10).
| Field | Notes |
|---|---|
| `id` | PK |
| `account_id` | FK → accounts |
| `stripe_subscription_id` | link |
| `plan`, `status`, `current_period_end` | drives credit provisioning on webhook |

_(Types, indexes, normalization, and the concrete engine are settled in Phase 1
(`geo_ranges`) and Phase 3 (accounts/keys/ledger/logs). This is the plan, not the DDL.)_

## 6. Data Sources

- **Primary:** our own self-hosted **IP → geolocation database**.
- **Shape (actual — from received sample):** a **headerless, `"`-quoted CSV**, **6 columns**,
  with **integer-encoded IPv4 range bounds** (IP2Location-db5 style). Full ETL, column
  mapping, and import pipeline: **[DATA-IMPORT.md](./DATA-IMPORT.md)**.

  | # | Field | Example | Notes |
  |---|---|---|---|
  | 1 | `ip_start_int` | `17367040` | **integer** IPv4 (= `1.9.0.0`), not dotted-quad |
  | 2 | `ip_end_int` | `17367167` | inclusive (= `1.9.0.127`) |
  | 3 | `country_code` | `MY` | ISO 3166-1 alpha-2 |
  | 4 | `country` | `Malaysia` | country name |
  | 5 | `region` | `Kelantan` | state/region (may be empty) |
  | 6 | `city` | `Peringat` | city (may be empty) |

  Lookup = find the range containing the queried IP by integer comparison
  (`ip_start_int ≤ ip ≤ ip_end_int`); ranges are non-overlapping + sorted; index on
  `ip_start_int` (evaluate GiST `int8range` in Phase 1 — see DATA-IMPORT.md).
- **Drift D1 — RESOLVED (decision “a”, 2026-07-20):** dataset has **no latitude/longitude**
  and **no ASN/ISP/metadata**. v1 ships **country/region/city only** — lat/long dropped
  from schema, API response, and map UI. Coordinates return only if a richer dataset is
  adopted later (separate change). See [DATA-IMPORT.md §5](./DATA-IMPORT.md).
- **Volume:** ~800 MB full CSV *(received sample only so far; full file not yet on host)*.
- _(TBD)_ Refresh cadence; IPv6 coverage; empty-field convention; dataset licensing/attribution.
- _(TBD)_ Enrichment layers (ASN / WHOIS / VPN-proxy-hosting detection) — later phase.

## 7. Build Phases

| Phase | Name | Description | Status |
|---|---|---|---|
| 0 | **Planning** | Spec, scope, stack + pricing decisions | ✅ |
| 1 | **Data & lookup core** | ~~Import CSV → `geo_ranges`; range-containment lookup + index~~ **✅ DONE (2026-07-20):** PostgreSQL 18 installed; TS importer ([`src/backend/src/import_geo_ranges.ts`](../src/backend/src/import_geo_ranges.ts)) loads sample via COPY+atomic swap; TS lookup module + CLI proven (e.g. `1.2.3.4 → PL/Warsaw`); index strategies benchmarked (btree vs GiST). Dev works against the **sample fixture**; the full ~800 MB CSV is a **production-only** import (Phase 6) | ✅ |
| 2 | **Lookup API** | ~~Backend API: IP in → location out; validation; error handling~~ **✅ DONE (2026-07-20):** Fastify API ([`src/backend/`](../src/backend/)) — `GET /v1/lookup/:ip` + bulk `POST /v1/lookup` (207 partial per §4.2), health/meta, unified error envelope; all endpoints verified with real HTTP. Ref: [API.md](./API.md) | ✅ |
| 3 | **Accounts & credit ledger** | ~~Sign-up/auth, API keys, credit balance, debit-per-lookup, hard-cap (429); usage records~~ **✅ DONE (2026-07-20):** accounts + bearer sessions; API keys (hashed, shown once, revocable); `credit_ledger` (balance = SUM); Free daily refill @ GMT+8; **atomic debit + 429 hard-cap**; bulk partial-on-exhaust (207); usage/request logging. All proven over HTTPS. Ref: [API.md](./API.md) | ✅ |
| 4 | **Billing, tiers & Stripe** | ~~Stripe subscriptions + webhooks + invoicing~~ **✅ DONE (2026-07-20):** Stripe test-mode products/prices (Hobby $14.90, Enterprise $250); Checkout + Customer Portal; signature-verified idempotent webhooks driving credit provisioning (`checkout.session.completed`, `invoice.paid` monthly reset, `subscription.deleted` downgrade). Proven with a real subscription → 200k credits granted. Ref: [API.md](./API.md) | ✅ |
| 5 | **Frontend (React, routed)** | SPA — **User Portal** (landing, lookup UI, dashboard, usage, key mgmt, billing, pricing) + **Admin Portal** (user/credit/billing/dataset/abuse oversight) | ⬜ |
| 6 | **Deploy & harden (staging→prod)** | Scalable infra to meowtrace.com (§5.2): ALB (incl. req/sec rate limiting), autoscaling API fleet, caching, read replicas, monitoring; fair-usage ceilings (~10M/day, 100 concurrent) | ⬜ |

_Phases are sequential by dependency but may overlap (e.g. FE §5 can start against a
mocked API from §2). Each phase closes only when its spec↔implementation match is
verified; drift is logged as a bug._

## 8. Pricing (credits-based)

meowtrace uses a **credits-based** model. **1 credit = 1 lookup** _(working
assumption — confirm)_. Users hold a credit balance; each lookup debits the ledger.

**Reference provider:** [ipgeolocation.io/pricing.html](https://ipgeolocation.io/pricing.html)
— tiered monthly request allowances, annual billing gives "2 months free" (~17% off),
and overage is sold in fixed blocks (e.g. +25k requests at $5). We model meowtrace's
tiers on this shape.

### Reference tiers (ipgeolocation.io, for modeling — not final meowtrace prices)

| Tier | Included req/mo | Monthly | Annual (effective/mo) | Overage block |
|---|---|---|---|---|
| Starter | 150,000 | $19 | $190 ($16) | +25,000 @ $5 |
| Plus | 500,000 | $49 | $490 ($41) | +40,000 @ $5 |
| Pro | 1,000,000 | $79 | $790 ($66) | +50,000 @ $5 |
| Business | 2,000,000 | $129 | $1,290 ($108) | +65,000 @ $5 |
| Premium | 5,000,000 | $249 | $2,490 ($208) | +85,000 @ $5 |
| Developer | 1,000 req/day | Custom | — | — |

_Annual offer: pay 10 months, get 12 (~17% off)._

### meowtrace tiers (decided)

**Model:** credits-based, **1 credit = 1 lookup**. Overage is **hard-capped** — when
credits run out, lookups are refused (HTTP 429) until the next refill/reset. No
pay-as-you-go overage, no auto-refill.

| Tier | Price | Credits | Refill / Reset | Overage |
|---|---|---|---|---|
| **Free** | $0 | **10 credits/day** | Refilled daily at **00:00 GMT+8 (server time)** | Hard cap — 429 until next daily refill |
| **Hobby** | **US$14.90/mo** | **200,000 credits/mo** | Reset **monthly** (billing cycle) | Hard cap — 429 until next monthly reset |
| **Enterprise** | **US$250/mo** | **Unlimited** *(fair-usage)* | — | Governed by Fair-Usage Policy (below) |

Notes:
- **Free** balance does not roll over; it is a fixed daily allowance (not cumulative).
- **Hobby** unused credits **do not roll over** — reset each monthly cycle.
- **Enterprise** "unlimited" is subject to the Fair-Usage Policy to protect the system.

### Enterprise Fair-Usage Policy (proposed — guards the house)

"Unlimited" must not mean "undefended." Rules to protect against spam/abuse while
staying invisible to honest heavy users:

1. **Daily fair-usage ceiling:** soft cap **~10M lookups/day** per account.
2. **Concurrency cap:** max N in-flight requests per API key (e.g. 100).
3. **Per-key scoping:** limits apply per API key; keys are per-account, revocable.
4. **Anti-abuse heuristics:** flag sustained near-ceiling traffic, single-IP
   credential sharing, or scraping-pattern access for review.

> **Rate limiting (req/sec):** _out of scope for the app layer_ — handled later at the
> **ALB (load balancer)** layer, not in application logic.

_(Ceilings are proposals — tune before launch.)_

### Open pricing questions
- _(TBD)_ Do enrichment lookups (ASN/WHOIS/VPN detection) cost more than 1 credit
  when added later? (For now, all lookups = 1 credit.)
- _(TBD)_ Hobby credit rollover? (assumed: no rollover, resets monthly.)
- _(TBD)_ Annual billing discount for Hobby/Enterprise?
- _(TBD)_ Free-tier daily reset — fixed 00:00 server TZ; which timezone is "server time"?
- _(TBD)_ Overage handling — hard cap, pay-as-you-go blocks, or auto-refill.
- _(TBD)_ Final meowtrace tier names + prices (above table is the reference, not ours).

## 9. Usage Data & Billing Features

### 9.1 Usage tracking (per account / per API key)
- **Credit ledger** — source of truth for balance; every lookup writes a debit record.
- **Usage records** — per lookup: timestamp, API key, queried IP _(retention/PII
  policy TBD)_, result status, credits charged.
- **Aggregates** — rollups by day/month for dashboard + billing: total lookups,
  credits used, remaining balance, success/error split, top consuming keys.
- **Real-time counters** — current-period usage vs. allowance; drives hard-cap (429)
  and dashboard gauges (served from cache, reconciled to ledger — §5.2).
- **Exports** — CSV/JSON usage export for user (own data) and admin (any account).

### 9.4 HTTP request / response logging

Per-request logging of API traffic — for the developer's own debugging/audit (User
Portal) and for operations/abuse review (Admin Portal).

- **What is logged, per request:** timestamp, request ID, account + API key (id, not
  secret), endpoint + method, source IP of the caller, HTTP status code, latency,
  credits charged, request summary (e.g. queried IP(s) / batch size), and response
  summary (result status, error code if any).
- **Request/response bodies:** store a **bounded summary** by default (not full raw
  bodies) to limit size/PII; optional full-body capture is _(TBD)_ and, if added,
  short-retention + redacted.
- **User Portal view:** developer sees a searchable/filterable log of **their own**
  requests — recent calls, status, latency, credits, errors — to debug integrations.
- **Admin Portal view:** ops sees logs across accounts for support, abuse
  investigation (§8 fair-usage), and incident review.
- **Retention:** _(TBD)_ — e.g. detailed logs kept N days, then rolled into the
  aggregates (§9.1); align with the usage-record PII policy.
- **Access + security:** logs are per-account scoped for users; admin access is
  role-gated and audited. Never log API-key secrets or card data.
- **Relation to usage records (§9.1):** the credit **debit** record is the billing
  source of truth; the HTTP log is the richer per-call diagnostic trail. They share
  a request ID for correlation.
- _(TBD)_ Export/stream of logs (CSV/JSON download; later, log-drain/webhook for
  enterprise).

### 9.2 Billing features (User Portal)
- Current plan + credit balance and reset/refill date.
- Usage this period (with history / trend).
- Upgrade / downgrade / cancel plan.
- Payment method management (via Stripe — §10).
- Invoice history + downloadable receipts (Stripe-hosted or synced).
- Billing alerts _(TBD)_ — e.g. approaching hard cap, payment failed.

### 9.3 Billing oversight (Admin Portal)
- View/adjust any account's plan, credits, and balance (overrides).
- Inspect usage + billing history per account.
- Reconcile Stripe state (subscriptions, invoices, failed payments).
- Flag/suspend accounts (fair-usage / abuse — §8).

## 10. Payments & Invoicing (Stripe)

**Provider: Stripe** — payments, subscriptions, and invoicing.

- **Subscriptions** — Hobby (US$14.90/mo) and Enterprise (US$250/mo) as Stripe
  subscription products/prices; Free tier is non-billed (no Stripe subscription).
- **Checkout / Billing Portal** — use Stripe Checkout for sign-up/upgrade and the
  Stripe Customer Portal for self-serve payment-method + invoice management (reduces
  PCI scope; we store no raw card data).
- **Webhooks** — Stripe events (`invoice.paid`, `payment_failed`,
  `subscription.updated/deleted`, etc.) drive credit provisioning and plan state,
  processed **async off the hot path** (§5.2). On successful payment → grant/reset
  credits for the cycle; on failure/cancel → downgrade per dunning policy _(TBD)_.
- **Invoicing** — Stripe-generated invoices/receipts; surfaced in User Portal (§9.2)
  and reconcilable in Admin Portal (§9.3).
- **Idempotency & webhooks security** — verify webhook signatures; use idempotency
  keys; treat Stripe as the billing source of truth, our ledger as the credit
  source of truth, reconciled on webhook events.
- _(TBD)_ Annual billing option; proration on plan change; dunning/retry policy;
  tax handling (Stripe Tax?).

## 11. Open Questions
- ~~Frontend stack?~~ **Decided: ReactJS + Vite (no Next.js), client-side routed.**
- ~~Backend stack / DB engine?~~ **Decided (2026-07-20): PostgreSQL + TypeScript everywhere (Node backend, React/Vite frontend).**
- ~~Credit ↔ lookup ratio; free tier size; overage handling?~~ **Decided (§8):** 1:1,
  Free 10/day, Hobby 200k/mo @ $14.90, Enterprise unlimited @ $250 (fair-usage), hard-cap overage.
- ~~Server-time timezone for Free daily reset?~~ **Decided: 00:00 GMT+8.**
- ~~Hobby rollover?~~ **Decided: no rollover.**
- ~~Enterprise fair-usage ceilings?~~ **Decided (§8):** ~10M/day + 100 concurrent; req/sec rate limiting deferred to ALB.
- ~~Payments provider?~~ **Decided: Stripe [§10].**
- Enrichment-lookup credit cost when added later?
- Hosting target (see [DEVOPS.md](./DEVOPS.md))?
- Usage-record retention + PII policy for queried IPs (§9.1)?
- Stripe: annual billing, proration, dunning, tax handling (§10)?
- Concrete scalable-stack choices — ~~DB engine~~ (Postgres ✓), cache (Redis?), compute, queue (§5.2)?

---

## Changelog

> Any change to this spec is highlighted here. Inconsistencies between spec and
> implementation are flagged as **bugs** with proposed changes.

- **2026-07-19** — Spec initialized. Project scaffold created.
- **2026-07-19** — **Core decisions recorded:** (1) data = self-hosted IP→geolocation
  DB [§5/§6]; (2) frontend = ReactJS, client-side routed [§5]; (3) pricing =
  credits-based, modeled on ipgeolocation.io reference tiers [new §8 Pricing].
- **2026-07-19** — **Pricing finalized to 3 tiers** [§8]: 1 credit = 1 lookup;
  hard-cap overage. **Free** 10 credits/day (daily server-time refill); **Hobby**
  US$14.90/mo, 200k credits reset monthly; **Enterprise** US$250/mo unlimited under a
  proposed **Fair-Usage Policy** (rate/concurrency ceilings, anti-abuse, no-resale).
  **Data shape** [§6] specified: rows of IP range → location + metadata (containment lookup).
- **2026-07-19** — **Fair-usage refined:** Free reset fixed to **00:00 GMT+8**; Hobby
  **no rollover** confirmed; enterprise ceilings held (~10M/day, 100 concurrent).
  **req/sec rate limiting deferred to ALB layer** (removed from app-layer policy);
  no-resale + escalation clauses dropped per decision.
- **2026-07-19** — **Build phases drafted [§7]:** 0 Planning → 1 Data & lookup core →
  2 Lookup API → 3 Accounts & credit ledger → 4 Billing & tiers → 5 Frontend →
  6 Deploy & harden.
- **2026-07-19** — **Goals/users/features detailed [§1–§4]:** goal = provide IP→geolocation
  service; non-goal = sophisticated fraud tracking (+ person-tracking, deferred enrichment);
  target users = **developers** (API-first); core features elaborated — 4.1 single lookup,
  4.2 bulk lookup, 4.3 API access; VPN/WHOIS/ASN enrichment moved explicitly out of core.
- **2026-07-20** — **Frontend base + datasource alignment.** Minimal v5.4.0 kit imported
  (archive → `reference/` read-only; `next-ts` variant → `src/frontend/` — see
  [DEVOPS.md](./DEVOPS.md)). Datasource sample received: aligned §6 & §5.3 to the
  **actual** 6-column headerless integer-IP CSV (IP2Location-db5 shape); added
  [DATA-IMPORT.md](./DATA-IMPORT.md) (ETL pipeline + drift log). Phase 1 updated.
- **2026-07-20** — **Drift D1 resolved (decision “a”):** v1 drops latitude/longitude —
  country/region/city only (no dataset upgrade). Import pipeline built + tested against the sample.
- **2026-07-20** — **Stack ratified [§5]:** **PostgreSQL** + **TypeScript everywhere**
  (Node backend + **React/Vite** frontend, **no Next.js** per reduced-complexity call);
  Stripe unchanged. Frontend base swapped `next-ts` → `vite-ts`.
- **2026-07-20** — **Phase 4 COMPLETE [§7]:** billing, tiers & Stripe (test mode).
  Products/prices (Hobby $14.90/mo→200k, Enterprise $250/mo→unlimited); Checkout +
  Customer Portal; **signature-verified, idempotent webhooks** (`processed_webhooks`)
  driving credit provisioning — `checkout.session.completed` (grant), `invoice.paid`
  (monthly reset, no rollover), `customer.subscription.deleted` (downgrade to free).
  `subscriptions` table mirrors Stripe state. **Proven with a real test subscription:**
  free→hobby, balance 10→200000; cancel→free. Keys in root-only `/etc/meowtrace-api.env`.
  *(Bugs fixed: raw-body webhook parser; `invoice.subscription` moved to `lines[].parent`
  in newer Stripe API.)*
- **2026-07-20** — **Phase 3 COMPLETE [§7]:** accounts & credit ledger. Signup/login
  (bearer session), API keys (SHA-256 hashed, shown once, list/revoke), `credit_ledger`
  as balance source-of-truth, Free-tier daily refill (10/day @ 00:00 GMT+8), **atomic
  debit-per-lookup with 429 hard-cap**, bulk **207** partial-on-credit-exhaust (§4.2),
  and per-request usage logging (§9.1/§9.4). Proven end-to-end over HTTPS. Schema:
  `scripts/schema_accounts.sql`. *(Fixed a daily-refill timezone date-compare bug.)*
  *(Hardening TODO: log real client IP via `X-Forwarded-For`, currently proxy IP.)*
- **2026-07-20** — **Phase 2 COMPLETE [§7]:** Fastify Lookup API built (`src/backend/`)
  — single `GET /v1/lookup/:ip` + bulk `POST /v1/lookup` (200/**207**-partial per §4.2),
  health/meta, unified error envelope (400/404/500; 401/429 reserved for Phase 3). All
  endpoints proven with real HTTP against the sample DB. New [API.md](./API.md) reference.
  *(Bug caught & fixed: masked `***` password leaked into the live DSN → auth-fail 500;
  fixed via `MEOWTRACE_DSN` env, no hardcoded creds.)*
- **2026-07-20** — **Phase 0 marked COMPLETE [§7]** (spec, scope, stack + pricing all
  decided). **Full ~800 MB CSV is a production-only import** (Phase 6); dev/staging work
  against the sample fixture.
- **2026-07-20** — **Phase 1 COMPLETE [§7]:** Postgres 18 installed; importer rewritten
  in TypeScript (`src/backend/`, Node+`pg` COPY, atomic swap); `geo_ranges` loaded from
  sample (306 rows); range-containment lookup module + CLI proven with real IPs; index
  strategy benchmarked (btree vs GiST int8range). Superseded Python importer retired.
  Full ~800 MB import awaits file delivery.
- **2026-07-19** — **Architecture & billing expansion:** (1) removed dataset
  **licensing** mention from §6 to avoid confusion; (2) added **User Portal + Admin
  Portal** to §5; (3) added **scalable high-traffic architecture proposal** [§5.2 —
  ALB, autoscaling stateless API fleet, caching, read replicas, async billing];
  (4) added **Usage Data & Billing Features** [new §9]; (5) **Stripe** named for
  payments/invoicing [new §10]. Sections renumbered (Open Questions → §11); build
  phases §7 updated to reflect portals + Stripe.