Claude Opus 5.5

What was wrong

1. The cursor only stores created_at, and the query uses <=. Every post on the next page that shares the last post's timestamp gets fetched again, so you see duplicates.
2. The order among equal timestamps is undefined. ORDER BY created_at DESC lets SQLite return tied rows in any order, and that order can differ between queries. Some tied posts fall into the gap between pages and are never shown.
3. Switching to < would not fix it. It would skip every remaining post with the boundary timestamp.

This happens more on busy days, and with the importer, because both produce many posts with the same timestamp.

The fix

The fix is keyset pagination on the pair (created_at, id):

• Sort by created_at DESC, id DESC. Since id is unique, this gives a total, stable order.
• Put both values in the cursor.
• Ask for rows that come strictly after the cursor position in that order.

import base64
import sqlite3

MAX_LIMIT = 100
COLUMNS = "id, author, body, created_at"


def _encode_cursor(created_at, post_id):
    raw = f"{int(created_at)}:{int(post_id)}".encode("ascii")
    return base64.urlsafe_b64encode(raw).decode("ascii")


def _decode_cursor(cursor):
    try:
        raw = base64.urlsafe_b64decode(cursor.encode("ascii")).decode("ascii")
        created_at, post_id = raw.split(":")
        return int(created_at), int(post_id)
    except (ValueError, UnicodeError, AttributeError) as exc:
        raise ValueError("invalid cursor") from exc


def fetch_page(conn, cursor=None, limit=20):
    """Return (rows, next_cursor): one page of the board, newest first.

    rows is a list of (id, author, body, created_at) tuples, ordered by
    created_at DESC, then id DESC for posts sharing a created_at.
    next_cursor is an opaque string the caller hands back to get the next
    page, or None when there are no more posts.

    posts(id INTEGER PRIMARY KEY, author TEXT, body TEXT, created_at INTEGER)
    created_at is unix seconds.
    """
    limit = max(1, min(int(limit), MAX_LIMIT))

    if cursor is None:
        cur = conn.execute(
            f"SELECT {COLUMNS} FROM posts "
            "ORDER BY created_at DESC, id DESC LIMIT ?",
            (limit + 1,),
        )
    else:
        last_created_at, last_id = _decode_cursor(cursor)
        cur = conn.execute(
            f"SELECT {COLUMNS} FROM posts "
            "WHERE created_at < ? OR (created_at = ? AND id < ?) "
            "ORDER BY created_at DESC, id DESC LIMIT ?",
            (last_created_at, last_created_at, last_id, limit + 1),
        )

    rows = cur.fetchall()
    has_more = len(rows) > limit
    rows = rows[:limit]

    if has_more:
        last = rows[-1]
        next_cursor = _encode_cursor(last[3], last[0])
    else:
        next_cursor = None
    return rows, next_cursor

Notes

• Index: add CREATE INDEX IF NOT EXISTS idx_posts_created_id ON posts(created_at DESC, id DESC); so each page is a quick index seek rather than a sort of the whole table.
• Cursor format: the cursor is now base64 of "created_at:id". The app still just passes it back. A malformed cursor raises ValueError instead of producing a silently wrong page.
• Old cursors: cursors issued by the old version will fail to decode. Clients will need to restart from the first page once after you deploy.
• Alternative query: on SQLite 3.15 or later you could write the condition as WHERE (created_at, id) < (?, ?). The expanded OR form above does the same thing and works on all versions.

What was wrong

The cursor is only the last post's created_at, and that value isn't unique.

• Duplicates: WHERE created_at <= cursor fetches the last post of the previous page again, along with every other post that has the same timestamp. Busy days mean more posts in the same second, so you get more repeats.
• Missing posts: if a full page or more of posts share one timestamp (easy with the importer), the next page contains only those posts. It ends on the same timestamp, so the cursor never moves. The same page keeps coming back, and nothing after it is ever reached.
• No tie-breaker: with only ORDER BY created_at, the order of tied posts is undefined. SQLite can return them in a different order on each query, so which page they land on is arbitrary.
• Changing <= to < would only swap the duplicates for skipped posts.

Fix: sort by created_at DESC, id DESC. Because ids are unique, this gives every post a fixed place. The cursor stores both values of the last row sent, and the next page starts strictly after that exact row. Every post then appears exactly once, and a large group of same-second posts can't stall the scroll.

import sqlite3

MAX_LIMIT = 100
COLUMNS = "id, author, body, created_at"


def fetch_page(conn, cursor=None, limit=20):
    """Return (rows, next_cursor): one page of the board, newest first.

    Posts with the same created_at are ordered by id, highest first.

    rows is a list of (id, author, body, created_at) tuples.
    next_cursor is an opaque string the caller hands back to get the next
    page, or None when there are no more posts.

    posts(id INTEGER PRIMARY KEY, author TEXT, body TEXT, created_at INTEGER)
    created_at is unix seconds.
    """
    limit = max(1, min(int(limit), MAX_LIMIT))

    if cursor is None:
        cur = conn.execute(
            f"SELECT {COLUMNS} FROM posts "
            "ORDER BY created_at DESC, id DESC LIMIT ?",
            (limit + 1,),
        )
    else:
        # The cursor is "created_at:id" of the last row already returned.
        # Return rows strictly after it in (created_at DESC, id DESC) order.
        last_created_at, last_id = map(int, cursor.split(":"))
        cur = conn.execute(
            f"SELECT {COLUMNS} FROM posts "
            "WHERE created_at <= ? AND (created_at < ? OR id < ?) "
            "ORDER BY created_at DESC, id DESC LIMIT ?",
            (last_created_at, last_created_at, last_id, limit + 1),
        )

    rows = cur.fetchall()
    has_more = len(rows) > limit
    rows = rows[:limit]

    next_cursor = f"{rows[-1][3]}:{rows[-1][0]}" if has_more else None
    return rows, next_cursor

Two small notes

• Old cursors: cursors handed out by the old version are a bare number. This version rejects them with ValueError, the same as any malformed cursor. Anyone mid-scroll during the deploy will get one failed "load more" and need to reload. If that matters, you could run the old created_at <= ? query for a cursor with no : for that one request.
• Performance: an index on posts(created_at) is all this query needs. SQLite stores the rowid (your id) at the end of every index entry, so it can walk that index in order without a separate sort.

Here's the fixed file:

export interface SearchOptions {
  // The API client. It returns a Promise and can't be cancelled.
  fetchResults: (query: string) => Promise<string[]>;
  // Draws the list. Pass an error as the second argument to show the error state.
  render: (results: string[], error?: Error) => void;
  // Shows or hides the spinner.
  setLoading: (loading: boolean) => void;
}

export function createSearch({ fetchResults, render, setLoading }: SearchOptions) {
  // Increments on every input. Only the input holding the current
  // value may touch the list or the spinner.
  let latestRequestId = 0;

  return {
    // Called on every keystroke with the current text of the box.
    async onInput(raw: string): Promise<void> {
      const requestId = ++latestRequestId;
      const query = raw.trim();

      if (query === "") {
        // This input is now the newest, so any request still running is stale.
        setLoading(false);
        render([]);
        return;
      }

      setLoading(true);

      let results: string[];
      try {
        results = await fetchResults(query);
      } catch (err) {
        if (requestId !== latestRequestId) return; // a newer input took over
        setLoading(false);
        render([], err instanceof Error ? err : new Error(String(err)));
        return;
      }

      if (requestId !== latestRequestId) return; // a newer input took over
      setLoading(false);
      render(results);
    },
  };
}

What was wrong

1. Stale results. Every call to onInput rendered whatever its own request returned, whenever that request finished. Requests can finish out of order. So if you typed ab and then abc, and the ab request came back last, its results overwrote the abc list. The empty-box case had the same problem: an older request finishing after the box was cleared would fill the list again.
2. Spinner turning off too early. Every call ran setLoading(false) in its finally. When any older request finished, it hid the spinner, even though the newest request was still loading.

The fix

Each call takes a number from a counter that goes up on every input, including empty ones. After the await, the call checks whether it is still the newest. If it isn't, it does nothing: it doesn't render and it doesn't change the spinner. Older requests still run to the end, since we can't cancel them, but their results are ignored.

Only the newest call can turn the spinner off. It does that right as it shows its results or error. Because emptying the box also takes a new number, it turns the spinner off, clears the list, and makes every earlier request stale.

I also moved render(results) out of the try. Before, if render itself threw, that error was caught and shown as a search error.

The file uses only type annotations, so Node's type stripping can run it without a compile step.

What was wrong

• Stale results. Every call rendered its own response whenever it arrived. The requests can't be cancelled and can finish out of order. So if the request for ca came back after the one for cat, it overwrote the list. Clearing the box had the same problem: a request still in flight would put its results back into the empty list.
• Spinner. Every call turned the spinner off in its finally. When several requests overlapped, the first one to finish hid the spinner while the newest one was still loading. Clearing the box never turned the spinner off at all, so it stayed on until the old request finished.

The fix

Every call to onInput takes a new id from a counter, including calls for an empty box. When its request finishes, a call renders and changes the spinner only if its id is still the latest. Otherwise a newer input has replaced it, so its results or error are ignored. The request still runs to the end, but nothing uses its outcome.

The empty-box path also takes an id, so a late response can't undo the clear. It now turns the spinner off as well.

The fix adds only plain JavaScript and type annotations, so it still runs with Node's built-in TypeScript support.

export interface SearchOptions {
  // The API client. It returns a Promise and can't be cancelled.
  fetchResults: (query: string) => Promise<string[]>;
  // Draws the list. Pass an error as the second argument to show the error state.
  render: (results: string[], error?: Error) => void;
  // Shows or hides the spinner.
  setLoading: (loading: boolean) => void;
}

export function createSearch({ fetchResults, render, setLoading }: SearchOptions) {
  // Every call to onInput takes a new id, including an empty input. Only the
  // call holding the latest id may update the list or the spinner. Requests
  // can't be cancelled, so older ones still run to the end, but whatever they
  // return or throw is ignored.
  let latestId = 0;

  return {
    // Called on every keystroke with the current text of the box.
    async onInput(raw: string): Promise<void> {
      const id = ++latestId;
      const query = raw.trim();

      if (query === "") {
        // Taking a new id above has already made any request in flight stale,
        // so it can't refill the list when it finishes.
        render([]);
        setLoading(false);
        return;
      }

      setLoading(true);
      try {
        const results = await fetchResults(query);
        if (id === latestId) render(results);
      } catch (err) {
        if (id === latestId) render([], err instanceof Error ? err : new Error(String(err)));
      } finally {
        // An older request finishing must not hide the spinner of a newer one.
        if (id === latestId) setLoading(false);
      }
    },
  };
}

Bugs: uses UTC date not local date; mutates d so day drifts (31→28 sticks). Fix: get local Y/M/D via Intl.DateTimeFormat, then compute pure calendar math.

// Monthly renewal dates for a subscription.
//
// signupIso  : the moment the customer signed up, as an ISO string in UTC,
//              e.g. "2025-01-31T23:30:00Z"
// timeZone   : the customer's IANA time zone, e.g. "Pacific/Auckland"
// count      : how many upcoming renewals to return
// returns    : an array of count local calendar dates, "YYYY-MM-DD", in the
//              customer's time zone, earliest first
//
// Rules:
//  - The first renewal is one month after the signup, not on the signup day.
//  - A renewal falls on the same day of the month as the signup's local date
//    in the customer's time zone.
//  - If a month is too short for that day, use its last day.
//  - Always count from the original signup day, never from the previous renewal.
export function renewalDates(signupIso, timeZone, count) {
  const signup = new Date(signupIso);
  if (Number.isNaN(signup.getTime())) {
    throw new RangeError(`Invalid signup date: ${signupIso}`);
  }

  // Signup's calendar date in the customer's time zone.
  const parts = new Intl.DateTimeFormat("en-US", {
    timeZone,
    year: "numeric",
    month: "numeric",
    day: "numeric",
  }).formatToParts(signup);
  const get = (type) => Number(parts.find((p) => p.type === type).value);
  const year = get("year");
  const month = get("month") - 1; // 0-based
  const day = get("day");

  const pad = (n, len = 2) => String(n).padStart(len, "0");
  const dates = [];

  for (let i = 1; i <= count; i++) {
    // Always offset from the original signup month, never the previous renewal.
    const total = month + i;
    const y = year + Math.floor(total / 12);
    const m = ((total % 12) + 12) % 12;
    // Day 0 of the next month = last day of month m.
    const lastDay = new Date(Date.UTC(y, m + 1, 0)).getUTCDate();
    const d = Math.min(day, lastDay);
    dates.push(`${pad(y, 4)}-${pad(m + 1)}-${pad(d)}`);
  }
  return dates;
}

What was wrong

1. Wrong time zone. It read the day with getUTCDate(), so it used the signup's UTC date instead of the customer's local date. A morning signup on the 1st in Auckland is still the previous day in UTC (e.g. the 30th or 31st), so renewals landed a day early. The fix gets the local date with Intl.DateTimeFormat in the customer's timeZone.
2. Counting from the previous renewal. It changed the same Date object on every pass and re-read the day from it. Once February clamped the 31st to the 28th, every later month started from 28. The fix keeps the original signup day and works out each renewal as signup month + i. It clamps only that month's result to the month's last day.

It also now does pure calendar arithmetic with no time-of-day involved, so DST changes can't move a date.

// Monthly renewal dates for a subscription.
//
// signupIso  : the moment the customer signed up, as an ISO string in UTC,
//              e.g. "2025-01-31T23:30:00Z"
// timeZone   : the customer's IANA time zone, e.g. "Pacific/Auckland"
// count      : how many upcoming renewals to return
// returns    : an array of count local calendar dates, "YYYY-MM-DD", in the
//              customer's time zone, earliest first
//
// Rules:
//  - The first renewal is one month after the signup, not on the signup day.
//  - A renewal falls on the same day of the month as the signup's local date
//    in the customer's time zone.
//  - If a month is too short for that day, use its last day.
//  - Always count from the original signup day, never from the previous renewal.
export function renewalDates(signupIso, timeZone, count) {
  // The signup's calendar date in the customer's own time zone. A fixed
  // locale keeps the parts as plain digits. Throws a RangeError for an
  // invalid timestamp or an unknown time zone.
  const parts = new Intl.DateTimeFormat('en-US', {
    timeZone,
    year: 'numeric',
    month: 'numeric',
    day: 'numeric',
  }).formatToParts(new Date(signupIso));
  const part = (type) => Number(parts.find((p) => p.type === type).value);
  const year = part('year');
  const monthIndex = part('month') - 1; // 0-11, as Date.UTC expects
  const day = part('day');

  const dates = [];
  for (let n = 1; n <= count; n++) {
    // Renewal n is n months after the signup month, on the signup's day.
    // It never uses the previous renewal. Date.UTC carries month indexes
    // past 11 over into the following years.
    const lastDay = new Date(Date.UTC(year, monthIndex + n + 1, 0)).getUTCDate();
    const renewal = new Date(Date.UTC(year, monthIndex + n, Math.min(day, lastDay)));
    dates.push(renewal.toISOString().slice(0, 10));
  }
  return dates;
}

What was wrong

1. timeZone was never used. The signup day came from getUTCDate(), which gives the date in UTC, not the customer's local date. Auckland is 12–13 hours ahead of UTC. A signup there on the morning of the 1st is still the last day of the previous month in UTC, so the code used that day. The fix reads the signup's local date in the customer's zone with Intl.DateTimeFormat. The same bug works the other way west of UTC: an evening signup there is already the next day in UTC, so those customers have been renewed late. Customers rarely complain about that, so it won't show up in tickets.
2. Each renewal was based on the previous one. day was read again from d on every pass of the loop. By then d held the previous renewal, already cut down to fit its month. Once February turned the 31st into the 28th, every later month got the 28th as well. Now every renewal is worked out from the original signup: renewal n is the signup month plus n, on the signup day, or on the last day of a shorter month.

One thing to check: the old code ignored timeZone, so a caller that never passed it would have gone unnoticed. If it arrives as undefined, Intl quietly uses the server's own time zone instead.

Must fix before merging

1. Any logged-in user can read any invoice (IDOR). GET /invoices/:id calls findInvoiceById(id) with no account check, so anyone with a valid session can fetch another account's invoice by guessing or enumerating IDs. Scope the query to the account:

export async function findInvoiceById(id: string, accountId: string) {
  const { rows } = await db.query(
    `SELECT ${COLUMNS} FROM invoices WHERE id = $1 AND account_id = $2`,
    [id, accountId],
  );
  return rows[0] ?? null;
}

Keep returning 404 rather than 403 when the account doesn't match, so the endpoint doesn't reveal which IDs exist.

2. to is not inclusive, despite what the code comment and docs say. created_at < $3 with to=2026-01-31 compares against midnight at the start of the 31st, so that whole day is excluded. Use created_at < ($3::date + 1) or add one day in the route.

3. Invalid dates cause 500s. The regex accepts values like 2026-02-30 and 2026-13-99. Postgres rejects them, and the handler surfaces that as an error instead of a 400. Check that the value is a real calendar date before querying.

4. Async errors may not be handled. If you're on Express 4, a rejected promise in these handlers (DB down, bad input) is never passed to your error middleware. The request hangs and you get an unhandled rejection. Wrap the handlers, or follow whatever pattern receipts.ts uses. Express 5 handles this for you.

Worth checking

• The fmtMoney → formatMoney rename is unrelated to invoices and breaks any caller this diff doesn't touch. Grep for fmtMoney (for example in account.ts) and confirm tsc passes. Consider splitting the rename into its own PR.
• Time zones: "day" boundaries depend on the column type (timestamp vs timestamptz) and the session time zone. Decide whose midnight counts (UTC or the account's) and document it.
• No pagination or range limit: a very wide from/to returns everything in one response. Consider adding limit/cursor support or capping the range.
• Unstable ordering: sorting by amount or created_at alone gives an arbitrary order for ties. Add , id as a tiebreaker, which you'll need anyway for pagination.
• Silent fallbacks: an invalid sort/dir quietly uses the defaults, and from > to silently returns an empty list. Returning 400 would be friendlier, but this is your call.
• formatMoney assumes two decimal places, which is wrong for currencies like JPY or KWD. This predates the PR but now affects more endpoints.

Looks good

• The sort whitelist using Object.hasOwn and the hard-coded direction is safe from SQL injection.
• The date and account filters are properly parameterized.
• requireLogin covers the whole router.
• The docs match the intended behaviour, once fix 2 makes the code match them.

Must fix before merging

1. GET /invoices/:id exposes other accounts' invoices. findInvoiceById looks up by id only, so any logged-in user can read any invoice whose id they know or guess. Scope the query with WHERE id = $1 AND account_id = $2, passing req.user.accountId. Another account's invoice then gets the same 404 as a missing one, so nothing leaks. Also make accountId a required parameter of the repo function so this can't recur.
2. to isn't inclusive. The docs and route comment say it is, but created_at < $3 with a bare day excludes the whole last day. to=2026-01-31 drops Jan 31, and a single-day query (from = to) always returns nothing. Use created_at < $3::date + 1.
3. Bad input causes uncaught database errors. The regex accepts impossible dates like 2026-02-30 or 2026-13-01, which Postgres rejects. A malformed :id fails the same way if id is a uuid column. Nothing catches these errors:
   • Express 4 without an async wrapper: the rejection is unhandled, so the request hangs and the process can crash on current Node. Any logged-in user can trigger this.
   • Express 5: the user gets a 500 instead of a 400.
   Fix it in three places: check that dates are real (e.g. the string round-trips through Date), check the id format, and make sure async errors reach your error handler.

Also worth knowing

• The fmtMoney → formatMoney rename only updates receipts.ts. Search for other callers (e.g. the account routes) before merging. This unrelated refactor could also go in its own PR.
• There's no limit or pagination, so a wide date range returns everything.
• If created_at is timestamptz, day boundaries follow the database session's time zone. Decide what a "day" means (probably UTC) and document it.
• sort=amount compares raw cents across currencies, which is meaningless if an account can have more than one. Also add id as a tiebreaker so the order is stable.
• Unknown sort/dir values silently fall back to the defaults. Document that, plus that from/to are required, plus the 400/404 responses.
• created_at is typed string, but node-postgres returns timestamps as Date unless you've changed its type parsers.
• Add tests for the cross-account 404, the inclusive to boundary and invalid dates.

What's fine: the sort/direction whitelist is safe from SQL injection, and the list query is correctly scoped to the account.

Query

WITH ordered AS (
  SELECT
    sp.customer_id,
    sp.start_date,
    -- Latest access day covered by any EARLIER period of this customer.
    -- NULL end_date (still active) is treated as "covers everything after".
    MAX(COALESCE(sp.end_date, '9999-12-31')) OVER (
      PARTITION BY sp.customer_id
      ORDER BY sp.start_date, sp.id
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_covered_until
  FROM subscription_periods AS sp
)
SELECT
  c.name                                         AS name,
  o.prev_covered_until                           AS last_access,
  o.start_date                                   AS came_back,
  CAST(julianday(o.start_date)
       - julianday(o.prev_covered_until) - 1 AS INTEGER) AS days_without
FROM ordered AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.start_date > date(o.prev_covered_until, '+1 day')
ORDER BY c.name, came_back, c.id;

How it works

1. Sort each customer's periods by date. The window orders by start_date, so the order of the ids doesn't matter. id is only used to break ties between periods that start on the same day.
2. Track how far access already reaches. For each period, prev_covered_until is the latest end_date among all periods that started before it. Using a running MAX instead of just the previous row's end date matters in two cases:
   • A short period that sits inside a longer one doesn't hide the longer one's end date.
   • Overlapping periods are handled correctly.
   An active period (NULL end date) counts as '9999-12-31'. Because of that, nothing that comes after an active period can ever look like a gap.
3. Find the gaps. A period starts a gap only if it begins after the day following prev_covered_until. Periods that touch, start on the same day, or overlap all fail that test, so they aren't reported.
   • A customer's first period has no earlier periods, so prev_covered_until is NULL. The comparison is then NULL and the row is excluded.
   • A customer who left and never came back has no later period, so they produce no row.
4. Count the days. julianday(came_back) - julianday(last_access) - 1 gives the number of whole days without access. For 2024-06-30 → 2024-07-10 that is 9.
5. Sort. ORDER BY name uses SQLite's default BINARY collation, which is plain alphabetical order. After that, rows are sorted by came_back, then by customer id.

On your sample data, the query returns exactly the three expected rows. Bruno's overlapping periods (02-01 to 02-29, then 02-20 onward) don't count as a gap.

WITH runs AS (
  SELECT
    customer_id,
    start_date,
    -- latest day with access among all periods that started before this one
    MAX(COALESCE(end_date, '9999-12-31')) OVER (
      PARTITION BY customer_id
      ORDER BY start_date, end_date, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS covered_until
  FROM subscription_periods
  WHERE end_date IS NULL OR end_date >= start_date
)
SELECT
  c.name,
  r.covered_until AS last_access,
  r.start_date    AS came_back,
  CAST(julianday(r.start_date) - julianday(r.covered_until) AS INTEGER) - 1 AS days_without
FROM runs AS r
JOIN customers AS c ON c.id = r.customer_id
WHERE julianday(r.start_date) - julianday(r.covered_until) > 1
ORDER BY c.name, r.start_date, c.id;

On your sample data this returns exactly the three rows you listed.

How it works

• It tracks coverage so far, not the previous row. Each customer's periods are sorted by start_date, and the ids are only used to break ties. For each period, covered_until is the latest last-access day of any period that started earlier. It is a running MAX over the preceding rows. A plain LAG(end_date) would fail when a short period sits inside a longer one. The running max handles touching, same-day, overlapping and nested periods the same way.
• Active periods count as never ending. A NULL end date is treated as '9999-12-31', so no gap can appear after an active period.
• A gap is a period that starts more than one day after covered_until. last_access is covered_until and came_back is the new start date. days_without is the difference in days minus one: 06-30 to 07-10 is 10 days apart, so 9 days without access. Touching periods give 0 and overlapping ones give a negative number, so both are filtered out. The CAST makes the result an integer rather than 9.0.
• No rows appear before the first period or after the last one. A customer's first period has nothing before it, so covered_until is NULL and the row is dropped. Gaps are only found when a later period starts, so a customer who left for good gets no row for that final departure.
• Two details are defensive. Two periods starting on the same day are harmless, because the second one always sees the first one's coverage. The WHERE end_date >= start_date filter drops malformed periods that end before they start, since they never gave any access.

ORDER BY c.name uses SQLite's default byte-order comparison, which is case-sensitive. If some names start with a lowercase letter, use c.name COLLATE NOCASE instead.

import csv
import io

_NAME_HEADERS = {"name", "full name", "contact name"}
_EMAIL_HEADERS = {"email", "e-mail", "email address", "e-mail address"}
_PHONE_HEADERS = {"phone", "phone number", "mobile", "mobile phone", "telephone"}


def _decode(data):
    if data.startswith(b"\xef\xbb\xbf"):
        data = data[3:]
    try:
        return data.decode("utf-8")
    except UnicodeDecodeError:
        # Old Windows tool. A few bytes are undefined in cp1252, so replace them.
        return data.decode("cp1252", errors="replace")


def _detect_delimiter(text):
    # Count separators outside quotes on the first non-blank logical line (the header).
    commas = semicolons = 0
    in_quotes = False
    seen_content = False
    for ch in text:
        if ch == '"':
            in_quotes = not in_quotes
        elif not in_quotes:
            if ch == "\n":
                if seen_content:
                    break
                continue
            if ch == ",":
                commas += 1
            elif ch == ";":
                semicolons += 1
            if not ch.isspace():
                seen_content = True
    return ";" if semicolons > commas else ","


def _norm_header(h):
    return " ".join(h.strip().lower().split())


def import_contacts(path):
    with open(path, "rb") as f:
        text = _decode(f.read())

    # Normalise line endings so line breaks inside quoted fields become "\n".
    text = text.replace("\r\n", "\n").replace("\r", "\n")
    if not text.strip():
        return []

    reader = csv.reader(io.StringIO(text, newline=""),
                        delimiter=_detect_delimiter(text))

    # The header is the first row that is not blank.
    header = None
    for row in reader:
        if any(cell.strip() for cell in row):
            header = row
            break
    if header is None:
        return []

    name_idx = email_idx = phone_idx = None
    for i, h in enumerate(header):
        key = _norm_header(h)
        if key in _NAME_HEADERS and name_idx is None:
            name_idx = i
        elif key in _EMAIL_HEADERS and email_idx is None:
            email_idx = i
        elif key in _PHONE_HEADERS and phone_idx is None:
            phone_idx = i
    if email_idx is None:
        return []

    def cell(row, idx):
        if idx is None or idx >= len(row):
            return ""
        return row[idx].strip()

    contacts = []
    seen = set()
    for row in reader:
        email = cell(row, email_idx).lower()
        if not email or email in seen:
            continue
        seen.add(email)
        contacts.append({
            "name": cell(row, name_idx),
            "email": email,
            "phone": cell(row, phone_idx),
        })
    return contacts

How it works

• Decoding: The function reads the file as bytes and removes a UTF-8 BOM if there is one. It then decodes the text as UTF-8 and falls back to Windows-1252 if that fails.
• Line endings: All line endings are turned into \n, so line breaks inside quoted fields come out as a single \n.
• Separator: It counts commas and semicolons in the header line, ignoring any inside quotes, and uses whichever appears more often.
• Columns: csv.reader handles quoting, doubled quotes and multi-line fields. The header names are lower-cased and their spacing is normalised, then matched against the accepted names to find each column's position.
• Rows: Each data row is read by position, so short rows give empty strings and extra cells are ignored. Rows with no email, including blank rows and rows of only separators, are skipped. A set of emails already seen drops duplicates and keeps the first one, in file order.
• Missing email column: If the file has no email column, the function returns an empty list.

The old function was slow because of a nested loop. For each of the 200,000 payments it scanned all 200,000 invoices, which is about 40 billion checks. The new version builds an index once and answers each payment with a short lookup.

from bisect import bisect_left
from collections import defaultdict


def match_payments(payments, invoices):
    """Match bank payments to open invoices.

    payments: list of dicts with id, customer, amount (integer cents) and date (datetime.date).
    invoices: list of dicts with id, customer, amount (integer cents) and due (datetime.date).

    Payments are handled one at a time, in the order they are given. A payment is
    matched to the unpaid invoice that has the same customer and the same amount,
    whose due date is within 7 days either side of the payment date (7 days
    apart still counts). If several invoices qualify, the one with the earliest
    due date wins; if they are due on the same day, the lowest invoice id wins.
    An invoice can be matched to one payment only.

    Returns (matches, unmatched):
      matches   - list of (payment_id, invoice_id), in payment order
      unmatched - list of ids of the payments that found no invoice, in payment order
    """
    # Group invoices by (customer, amount). The input lists are never modified.
    grouped = defaultdict(list)
    for inv in invoices:
        grouped[(inv["customer"], inv["amount"])].append((inv["due"], inv["id"]))

    # Within each group, sort by (due, id): the first eligible entry is the winner.
    # Each group holds: due ordinals (for bisect), ids, and a "next available"
    # pointer array used to skip over invoices that are already taken.
    index = {}
    for key, items in grouped.items():
        items.sort(key=lambda t: (t[0], t[1]))
        dues = [d.toordinal() for d, _ in items]
        ids = [i for _, i in items]
        nxt = list(range(len(items) + 1))  # nxt[len] is a sentinel
        index[key] = (dues, ids, nxt)

    def find(nxt, i):
        # Next index >= i that has not been removed (with path compression).
        root = i
        while nxt[root] != root:
            root = nxt[root]
        while nxt[i] != root:
            nxt[i], i = root, nxt[i]
        return root

    used = set()
    matches = []
    unmatched = []

    for pay in payments:
        group = index.get((pay["customer"], pay["amount"]))
        best_id = None
        found = False
        if group is not None:
            dues, ids, nxt = group
            n = len(dues)
            day = pay["date"].toordinal()
            hi = day + 7
            i = find(nxt, bisect_left(dues, day - 7))
            while i < n and dues[i] <= hi:
                inv_id = ids[i]
                if inv_id in used:
                    # Already used (possibly via a duplicate id elsewhere):
                    # remove it permanently and move on.
                    nxt[i] = i + 1
                    i = find(nxt, i + 1)
                    continue
                best_id = inv_id
                found = True
                nxt[i] = i + 1  # remove this invoice from the group
                break
        if not found:
            unmatched.append(pay["id"])
        else:
            used.add(best_id)
            matches.append((pay["id"], best_id))

    return matches, unmatched

What changed

1. Grouping. Invoices are grouped once by (customer, amount). A payment only ever looks at invoices that could match it, instead of all 200,000.
2. Sorting plus binary search. Each group is sorted by (due, id), which is exactly the old tie-break order. A binary search jumps straight to the first invoice due on or after payment date − 7. Scanning forward from there, the first available invoice whose due date is at most payment date + 7 is the one the old code would have picked.
3. Skipping used invoices. Your large customer with over a thousand same-amount invoices would otherwise make each payment walk past every invoice already used. To avoid that, a used invoice is removed with a "next available" pointer array, compressed as it is followed. Each invoice is skipped at most a handful of times in total, not once per payment.

Matching the old behaviour exactly

• Payment order. Payments are still processed in the order given, so unsorted bank order gives the same results.
• Used invoices. "Used" is still tracked by invoice id in one global set, as before. If the same id appears more than once, even under a different customer or amount, it becomes unavailable everywhere once matched.
• The 7-day limit. It is inclusive on both sides, as before. Day ordinals are used, which gives the same result as (due - date).days for datetime.date values.
• Your two lists. payments and invoices are only read, never modified.

Speed

Building the index takes O(N log N) time, and each payment costs about O(log N) on top of that. 200,000 × 200,000 should run in roughly a second.

Online Booking for a One-Person Dog Groomer: The Plan

First, consider buying instead of building

Square Appointments, Acuity, Fresha and similar tools already handle deposits, reminders and blocked days, often for little or nothing at one-staff scale. Spend an hour checking whether one covers the large-dog rule. Usually it can, as a separate "large dog" version of each service.

If you build anyway (control, learning, no monthly fee), here's the plan.

Stack

Choose boring, well-documented tools you already know.

• Backend: Django with PostgreSQL. You get auth, an ORM, migrations and an admin for free. Postgres matters here because it can prevent double-bookings at the database level.
• Frontend: Server-rendered Django templates with a little JavaScript (or htmx). There are two views:
  • a public booking flow,
  • a mobile-first dashboard for your sister.
  A SPA isn't worth it at this size.
• Payments: Stripe Checkout for the deposit, Stripe webhooks to confirm payment, and the Stripe Refunds API for cancellations.
• Email: Postmark or Resend. SMS through Twilio is optional, but reminders by text get read far more often.
• Scheduled jobs: A cron job (the host's scheduler, or a management command every 15 minutes) for reminders and for expiring unpaid holds. You don't need Celery.
• Hosting: Render, Railway or Fly.io with managed Postgres. Turn on automated backups.
• Time handling: Python's zoneinfo, with her IANA zone (for example Europe/London) stored in settings.

Data model

Service

• name, base_minutes (30/60/90), price, active

Booking

• service (FK)
• dog_size (standard/large)
• duration_minutes: snapshot at booking time, base or base × 2
• price and deposit_amount: snapshots, so later price changes don't rewrite history
• start, end: timezone-aware, stored in UTC
• client_name, email, phone, dog_name, breed, notes
• status: pending_payment → confirmed → completed / cancelled / no_show, plus expired
• stripe_checkout_session_id, stripe_payment_intent_id, refund_id
• cancel_token: a random secret for the "manage my booking" link, so clients don't need accounts
• hold_expires_at, reminder_sent_at, cancelled_at, created_at

WorkingHours

• weekday, open_time, close_time
• Store breaks as separate rows (Tue 9:00–12:30, Tue 13:30–17:00). Lunch then needs no special case, and she can change hours later without a code change.

BlockedPeriod

• start, end, reason. A full day is just a range covering it.

Settings (a single row or config)

• timezone, booking horizon (42 days), deposit percent (20), refund cutoff (24h), slot granularity (for example 30 minutes), optional buffer between appointments

Preventing double-bookings

Add a Postgres exclusion constraint on tstzrange(start, end) for bookings whose status is pending_payment or confirmed. Two people clicking the same slot at the same moment then can't both win, whatever your Python code does.

Core logic: availability

Write this as a pure function, available_starts(date, duration), and test it thoroughly before building any UI.

1. If the date is in the past, beyond 6 weeks, or not Tuesday to Saturday, return nothing.
2. Build the working intervals for that date in local time, then convert them to aware datetimes.
3. Subtract blocked periods and active bookings.
4. Return each start time, on the slot grid, where start + duration fits entirely inside one free interval. An appointment must never span lunch.

A large-dog 90-minute groom takes 180 minutes. It fits in the morning (3.5h) or the afternoon (3.5h), but only at a few start times. That's correct, but she should know.

Phases

Phase 0: Decisions with your sister (one evening). Get answers to:

• Does a large dog also cost double? This affects the deposit.
• What counts as "large"? Pick a weight threshold the client declares, and decide what she does if someone under-declares.
• Does she want buffer time between dogs?
• What slot grid should start times use?
• What time should reminders go out?
• Can clients reschedule, or only cancel and rebook?
• What's the no-show policy?
• What does she want written in the policy text clients agree to?

Phase 1: Availability engine plus tests. Cover these cases:

• lunch boundaries,
• 180-minute bookings,
• blocked days,
• the 6-week edge,
• both DST transition dates.

Phase 2: Booking without payment, plus her dashboard. The dashboard needs:

• a today/week list,
• booking details with a tap-to-call phone link,
• a "block time" form,
• a new-booking notification to her (email or push).

Run this privately with fake bookings so she can try it on her phone.

Phase 3: Deposits. Use this flow:

1. Create the booking as pending_payment with a 15-minute hold.
2. Redirect to Stripe Checkout.
3. Confirm only when the checkout.session.completed webhook arrives. Never rely on the success-page redirect.
4. A cron job expires stale holds.
5. Make the webhook handler idempotent, because Stripe retries.

Phase 4: Cancellation and refunds. Use the token link and compare now against start - 24h in absolute time.

• More than 24h ahead: refund through the API and record the refund ID.
• Within 24h: cancel and keep the deposit.

She also needs a "cancel and refund anyway" button for goodwill cases.

Phase 5: Reminders. The cron job finds confirmed bookings for tomorrow (local date) with no reminder_sent_at, sends the reminder, then sets the field. Setting the field makes the job safe to re-run.

Phase 6: Polish and soft launch. Add:

• confirmation emails with an .ics attachment,
• a privacy notice,
• error monitoring (Sentry),
• a backup restore test.

Launch to a handful of regular clients first.

What could go wrong

Time zones and DST

• Store UTC and compute availability in local time. Never add timedelta(days=1) to an aware datetime to get "tomorrow 9:00". Build it from the local date and time instead.
• The DST changes fall on Sundays and Mondays, when she's closed, which helps. But "24 hours before" a Tuesday booking after a change is not the same as "same time Monday". Decide which one the policy means, and write the policy to match.
• Server cron runs in UTC. A fixed UTC reminder time drifts by an hour twice a year unless you compute it from local time.

Payments

• Race between payment and expiry. A client pays at minute 16 after their hold has expired and someone else took the slot. Detect this in the webhook and refund automatically.
• Stripe fees aren't returned on refunds. She loses a little on every refunded cancellation. She should know this up front.
• Chargebacks on kept deposits. A clear policy checkbox at booking time is her evidence.
• Test mode vs live keys. Check webhook secrets separately in each environment.

Business rules

• Blocking a day that already has bookings. The block form must show the conflicts and offer "cancel and refund all", not hide them silently.
• Bookings made less than 24h ahead are non-refundable from the moment they're made. Show that clearly before payment.
• Bookings made after the reminder run for tomorrow either get no reminder or need one sent immediately. Pick one.
• Large-dog honesty. A self-declared size can be gamed, and a mistake wrecks her afternoon. Let her change the size and duration from the dashboard.
• Changing hours or prices later. The snapshots protect existing bookings, but new working hours could leave old bookings "outside hours". Flag them; don't delete them.

Operations

• She's running it from her phone, so every admin action must work one-handed on a small screen. Test on her actual phone.
• Single point of failure: you. Write a one-page "what to do if it breaks" note. Keep a manual fallback: she can still take phone bookings and enter them in the dashboard.
• Spam and bot bookings. Pending holds expire, but add rate limiting or a CAPTCHA on the booking form.
• Personal data (names, phones, emails). Use HTTPS, a short privacy notice, and minimal retention. Check local data-protection rules.
• Email deliverability. Set SPF/DKIM on her domain, or reminders land in spam.

Rough timeline

At weekend pace, this is about 6–8 weekends to a soft launch:

• Phases 1–2: about three weekends
• Payments and refunds: about two weekends
• Reminders, polish and testing: one to two weekends

Don't split into microservices now. Neither of your real problems comes from the monolith, both can be fixed in days or weeks, and a six-service Kubernetes migration would take your whole two-person team for months while making deploys and debugging harder.

Here's why:

The report timeout is a query and data problem, not an architecture problem. Moving the report into its own service would give it the same slow queries plus network hops. The likely fixes:

• Profile the report for the three big customers. Look for N+1 queries, missing indexes and unbounded loops in Python.
• Add select_related/prefetch_related, push aggregation into SQL, and add indexes where EXPLAIN ANALYZE points.
• Precompute monthly reports with a scheduled background job (Celery, or a cron plus a management command) or a materialized view. Then serve the stored result or email a link.

The 20-minute deploys are a pipeline problem. Find out where the time goes. It's usually some mix of:

• Uncached dependency installs or Docker layers
• A test suite running serially, which you can parallelize with pytest -n
• Slow migrations or static asset builds

Six services means six pipelines, versioned APIs between them, distributed tracing, and a Kubernetes cluster for someone to run. With two engineers, that someone is you two, instead of building product.

On "that's how you scale": 200 business customers is a small load for one well-tuned Django and Postgres setup. Shopify, GitHub and Basecamp ran large monoliths for years. Microservices mainly solve an organizational problem, letting many teams deploy independently. You don't have that problem.

On the funding round: investors fund traction and speed. "We fixed our biggest customers' pain in two weeks and ship daily" is a better story than "we spent the quarter on infrastructure." Good technical diligence will see six services run by two people as a risk, not a strength.

What I'd do instead:

1. Fix the report this week, starting with profiling and then precomputing.
2. Get deploys under five minutes.
3. Keep the monolith modular, with clean app boundaries and no cross-app reaching into models. That way, if you ever do need to extract a service, the seams are already there.

Revisit the question when you have several teams stepping on each other, or one component with clearly different scaling needs.

Don't split into microservices now. It would tie up both of you for months right before the funding round and fix neither of your real problems. Both the slow deploys and the report timeouts should be fixable inside the monolith in a few weeks.

Why microservices won't help

• The timeout hits only your three biggest customers. That points to queries that slow down as data grows. A separate service would run the same queries on the same data, plus network hops.
• Smaller services might deploy faster. Fixing the current pipeline gets you there without six pipelines, versioned APIs between services, and data split across databases.
• Microservices mainly help teams scale, not traffic, by letting many teams ship independently. With two engineers you'd pay the costs (Kubernetes alone is a part-time job) without the benefit.
• 200 business customers is a modest load for Django and Postgres. Instagram runs a Django monolith at vastly larger scale.

Fix the reports (1–2 weeks)

1. Profile one big customer's report with EXPLAIN ANALYZE and Django Debug Toolbar. The usual causes are missing indexes, N+1 queries (fix with select_related/prefetch_related), or aggregating in Python instead of SQL.
2. Generate reports in a background job (e.g. Celery) and notify the customer when the report is ready, so no web request times out.
3. Completed months don't change, so build each report once and store it.

Fix deploys (about a week)

1. Time each stage to see where the 20 minutes goes. It's usually tests, image builds or static assets.
2. Run tests in parallel (pytest-xdist or manage.py test --parallel), and cache dependencies and build layers. Getting under five minutes is often achievable.

On the funding round Investors back revenue, growth and retention, not architecture, and a Django monolith is normal at your stage. "We fixed our biggest customers' problems in weeks" is a better story than "we spent months rewriting."

When to revisit: keep clean boundaries between Django apps now. Pull a piece out into its own service only when a real need appears, such as a separate team or a component that needs to scale very differently.

Zero-downtime rename: users.username → users.handle

You can't use a plain ALTER TABLE ... RENAME COLUMN. The rename itself is instant, but every deployed reader and writer that uses the old name breaks at that moment. Because three services deploy separately, use expand → migrate → contract: run both columns side by side, keep them in sync with a trigger, move each service across, then remove the old column.

Treat the database column and the JSON field as two separate migrations. The JSON username field must stay for at least three months. The column can be gone long before that.

General rule for every DDL step: run it with a short lock timeout and retry on failure. A DDL statement waiting behind a long transaction will block all traffic queued behind it.

SET lock_timeout = '2s';
SET statement_timeout = 0;  -- per session, for the long-running steps

Phase 1: Expand (database only, no app changes)

1. Add the new column (nullable, no default)

ALTER TABLE users ADD COLUMN handle text;

This is a metadata-only change and takes milliseconds.

• Rollback: ALTER TABLE users DROP COLUMN handle;

2. Add a sync trigger that works in both directions

Any write to either column is mirrored to the other. This lets old and new code run at the same time in any order.

CREATE OR REPLACE FUNCTION users_sync_handle() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.handle   := COALESCE(NEW.handle, NEW.username);
    NEW.username := COALESCE(NEW.username, NEW.handle);
  ELSIF NEW.username IS DISTINCT FROM OLD.username
        AND NEW.handle IS NOT DISTINCT FROM OLD.handle THEN
    NEW.handle := NEW.username;          -- old code wrote username
  ELSIF NEW.handle IS DISTINCT FROM OLD.handle
        AND NEW.username IS NOT DISTINCT FROM OLD.username THEN
    NEW.username := NEW.handle;          -- new code wrote handle
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER users_sync_handle
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION users_sync_handle();

NOT NULL is checked after BEFORE triggers run. So once new code inserts only handle, the username NOT NULL constraint is still satisfied.

• Rollback:

  DROP TRIGGER users_sync_handle ON users;
  DROP FUNCTION users_sync_handle();

3. Backfill in batches

Run this from a script. Each batch is its own transaction, with a short sleep between batches. Watch replication lag and autovacuum while it runs.

UPDATE users
SET    handle = username
WHERE  id >= $1 AND id < $2      -- e.g. 10k–50k ids per batch
AND    handle IS NULL;

Repeat until this returns 0:

SELECT count(*) FROM users WHERE handle IS NULL;

• Rollback: nothing to undo. The data is only a copy. Stop the script, or drop the column as in step 1.

4. Add the unique index without blocking writes

CREATE UNIQUE INDEX CONCURRENTLY users_handle_key ON users (handle);

If it fails, it leaves an INVALID index behind. Drop that index and retry.

If your existing uniqueness is a constraint rather than just an index (check with \d users), recreate it the same way:

ALTER TABLE users ADD CONSTRAINT users_handle_key UNIQUE USING INDEX users_handle_key;

• Rollback: DROP INDEX CONCURRENTLY users_handle_key;

5. Make handle NOT NULL without a long lock

Validating the check scans the table while holding only a light lock. On Postgres 12 and later, SET NOT NULL then uses the validated check and skips its own full scan.

ALTER TABLE users ADD CONSTRAINT users_handle_not_null
  CHECK (handle IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_handle_not_null;
ALTER TABLE users ALTER COLUMN handle SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_handle_not_null;

• Rollback: ALTER TABLE users ALTER COLUMN handle DROP NOT NULL;

At this point both columns are always identical, unique and NOT NULL.

Phase 2: Migrate the applications

6. Move the two read-only services to handle

Deploy them independently, in any order. They are safe because the trigger keeps both columns identical.

• Rollback: redeploy the previous version, which reads username. It still works.

7. Move the main API's reads to handle, and change its JSON output

• Read from handle.
• Return both fields in the JSON, with the same value:

  { "username": "alice", "handle": "alice" }

• If clients send username in request bodies, accept both, and prefer handle when both are present.

Release the mobile app version that uses handle. Its three-month clock starts now.

• Rollback: redeploy the previous API version.

8. Move the main API's writes to handle only

Stop writing username. The trigger fills it in, so any old instance or reader still using username keeps seeing correct data.

• Rollback: redeploy the previous version. The trigger handles both write paths, so either version is correct.

9. Verify that nothing touches username any more

• Search all three codebases, including raw SQL, ORM models, reports, ETL jobs, BI tools and ad-hoc scripts.
• Check pg_stat_statements for queries that mention username:

  SELECT query FROM pg_stat_statements WHERE query ILIKE '%username%';

• Run SELECT pg_stat_statements_reset();, wait a week or so, then check again.
• Make sure no service uses SELECT * with cached prepared statements or ORM column caches. Dropping a column can cause cached plan must not change result type errors until the connection pools recycle.
• Rollback: this step changes nothing, so there is nothing to undo.

Phase 3: Contract

10. Drop the trigger

From here on, username is no longer kept up to date. Do this only after step 9 is clean.

DROP TRIGGER users_sync_handle ON users;
DROP FUNCTION users_sync_handle();

Before dropping, relax the old column's constraints so nothing can fail on it:

ALTER TABLE users ALTER COLUMN username DROP NOT NULL;
DROP INDEX CONCURRENTLY users_username_key;   -- use your actual index name

• Rollback:
  1. Recreate the trigger from step 2.
  2. Re-sync: UPDATE users SET username = handle WHERE username IS DISTINCT FROM handle; (batched, as in step 3).
  3. Rebuild the unique index on username concurrently.
  4. Restore NOT NULL on username using the method from step 5.

11. Drop the old column (the point of no return)

Wait as long as you're comfortable, for example a couple of weeks after step 10.

SET lock_timeout = '2s';
ALTER TABLE users DROP COLUMN username;

This is instant and metadata-only. The space is reclaimed gradually as rows are rewritten.

• Rollback: there is no cheap one. You would have to repeat steps 1–5 in reverse, copying handle back into a new username column. Take a backup or snapshot first. You can also postpone this step indefinitely, because an unused nullable column costs very little.

12. Remove username from the JSON

Do this after the three-month window, once your analytics show negligible traffic from old app versions. Ideally, log requests by app version before you remove it.

• Rollback: redeploy the API version that returns both fields.

Summary of safe states

Everything up to step 10 can be rolled back by redeploying code or dropping what you added. Step 11 is the only step that is truly irreversible.

Team Invites v1: Spec

Your brief contradicts itself in three places. I've written the spec with placeholders for those, so please resolve them before handing it over, or the developer will have to guess.

⚠️ Decisions needed before build

Notes to help you decide:

• D1: If any member can invite, the "client data" risk returns. A middle option is that members can invite but an admin must approve. That adds scope, though, and may not fit in three weeks.
• D2: A 30-day expiry with resend still available is a common compromise. Agree whether resending resets the clock (see Q5).
• D3: This also affects billing and upgrade messaging, so check with whoever owns pricing.

1. Overview

Goal: Let workspace users invite teammates themselves, so support no longer adds people by hand. Why now: Support loses hours each week on manual adds, and this is the top request on the feedback board. Launch target: End of next month. Developer capacity is about 3 weeks.

In scope (v1):

• Sending invites
• Pending state, resend, and revoke
• Accepting invites (new and existing accounts)
• Seat limits
• Default role
• Audit trail
• Analytics events

Out of scope (v1):

• The "who can invite" workspace setting
• Bulk or CSV invites
• Invite approval flows
• Custom email content
• SSO or domain auto-join
• Changes to seat purchasing in Billing (assumed to already exist; see Q10)

2. User stories and acceptance criteria

US1: Send an invite

As a [INVITE_PERMISSION] user, I want to invite a teammate by email so they can join my workspace without contacting support.

• AC1.1 Settings > Team has an "Invite" action with an email field. It is visible only to [INVITE_PERMISSION] users and hidden or disabled for everyone else. The API also enforces this permission server-side.
• AC1.2 The email address is validated for format. Invalid addresses show an inline error and no invite is sent.
• AC1.3 If the email belongs to an existing workspace member, an error is shown: "Already a member."
• AC1.4 If the email already has a pending invite, the user is told so and offered Resend. No duplicate invite is created.
• AC1.5 If the workspace has no available seats, the invite is blocked:
  • Free plan: an upgrade prompt is shown.
  • Paid plan: a prompt to buy seats in Settings > Billing is shown.
• AC1.6 On success, an email is sent:
  • From: no-reply
  • Subject: You've been invited to <workspace name>
  • Content: a unique, single-use invite link
• AC1.7 The invitee appears in the team list with status Pending, the invite date, and who invited them.
• AC1.8 An audit entry and the invite_sent analytics event are recorded.

US2: Resend an invite

As an inviter, I want to resend a pending invite in case the email was missed.

• AC2.1 Pending rows have a Resend action.
• AC2.2 Resend is limited to once per hour per invite. During the cooldown the button is disabled and shows when it becomes available again. The API also enforces this limit.
• AC2.3 Resend sends the same email again. Whether it extends the expiry is to be confirmed (Q5).
• AC2.4 The resend is recorded in the audit trail.

US3: Revoke an invite

As an inviter, I want to revoke a pending invite so the link can no longer be used.

• AC3.1 Pending rows have a Revoke action with a confirmation step.
• AC3.2 After revoking, the link stops working immediately and the row is removed from the list (or marked Revoked; see Q8).
• AC3.3 A revoked invite frees its seat, if pending invites hold seats (Q2).
• AC3.4 The revocation is recorded in the audit trail.

US4: Accept an invite

As an invitee, I want to click the link and land in the workspace with minimal steps.

• AC4.1 New user: the link opens sign-up with the email pre-filled. After the account is created, the user lands directly in the workspace with no intermediate screens.
• AC4.2 Existing user: the link opens login, or skips it if already logged in. The user then lands directly in the workspace.
• AC4.3 The invitee joins with the Member role.
• AC4.4 The team list status changes from Pending to Active. An audit entry and the invite_accepted analytics event are recorded.
• AC4.5 Expired link: the user sees a clear page saying "This invite has expired, ask <inviter/admin> to resend." No account is created and no access is granted.
• AC4.6 Revoked or already-used link: the user sees a clear error page and is not granted access.
• AC4.7 If the seat limit was reached between sending and accepting, acceptance is blocked with a clear message (see Q3).
• AC4.8 If the logged-in account's email differs from the invited email, the behaviour follows the decision on Q4.

US5: Invite expiry

• AC5.1 Invites expire [EXPIRY] after they were sent (or after the last resend, depending on Q5).
• AC5.2 Expired invites show as Expired in the team list and can be resent.

US6: Change a member's role

As an admin, I want to change a new member's role after they join.

• AC6.1 Admins can change a member's role from Settings > Team.
• AC6.2 Non-admins cannot change roles. This is enforced in both the UI and the API.
• AC6.3 Every role change is recorded in the audit trail.

US7: Seat limits

• AC7.1 On the free plan, the limit is [FREE_SEATS] total and the owner counts as one seat.
• AC7.2 On paid plans, the limit is the number of seats purchased.
• AC7.3 When the limit is reached, US1 shows the upgrade or buy-seats prompt (AC1.5).

US8: Audit trail

As an admin, I want to see who invited whom and when, and the outcome.

• AC8.1 Each audit entry records:
  • inviter
  • invitee email
  • action (sent / resent / revoked / accepted / expired / role changed)
  • timestamp
• AC8.2 Where the trail is visible (admin UI or internal only) and how long it is kept are to be confirmed (Q9).

US9: Analytics

• AC9.1 The invite_sent event fires when an invite is sent, with properties: workspace_id, plan, inviter_role.
• AC9.2 The invite_accepted event fires when an invite is accepted, with properties: workspace_id, plan, new vs existing user, time-to-accept.
• AC9.3 Whether resends count as invite_sent or have their own event is to be confirmed (Q11).

3. Non-functional requirements

• Tokens: Invite tokens are unguessable, single-use, and tied to the workspace and invited email.
• Permissions: All permission and rate-limit checks happen server-side.
• Deliverability: Invite emails must reliably reach inboxes, which means checking SPF/DKIM on the no-reply domain.
• Abuse protection: Consider a daily cap on invites per workspace, especially if any member can invite.

4. Questions to answer first

Blocking

1. D1 to D3 in the table above.
2. Does a pending invite use up a seat, or are seats only counted on acceptance? If invites don't hold seats, you could have more pending invites than free seats.
3. If seats run out before someone accepts, should acceptance be blocked, or should the invitee be allowed in? On a paid plan, are seats auto-purchased?
4. If someone opens the link while logged in with a different email, should they be allowed to accept, blocked, or prompted to switch accounts?
5. Does resending reset the expiry clock?

Important 6. Can someone belong to multiple workspaces? If yes, can an existing user from another workspace accept an invite? 7. Are paid invites charged immediately, with prorated billing for added seats? 8. Should revoked and expired invites stay visible in the team list or disappear? 9. Where is the audit trail shown (an admin UI page or internal only), and how long is it kept? 10. Does buying seats in Settings > Billing already exist, or is it part of this work? 11. Should resends be tracked as a separate analytics event? 12. Can an invite be sent to someone who was previously removed from the workspace?

Scope and timeline 13. Is "no extra screens" a strict rule? A short "Join <workspace>?" confirmation can prevent accidental joins. 14. Who writes the email copy, and does it need branding? 15. Is three weeks realistic for this scope plus QA? The main risks are the billing and seat logic (Q2, Q3, Q7, Q10). If time is tight, the audit trail UI could be cut to internal-only for v1.

Hi Mr Okafor, something has come up on the morning of 14 June, so I can't do 9am. Could you come at 2pm that day instead, or any time on 15 June? Sorry for the change, and thanks! Nadia

Hi Mr Okafor, unfortunately something's come up and I can't do 9am on 14 June for the kitchen tap. Would 2pm that day work instead, or any time on 15 June? Let me know what suits you. Thanks, Nadia

You were overcharged by £1.00. The tomatoes line is wrong, and you should have paid £27.01, not £28.01.

Line-by-line check

Other checks

• Item count: 10 is correct, counting the 2 oat milks separately.
• Total: The printed lines do add up to £28.01, so the till added correctly. The error is the tomato line itself, which is £1.00 too high.

What you should have paid

• Corrected total: 28.01 − 1.00 = £27.01

Take the receipt (Receipt 004817, Till 3) back to Fernbank Market and ask for a £1.00 refund to your card.

Yes, one line is wrong. The tomatoes were overcharged by exactly 1.00, so you paid 28.01 but should have paid 27.01.

Line-by-line check

Totals

• Item count: 10 is correct (9 lines, with 2 oat milks).
• Printed total (28.01): This is the correct sum of the lines as printed. The till added up correctly, but the overcharged tomato line went into the total.
• Correct total: 28.01 − 1.00 = 27.01
• Card payment: 28.01, so you're owed 1.00.

What to do

Take the receipt back to the shop and point to the tomato line. The receipt shows the weight (0.615 kg) and the price per kg (4.80) next to the 3.95 charge, so staff can confirm the error quickly. Because you paid by contactless card, bring the same card in case they refund it to the card.

One limitation: for the single-price items, I could only check that they were added correctly. I can't tell whether they match the shelf prices, so check those if any seemed higher than the label.

I don't recognize "The Lamplighter's Arithmetic" by Odalys Prewitt. It may be very new, self-published, or not widely covered, or the title or author name may differ slightly from what I have. I won't invent characters, a twist, or quotes, because quoting made-up lines at book club would backfire quickly.

Ways to get up to speed fast:

• Read the ending first. If the twist is the main topic, the last few chapters may be enough to follow the discussion.
• Check reader reviews on Goodreads, StoryGraph, or Amazon. Many include spoiler-tagged summaries of the twist.
• Read the publisher's description and early reviews for the main characters and premise.
• Ask a fellow member for a two-minute recap before the meeting. People usually enjoy explaining a twist they loved.
• Skim for quotes by flipping through for passages others have highlighted. Kindle's "Popular Highlights" is handy for this.

Low-risk ways to join in without having read it:

• "Did you see the twist coming, or were there clues you only noticed afterward?"
• "Which character changed the most for you?"

If you can paste the jacket copy or a summary, I can help you pull out key points and discussion questions.

I'm sorry, but I don't have reliable information about The Lamplighter's Arithmetic by Odalys Prewitt. It may be very new or a small-press title. I won't invent characters, a twist or quotes, since made-up quotes would backfire fast if someone has the book open. Here's how to catch up quickly:

• Read spoiler-tagged reviews on Goodreads or StoryGraph, where readers often break down the ending in detail.
• Skim the first chapter and the last few for the setup and the twist.
• Pick your own quotes from those pages. A line you chose is easier to talk about.

Questions that work for almost any twist ending (and get others explaining it to you):

1. Did the twist feel earned, or did it come out of nowhere?
2. What clues did you spot in hindsight?
3. Does it change how you see the main character?
4. Does the title mean something different after the ending?

If you paste in a blurb, review or passage, I'll turn it into a quick character rundown and talking points.

Subject: Gwen's leaving dinner: final details

Hi everyone,

Thank you all for your patience with the back-and-forth. Here's where everything has landed for Gwen's leaving dinner in the Marlow Room at Harbour House on 18 October at 7pm.

There will be 20 of us, including Gwen, whose share we're covering. With room hire at £180 and the set menu now £48 a head, the total comes to £1,140. Split between the 19 of us paying, that's £60 each.

Please pay me £60 by bank transfer by 2 October. The bank details are in my earlier email. The restaurant needs the money by then, so the earlier date really matters.

Please send any dietary needs to Ben by 27 September. Thanks again, Ben, for taking that on!

I can't wait to give Gwen a lovely send-off with you all.

Warm wishes, Aisha

Subject: Gwen's leaving dinner: final cost and payment

Hi all,

Thank you so much for your patience with all the back-and-forth! Here's where we've landed for Gwen's leaving dinner on 18 October at 7pm in the Marlow Room at Harbour House.

There are 20 of us including Gwen. With the £180 room hire and the set menu now £48 a head, it works out at £60 each for the 19 of us paying.

Please send your £60 to me by bank transfer by 2 October (a little earlier than I first said), using the details in my other email.

If you have any dietary needs, please send them to Ben by 27 September.

I can't wait to give Gwen a proper send-off with you all!

Aisha

1. 4 hours 15 minutes. The standard time is 3:45. Size L adds 10 minutes to the bake and 15 minutes to the rise on whole wheat, and Dark crust adds 5 minutes.
2. You can't set it right now. 8:15 pm to 9:30 am is 13 h 15 min, but the longest delay is 13 hours. You can either:
   • wait until 8:30 pm and set 13:00, which gives bread at 9:30, or
   • set 13:00 now and have the bread ready at 9:15.
3. E01 means the motor is blocked and the paddle can't turn. Unplug the machine and let it cool. Then check that the pan is seated properly and that nothing is stuck under the paddle.
4. Use programme 7 (Gluten-free) at size M, with no more than 400 g of flour. Size L isn't available on programme 7.
5. The manual doesn't say. It only says the machine switches to keep-warm after the bake and that you can end it by holding START/STOP for 3 seconds.
6. 6 months. The paddle is a wearing part, so it doesn't get the 24 months that covers the rest of the machine.