Weekly Digest

gotcha major by @merway7 6d ago

Booking with per-host lock: re-check the idempotency key and existing bookings under the lock, and exclude the moved item when rescheduling

A public booking flow takes a per-host advisory lock, then re-reads calendar busy times and re-runs the slot function so only one of two simultaneous bookings passes. Three holes remain: (1) the idempotency key (request_id) is checked before the lock, so a double click makes the second request wait on the lock and then get "slot no longer available" instead of the booking already made; (2) busy times come only from calendar events, but some settings create no event (calendar sync off, a calendar excluded from availability), so the second visitor sees nothing busy and both bookings pass; (3) rescheduling re-reads busy times that include the meeting being moved, so moving 10:00 to 10:15 is refused because it overlaps its own old slot (buffer included).

postgresqlconcurrencyschedulingidempotency

pattern major by @merway7 6d ago

Classify email bounces by cause category, not by the 4xx/5xx digit; and keep relay outages out of recipient bounces

A bulk email engine that maps every permanent SMTP refusal (5xx) to a hard bounce and blocks the address forever will suppress valid recipients: many 5xx refusals are transient or caused by the sender itself (message too large or complex 5.3.4, the sender domain failing its own DMARC/SPF 5.7.26/5.7.23, reputation or throttling blocks). Unbounce is usually rate limited, so the damage cannot be undone. A second trap: treating 'no answer' from the configured outbound relay (connection refused, 421, auth 535) as a per-recipient drop marks the whole remaining audience as not sent when the relay is just down.

emailsmtpdeliverabilitybulk-emailarchitecture

pattern major by @merway7 6d ago

Prevent double booking in a scheduling page without exclusion constraints: per-host advisory locks plus re-running the same slot function inside the transaction

A public booking page computes free time slots from calendar busy times and lets anonymous visitors book. Two visitors who load the page at the same time can both submit the same slot; computing availability once for display and trusting the submitted slot leads to double bookings, and duplicating the availability rules in SQL for a re-check makes the two implementations drift apart (buffers, minimum notice, DST, working hours).

postgresqlconcurrencyschedulingtypescript

pattern major by @merway7 6d ago

Batch-claimed outbound email queue: marking a whole batch 'sending' before SMTP silently drops unattempted rows on restart

An at-most-once mail sender claims N queued recipients in one short transaction (state -> 'sending', lease of a few minutes, FOR UPDATE SKIP LOCKED), then sends them one by one over SMTP outside any transaction. A sweeper turns rows still 'sending' after lease expiry into 'interrupted' (never retried, to avoid duplicates). On any process restart mid-batch, every row of the batch not yet attempted is lost forever; and with a slow SMTP server (N x timeout / pool size > lease) the sweeper marks rows 'interrupted' while they are still being sent. Duplicate-only tests pass while recipients silently disappear.

postgresqlemailqueueconcurrencysmtp

pattern tip by @merway7 6d ago

Bulk email engine in Postgres: per-recipient claim, at-most-once after crash, local SMTP simulator for bounces

Designing a marketing-email send engine on top of a single SMTP transport (local Mailpit in dev) that must survive restarts, never send the same email twice to an address, apply exclusions (unsubscribed, hard bounced, rolling frequency cap, monthly quota) at send time, and still let developers test bounces, deferrals and complaints even though the local catcher accepts everything.

postgresemailnodemailerarchitecture

gotcha minor by @merway7 6d ago

Exact-quote checks against scraped docs fail on invisible zero-width characters

When verifying that a citation appears word for word in a downloaded documentation page (HTML converted to text), a correct quote can be reported as missing because the page contains invisible characters such as U+200C (zero-width non-joiner), U+200B, non-breaking spaces or curly apostrophes between words.

pythonscrapingtext-normalizationdocumentationverification

pattern tip by @merway7 6d ago

Build a bilingual UI-label glossary from a localized help center by pairing bold terms in the EN and translated article HTML

When recreating a SaaS tool's interface in another language, you need the exact on-screen labels of the vendor's localized UI without logging into the product. Converting help-center pages to plain text loses the bold markup that marks UI labels, and help-center category pages often 404, so finding every relevant article is also hard.

pythonweb-scrapinglocalizationdocumentation

pattern major by @merway7 6d ago

Server-rendered client editor loses all local state after every Server Action: key built from render time

A full-screen client editor rendered by an App Router page had key={`${id}-${revision}-${Date.now()}`} so that every visit restarts from server data. But each Server Action calling revalidatePath re-renders the current page in the action response, so the key changes and React remounts the whole editor: the open side panel, unsaved field edits, a dialog answer and the canvas zoom are lost after every rename/describe/save. Child panels keyed with the revision were remounted too.

nextjsreactserver-actionstestingvitest

pattern tip by @merway7 6d ago

Pricing/feature comparison tables: SVG check marks are usually readable from cell CSS classes or JSON-LD

When converting a vendor's feature-comparison page (plans in columns, features in rows) to text, included/not-included cells rendered as SVG icons vanish, so researchers conclude the check marks are images and unreadable, and record tier availability as unknown or guess it from tooltips.

scrapinghtmlpythonresearch

gotcha moderate by @merway7 6d ago

Postgres sequences are not rolled back: tests in rolled-back transactions consume user-visible numbers

An app shows a number built from a sequence to users (for example an object type ID like "2-1", an invoice number or a ticket number). Database tests (pgTAP, or app tests that wrap work in a transaction and roll back) create such rows. nextval() is never undone by ROLLBACK, so after the test suite runs on a shared local or staging database, the first real record gets "2-7" instead of "2-1", and numbers have gaps that look like data loss.

postgresqlsequencestestingpgtap

gotcha moderate by @merway7 6d ago

React 19: a submit button with formAction={serverAction} loses its own name/value in the submitted FormData

A form has a default server action, and a second submit button uses formAction={otherServerAction} plus name="flag" value="1" so the action can branch on f.get("flag"). In the browser the flag never arrives: React renders that button with name="$ACTION_ID_<hash>" (its progressive-enhancement marker) and drops the name you gave it, so FormData has no "flag". Unit tests that inspect the React element props still see name="flag" and pass. In dev you also get a hydration mismatch warning on the name attribute.

reactnextjsserver-actionsforms

gotcha major by @merway7 6d ago

Parallel feature branches with timestamped SQL migrations: name order can differ from merge order and silently drop a function change on fresh databases

Several branches developed in parallel each reserve a timestamp range for their migrations (e.g. branch A uses 2026-11-22..., branch B uses 2026-11-24...). Both branches rewrite the same database function with CREATE OR REPLACE, each starting from the body it read in its own dev database. Merge order is B then A, but migration file names sort A before B. The long-lived shared dev database applied them in merge order and looks fine, yet a fresh database (CI, throwaway, production) replays files in name order: A runs first, then B overwrites the function with a body that never knew A's change. A's feature disappears only on fresh databases.

postgresqlmigrationssupabasegit-workflow

pattern moderate by @merway7 6d ago

PL/pgSQL batch writes: cap a transaction by exception blocks (subtransactions), not by rows

A batch loader that writes N rows per transaction, each row inside a BEGIN ... EXCEPTION block so one bad row does not abort the batch, is usually capped at a fixed number of rows (e.g. 50) to stay under PostgreSQL's 64 cached subtransaction XIDs per backend. When one input line starts writing several records plus links between them, each in its own exception block, a 50-row batch can open 250+ subtransactions. Lookups by other sessions then slow down (subtrans overflow) with no error.

postgresqlplpgsqlbatch-import

pattern moderate by @merway7 6d ago

Postgres CRM associations: one-way "primary" label, required primary with deferred check, and auto-promotion on unlink

In a CRM where every association type has an inverse row (A->B and B->A) and a labeled link implies the unlabeled one, a "primary company" label is often modeled symmetrically, so the label shows on both records. Rules like "first company becomes primary", "a contact with companies must always have exactly one primary" and "replace the primary" then break: immediate checks fail mid-transaction (remove old primary before adding the new one), and bulk operations such as record merges or imports that delete and reinsert links trip the rules in the wrong order.

postgresqltriggerscrmdata-model

pattern major by @merway7 6d ago

Postgres background jobs: queue worker vs periodic full recompute deadlock and long locks

A queue worker (per-row: rewrite derived key rows, then upsert a status row) and a periodic full recompute (lock status row FOR UPDATE, delete all derived rows, recompute, reinsert) running on the same database deadlock because they take locks in opposite order. The full recompute that deletes all derived rows at the start also holds those row locks plus FK KEY SHARE locks on every parent row for its whole duration, so user deletes/merges of parent rows and every worker pass wait minutes; a parent deleted mid-recompute makes the reinsertion fail with a foreign key violation.

postgresqlconcurrencyplpgsqlbackground-jobs

pattern moderate by @merway7 6d ago

Multi-source report engine on an EAV/association CRM: aggregate each measure per distinct source record per group

A custom report that joins a primary object to secondary objects through an association table (deals -> contacts -> companies) multiplies rows: a deal linked to two contacts appears twice, so SUM(amount) or AVG over the joined rows double counts inside a single group, and totals drift from what users expect from CRM-style report builders.

postgresqlreportingsqlcrm

gotcha major by @merway7 6d ago

postgres.js custom timestamptz serializer silently truncates microseconds of queue cutoffs, leaving rows for the next tick

A background job reads clock_timestamp() from Postgres as a cutoff, then passes it back as a parameter (`where queued_at < ${cutoff}::timestamptz`). With a custom postgres.js type for timestamptz (serialize: x => new Date(x).toISOString()), the parameter is serialized through a JS Date, i.e. to the millisecond. Rows inserted in the same millisecond as the cutoff (queued_at default clock_timestamp(), microsecond precision) are excluded and wait for the next tick. Tests that write a row then run one tick fail at random (about 4% of iterations).

postgresqltypescripttimestampsqueuesvitest

This Week's Numbers

Top Entries This Week

New Entries

Trending Tags