Usage Tracking and Metering Design

Issue: #6 Date: 2026-05-07

Goal

Track per-API-key usage (links created, hits served, webhook deliveries), enforce monthly tier quotas, and provide a usage summary endpoint. Master key is unlimited and exempt from all tracking/enforcement.

Approach

Single usage_counters table with one row per API key per month. Atomic increments via a Postgres function (upsert on conflict). Hardcoded tier limits in Python. Quota checked before the metered action; counter incremented after.

Database Changes

New table: usage_counters

CREATE TABLE usage_counters (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  api_key_id UUID NOT NULL REFERENCES api_keys(id) ON DELETE CASCADE,
  month TEXT NOT NULL,  -- 'YYYY-MM' format
  links_created INT NOT NULL DEFAULT 0,
  hits_served INT NOT NULL DEFAULT 0,
  webhook_deliveries INT NOT NULL DEFAULT 0,
  UNIQUE(api_key_id, month)
);

Postgres function: increment_usage_counter

CREATE OR REPLACE FUNCTION increment_usage_counter(
  p_api_key_id UUID,
  p_month TEXT,
  p_field TEXT,
  p_amount INT DEFAULT 1
) RETURNS VOID AS $$
BEGIN
  INSERT INTO usage_counters (api_key_id, month)
  VALUES (p_api_key_id, p_month)
  ON CONFLICT (api_key_id, month) DO NOTHING;

  EXECUTE format(
    'UPDATE usage_counters SET %I = %I + $1 WHERE api_key_id = $2 AND month = $3',
    p_field, p_field
  ) USING p_amount, p_api_key_id, p_month;
END;
$$ LANGUAGE plpgsql;

Atomic upsert + increment. Avoids race conditions from read-then-write in Python.

Tier Limits

TIER_QUOTAS = {
    "free": {"links_created": 100, "webhook_deliveries": 1000},
    "pro": {"links_created": None, "webhook_deliveries": 50000},
}

• None = unlimited
• Hits are tracked but never limited (don't break existing links)
• Master key (api_key_id is None): skip all quota checks and counter increments

Counter Increment Logic

Helper functions

def increment_usage(api_key_id: str, field: str, amount: int = 1):
    month = datetime.utcnow().strftime("%Y-%m")
    supabase.rpc("increment_usage_counter", {
        "p_api_key_id": api_key_id,
        "p_month": month,
        "p_field": field,
        "p_amount": amount,
    }).execute()

def check_quota(api_key_id: str, field: str, tier: str) -> bool:
    limit = TIER_QUOTAS.get(tier, {}).get(field)
    if limit is None:
        return True
    month = datetime.utcnow().strftime("%Y-%m")
    result = (
        supabase.table("usage_counters")
        .select(field)
        .eq("api_key_id", api_key_id)
        .eq("month", month)
        .execute()
    )
    current = result.data[0][field] if result.data else 0
    return current < limit

Increment points

1. POST /shorten: Check links_created quota before creating the link. Increment after successful creation.
2. record_hit_and_notify: Increment hits_served after inserting the hit (best-effort, no quota check).
3. deliver_webhook: Check webhook_deliveries quota before attempting delivery. Increment on successful delivery. If exceeded, log as failed delivery with error "quota exceeded" and skip the POST.

Enforcement behavior

• POST /shorten over quota: return 403 with {"detail": "Monthly link creation limit reached", "limit": 100, "current": 100}.
• deliver_webhook over quota: log to webhook_deliveries with success=false, error_message="quota exceeded", skip the HTTP POST.
• Master key: skip all checks.

Usage Endpoint

GET /usage

Requires X-API-Key. DB keys see their own usage. Master key can pass ?api_key_id=<uuid> to view any key's usage.

Response:

{
  "api_key_id": "550e8400-...",
  "month": "2026-05",
  "usage": {
    "links_created": {"current": 42, "limit": 100},
    "hits_served": {"current": 1583, "limit": null},
    "webhook_deliveries": {"current": 312, "limit": 1000}
  }
}

limit: null means unlimited.

Response headers on POST /shorten

After successful link creation, include:

X-Usage-Links-Created: 42
X-Usage-Links-Limit: 100

No usage headers on GET /{short_id} (public, unauthenticated).

Testing

• increment_usage unit tests: increments correct field, creates new row for new month, works with amount > 1
• check_quota unit tests: true when under limit, false when at limit, true for unlimited, true when no row exists
• POST /shorten enforcement tests: succeeds under quota, returns 403 when exceeded, master key bypasses, response includes X-Usage headers
• GET /usage tests: returns current month usage, DB key sees own usage, master key queries any key, zeros when no usage, 401 for invalid key
• deliver_webhook quota test: skips delivery when exceeded, logs "quota exceeded"

Out of Scope

• Stripe integration (Issue #10)
• Upgrade/downgrade flows (Issue #10)
• Usage alerts or email notifications
• Historical usage beyond current month in the endpoint (can query DB directly)