Running a Local Shake Alert Log Through Postgres, Not a Spreadsheet

During the 2023 swarm near our county line I was keeping a hand-typed CSV of every felt report my neighbors sent in: roughly 1,400 rows over nine days, timestamps in three different formats, and a column of “was it loud?” notes. The spreadsheet choked at about 900 rows and I lost an afternoon re-sorting. The environment is boring: one old laptop, Postgres 15, no cloud, no dashboard. That was enough.

The fix was to stop treating the log as a document. I made three tables and a view, and the whole thing fits in about 40 lines:

create table felt_report (
  id bigserial primary key,
  reported_at timestamptz not null,
  lat double precision,
  lon double precision,
  intensity smallint check (intensity between 1 and 12),
  note text
);

create table event (
  id bigserial primary key,
  origin_time timestamptz not null,
  mag numeric(3,1),
  depth_km numeric(5,1)
);

create view report_with_event as
select r.*, e.mag, e.depth_km,
       extract(epoch from (r.reported_at - e.origin_time)) as lag_s
from felt_report r
join lateral (
  select * from event e
  where e.origin_time <= r.reported_at
  order by e.origin_time desc limit 1
) e on true;

Then the only rule I enforce is that every entry goes through \copy from a pipe-delimited file, never through typing into a GUI. The pipe file is ugly but stable: 2023-09-14T02:11:07-07|34.02|-117.31|4|dog woke up.

What this buys is the lag column. Before, I could tell you a report was “around 2am”; with lag_s I can see that the loud-note reports cluster between 8 and 25 seconds after origin time, while the ones marked “gentle” show up 40 seconds to several minutes later. That is not a scientific finding, it is a sorting property of how people type, but it changed how I read the pile. The cost is real: I have to maintain the event table by hand, and a wrong magnitude or a missed event quietly corrupts every join for that window. I have done that twice.

The approach fails when reports are not tied to a single event, which is exactly what happens in a swarm. My lateral join assigns each report to the most recent origin, so during overlapping events the lag is meaningless and I have to fall back to reading notes. It also fails if anyone sends a report with no timestamp or a local time without an offset; Postgres will accept a naive string and silently assume the server timezone. I lost two days of reports to that before I added set timezone = 'UTC' at the top of the load script.

What I would like to know from people who log felt reports: do you keep one row per report, or one row per report per event, and how do you decide which event a late-arriving report belongs to when the origin times are minutes apart?