# Production errors from 16 apps: queries behind every number

Companion data for https://bugsly.dev/blog/production-errors-16-apps-lessons

Snapshot: 2026-11-17, about 09:40 UTC. Everything below is read-only (`SELECT` only). Organization 1 is our own; every other organization is excluded by the `organization_id = 1` filter. Counts only: no event contents, emails or IPs are published.

## Setup

Run the Postgres queries against Bugsly's issue database (tables `issues`, `projects`, `alert_rules`) and the ClickHouse queries against its `events` table, both read-only.

Every Postgres query below starts with this CTE (`b` = our issues):

```sql
WITH b AS (
  SELECT i.*, p.platform
  FROM issues i JOIN projects p ON p.id = i.project_id
  WHERE p.organization_id = 1
)
```

## Q1. Headline totals (89,499 occurrences, 1,195 issues, 16 projects, first seen 2026-03-13)

```sql
-- append to the CTE above
SELECT count(*) AS issues, sum(event_count) AS occurrences,
       count(DISTINCT project_id) AS projects_with_errors,
       min(first_seen)::date AS first_seen, max(last_seen)::date AS last_seen
FROM b;
-- result: 1195 | 89499 | 16 | 2026-03-13 | 2026-11-17
```

Projects in org 1 (any with zero issues, 17 in total):

```sql
SELECT count(*) FROM projects WHERE organization_id = 1;   -- 17
```

## Q2. By platform (Python 56,302 = 62.9%; Next.js 24,223; Node 5,970; Ruby 3,004)

```sql
SELECT platform, count(DISTINCT project_id) AS projects, count(*) AS issues, sum(event_count) AS occ
FROM b GROUP BY 1 ORDER BY 4 DESC;
```

The Python project is Bugsly's own backend (its historic project name is Watchdog).

## Q3. Category table (12 buckets)

Heuristic regular expressions over `issues.title`; first matching rule wins. Saved as `category.sql` in this folder.

```sql
WITH b AS (
  SELECT i.id, i.project_id, i.event_count, i.title
  FROM issues i JOIN projects p ON p.id = i.project_id
  WHERE p.organization_id = 1
), c AS (
  SELECT *,
  CASE
    WHEN title ~* 'ClickHouse|HTTPConnectionPool\(host=.bugsly-clickhouse' THEN '01 ClickHouse limits and connectivity'
    WHEN title ~* 'Minified React error #(418|423|425)|hydrat' THEN '02 React hydration mismatch'
    WHEN title ~* 'does not exist' AND title ~* 'column|relation|table' THEN '03 Schema drift (missing column/table)'
    WHEN title ~* 'ConnectionNotEstablished|connection pool|pool timeout|too many connections|could not connect to server|connection to server' THEN '04 Postgres connection/pool'
    WHEN title ~* 'redis' THEN '05 Redis'
    WHEN title ~* 'quota|rate limit|insufficient|out of credits|credits' THEN '06 Quota / credits / rate limits'
    WHEN title ~* '(Client|Server) error .[45][0-9][0-9]|HTTP [45][0-9][0-9]|status code [45][0-9][0-9]|API error|failed \([45][0-9][0-9]\)|[A-Za-z]+ [45][0-9][0-9]: |API failed: [45]' THEN '07 HTTP 4xx/5xx from an upstream API'
    WHEN title ~* 'token refresh|not connected|oauth|unauthor|signature|invalid_grant|jwt|not verified|missing or inactive' THEN '08 Auth, tokens, unverified identities'
    WHEN title ~* 'Failed to fetch|fetch failed|NetworkError|Load failed|ECONNRESET|ETIMEDOUT|socket hang up|EAI_AGAIN' THEN '09 Network failures (fetch/DNS/socket)'
    WHEN title ~* 'TypeError|NoMethodError|is not defined|is not a function|cannot read|has no attribute|has no len|ArgumentError|KeyError|ModuleNotFound|NameError|AttributeError|ImportError|ReferenceError|ValueError|DataError' THEN '10 Plain code bugs (type/null/import)'
    WHEN title ~* 'Next\.js|RSC|Server Action|router|IpSpoof|Unknown Error|Prisma' THEN '11 Framework/proxy/ORM misc'
    ELSE '12 Other (long tail)'
  END AS cat
  FROM b
)
SELECT cat, count(*) AS issues, sum(event_count) AS occurrences,
       round(100.0*sum(event_count)/(SELECT sum(event_count) FROM c),1) AS pct_occ
FROM c GROUP BY 1 ORDER BY 1;
```

Result used in the article (issues / occurrences / %): 01: 32/13,953/15.6 | 02: 132/1,060/1.2 | 03: 20/4,597/5.1 | 04: 48/673/0.8 | 05: 8/211/0.2 | 06: 63/2,349/2.6 | 07: 93/29,049/32.5 | 08: 55/3,169/3.5 | 09: 37/9,091/10.2 | 10: 90/19,189/21.4 | 11: 90/3,518/3.9 | 12: 527/2,640/2.9. Issues sum to 1,195 and occurrences to 89,499.

## Q4. Concentration (top 1 = 24.5%, top 3 = 50.7%, top 10 = 75.9%, top 12 = 78.7%) and singletons (465 = 38.9%)

```sql
-- append to the Q1 CTE
, r AS (SELECT event_count, row_number() OVER (ORDER BY event_count DESC) rk, sum(event_count) OVER () tot FROM b),
  c AS (SELECT rk, tot, sum(event_count) OVER (ORDER BY rk) cum FROM r)
SELECT rk, cum, round(100.0*cum/tot,1) AS pct FROM c WHERE rk IN (1,3,5,10,12,30,60,120);

SELECT count(*) FILTER (WHERE event_count = 1) AS singletons,
       sum(event_count) FILTER (WHERE event_count = 1) AS singleton_occ  -- 465 issues, 465 occurrences (0.52%)
FROM b;
```

## Q5. Title families (1,195 issues -> 556 families)

```sql
SELECT count(DISTINCT left(regexp_replace(title,'[0-9]+','N','g'),70)) AS families FROM b;   -- 556
```

## Q6. Statuses (Resolved 1,069 / Regression 59 / Ignored 44 / Unresolved 23) and regression share

```sql
SELECT status, count(*) AS issues, sum(event_count) AS occ FROM b GROUP BY 1 ORDER BY 2 DESC;
-- RESOLVED 1069 | 76967 ; REGRESSION 59 | 12215 (13.6% of 89,499) ; IGNORED 44 | 210 ; UNRESOLVED 23 | 107
SELECT count(DISTINCT resolution_type) , count(resolution_type) FROM b;  -- resolution_type is NULL on every row: no fix tracking
```

## Q7. Own email API (27 issues, 23,725 occurrences = 26.5%) and AI/media providers (55 issues, 5,474 = 6.1%; OpenAI-only 8 issues, 81)

```sql
SELECT count(*), sum(event_count) FROM b WHERE title ~* 'aisend';        -- 27 | 23725
SELECT count(*), sum(event_count) FROM b WHERE title ~* 'openai|anthropic|gemini|veo|elevenlabs|fpt\.ai|flux|tts|replicate|fal\.ai';  -- 55 | 5474
SELECT count(*), sum(event_count) FROM b WHERE title ~* 'openai';        -- 8 | 81
```

## Q8. Top 12 issues (hand triage table)

```sql
SELECT row_number() OVER (ORDER BY event_count DESC) rk, project_id, event_count,
       left(culprit,70) AS culprit,
       left(regexp_replace(title,'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+','<email>','g'),80) AS title   -- strips emails from titles
FROM b ORDER BY event_count DESC LIMIT 12;
```

Bucket sums (my manual labels, not measured): code only = #2 12,348 + #5 4,984 = 17,332 (19.4%); config/infra/migration = #1 21,926 + #3 11,123 + #6 3,541 + #7 3,147 + #8 2,273 + #12 1,235 = 43,245 (48.3%); no signal = #4 5,371 + #9 1,742 + #10 1,483 + #11 1,245 = 9,841 (11.0%). Top-12 total 70,418 (78.7%).

## Q9. Alert rules (32 active: 16 NEW_ISSUE + 16 REGRESSION, all EMAIL)

```sql
SELECT condition_type, action_type, is_active, count(*)
FROM alert_rules r JOIN projects p ON p.id = r.project_id
WHERE p.organization_id = 1 GROUP BY 1,2,3;
```

## Q10. ClickHouse: ingest latency, SDK mix, retention

Project ids of org 1 were taken from Postgres (`SELECT id FROM projects WHERE organization_id = 1` -> 1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,42; ClickHouse also holds a few events for id 46, which is not an org-1 project in the snapshot and is excluded).

```sql
-- latency, n = 15,190; p50 108 ms; p90 17,249 ms; p99 154,406 ms
SELECT count() n,
  quantile(0.5)(dateDiff('millisecond', timestamp, received_at)) p50_ms,
  quantile(0.9)(dateDiff('millisecond', timestamp, received_at)) p90_ms,
  quantile(0.99)(dateDiff('millisecond', timestamp, received_at)) p99_ms
FROM events
WHERE project_id IN (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,42)
  AND received_at > timestamp AND received_at < timestamp + INTERVAL 1 DAY;

-- stored events (15,291 for org 1; 90-day TTL)
SELECT count() FROM events WHERE project_id IN (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,42);
SHOW CREATE TABLE events;   -- TTL toDateTime(timestamp) + toIntervalDay(90)

-- SDK mix
SELECT sdk_name, count() c FROM events
WHERE project_id IN (1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17,42) GROUP BY 1 ORDER BY 2 DESC;
-- nextjs 2,690 | python.fastapi 1,058 | ruby.sidekiq 709 | ruby 706 | dart 613 | ... (9,037 events have an empty sdk_name)
```

## Not measured (and not claimed)

- Event-to-alert time: alert delivery is not timestamped. Only ingest latency (Q10) is reported.
- Fix rate: `autofix_attempts` has no rows for org 1 and `resolution_type` is empty, so no agent or code-fix rate exists. The top-12 triage is a manual judgment.
