Summary

On November 17, 2026, Sakura’s D1 read usage was found to be approaching the free allowance. The pending approval notification query scanned the messages table, including ordinary posts.

The query ran 112 times in 24 hours, reading 3,510,744 rows and returning none. Its join order was changed to fetch messages associated with approval requests by primary key. The fix is included in v0.4.1.

The confirmed impact was excessive consumption of D1’s read allowance. No limit exhaustion or service outage attributable to this incident has been confirmed.

An earlier article describes Sakura’s encrypted storage. The affected query retrieves approval metadata; it does not decrypt message bodies.

Detection and impact

Per-query statistics were checked through Cloudflare’s GraphQL Analytics API. The observation window was November 16, 2026 at 21:42:52 JST through November 17 at the same time.

MetricObserved value
Executions112
Rows read3,510,744
Rows returned0
Rows written0
Rows read per executionAbout 31,346
Average SQL execution timeAbout 85ms

This query accounted for about 98% of rows read across the query statistics retrieved in that observation.

At the time of writing, D1’s free allowance includes five million rows read per day. The measure counts scanned rows, not returned rows, as explained in Cloudflare’s pricing documentation. An empty result still consumes the allowance.

The observation window was a rolling 24 hours. The free daily allowance resets at 00:00 UTC, so these figures should not be treated as the usage of the current billing day.

Cause

The query runs under GET /web/v1/notifications. It finds rooms where the current user can approve requests, then returns up to 100 unanswered requests in descending message order.

It returns room and message identifiers, the sender handle, and timestamps, rather than request bodies or answer options.

Here is the original structure, with authorization details omitted. This is an illustrative excerpt, not executable SQL.

SELECT allowed.project_id, allowed.owner, allowed.name,
       msg.id AS message_id, msg.handle, msg.created_at
FROM (
  -- Validate the current user, key, membership, and approver role
  ...
) allowed
JOIN requests r ON r.project_id = allowed.project_id
JOIN messages msg
  ON msg.id = r.message_id AND msg.project_id = r.project_id
WHERE NOT EXISTS (
  SELECT 1 FROM decisions d
  WHERE d.project_id = r.project_id AND d.request_id = r.message_id
)
ORDER BY msg.id DESC
LIMIT 100;

requests tracks approval requests, and decisions tracks answered requests. The messages table also contains ordinary posts.

Running EXPLAIN QUERY PLAN against production D1 revealed:

SCAN msg

The request and decision lookups used indexes, but the plan scanned messages before those lookups. The plan suggests that SQLite favored traversing messages in the order needed for ORDER BY msg.id DESC LIMIT 100, then checking which ones qualified.

That can stop early if it finds 100 matches. With no matches, it cannot. LIMIT 100 limits the output, not the number of rows read. As ordinary posts accumulated, checking that there were no pending approvals became more expensive.

The roughly 31,346 rows read per execution is not a count of messages in the table. It includes the table and index access counted by D1.

Remediation

requests already had a primary key on (project_id, message_id), and decisions had one on (project_id, request_id). messages.id was an integer primary key.

The query was changed to start with authorized rooms, look up requests, and fetch the corresponding messages by primary key. The two joins became:

CROSS JOIN requests r ON r.project_id = allowed.project_id
CROSS JOIN messages msg
  ON msg.id = r.message_id AND msg.project_id = r.project_id

SQLite preserves the relative loop order of CROSS JOIN operands. This is a documented technique for manual control of join order. The ON conditions were retained.

The relevant parts of the new plan were:

SEARCH r USING COVERING INDEX sqlite_autoindex_requests_1 (project_id=?)
SEARCH d USING COVERING INDEX sqlite_autoindex_decisions_1 (project_id=? AND request_id=?)
SEARCH msg USING INTEGER PRIMARY KEY (rowid=?)
USE TEMP B-TREE FOR ORDER BY

The query now fetches individual messages by primary key and sorts the candidates afterward. A temporary sort is a better trade-off for this data than scanning all ordinary posts.

Not every scan disappeared: the plan still contains SCAN m for memberships. The fix specifically removes the scan over the full messages table.

Validation

The revised SELECT was executed once against production through the read-only D1 API, using an existing user and key with approval permission. It returned zero rows, read 78 rows, wrote none, and recorded 1.5227ms of SQL execution time.

Compared with the previous 24-hour average of about 31,346 rows, that is roughly 99.75% fewer reads. This compares a single probe with a historical average, not two full days of production traffic. Costs can vary with the number of rooms and requests. SQL execution time is also separate from browser-visible latency.

The regression test inserts 6,000 ordinary messages into an isolated local test D1 database and checks that:

  • The original SQL reads at least 6,000 rows even with no results.
  • The revised query reads fewer than 100 rows with no pending request, with a pending request, and after it is answered.
  • The plan has no SCAN msg and uses an integer primary-key lookup for messages.

The fewer-than-100 condition applies to this fixture, not every future production dataset.

The authorization predicates remain unchanged, including user and membership status, key revocation, room-specific revocation, and approval permissions. Existing tests also check that unrelated users and members without approval permission see no requests, and that answered requests disappear.

PR #63 was merged into v0.4.1. The implementation and regression test are public.

Gaps in prior validation

Existing tests checked pending request visibility and authorization-dependent results. They did not verify how many rows an empty-result query read after ordinary posts accumulated.

LIMIT 100 does not bound the number of rows scanned. Correct results needed to be supplemented with read-volume checks for empty results.

Prevention and follow-up

A regression test with 6,000 ordinary posts was added to check results, rows_read, and the query plan. It verifies that the query does not revert to scanning all messages when no approvals are pending.

Caching and notification refresh frequency were not changed. Refreshes occur on SSE events and actions such as opening the notification menu. For the observed 112 executions, reads per execution were the main source of consumption.

A full 24-hour post-fix read total has not yet been compared in this record. The 78-row result is a single production measurement and is not a constant for future user or request counts. The suitability of the pinned join order should be reassessed as room and request counts increase.