Claude Sonnet 5.5

What was wrong

The cursor only holds created_at, and the query uses created_at <= ? with no tiebreaker. Two things go wrong:

• Duplicates: the next page starts at the last row's timestamp, inclusive. Every post sharing that timestamp that was already shown comes back again.
• Missing posts: ORDER BY created_at DESC has no tiebreaker, so rows with equal timestamps can come back in any order. If a tie group straddles a page boundary, the rows on the next page may not be the ones that were skipped. If a whole page shares one timestamp, the cursor can never advance past it. Busy days and batch imports make ties common, which is why it shows up more then.

The fix

Order by (created_at DESC, id DESC), a total order, and make the cursor carry both values. The next page then starts strictly after that position.

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.

    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.

    Ordering is created_at DESC, then id DESC for posts sharing a created_at.

    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:
        try:
            created_str, id_str = cursor.split(":", 1)
            last_created, last_id = int(created_str), int(id_str)
        except (ValueError, AttributeError):
            raise ValueError("invalid 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, last_created, 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

Notes

• The cursor is now a string like "1700000000:42" (created_at and id). The app still just passes it back unchanged. Old cursors (a bare timestamp) will now raise ValueError. If you need to tolerate them during rollout, catch that and restart from page one.
• This is keyset pagination, so it stays correct when new posts arrive mid-scroll: new posts sort ahead of the cursor and don't shift later pages, which would cause duplicates with OFFSET.
• For speed on a large table, add an index: CREATE INDEX idx_posts_created_id ON posts(created_at DESC, id DESC);

What was wrong

The cursor was only the last post's created_at. That value isn't unique, since the importer gives many posts the same second, and the query had no tie-breaker.

• WHERE created_at <= ? re-selects every post with that timestamp, including ones already on the previous page, so you get duplicates. Changing it to < would just turn those into skipped posts.
• If limit or more posts share a timestamp, the cursor stops advancing because every page ends inside that group. The older posts are never reached.
• ORDER BY created_at alone leaves the order of equal timestamps undefined, so it isn't guaranteed to be the same from one query to the next.

Busy days and import runs create more same-second posts, which is why it shows up more then.

The fix is to order by the unique pair (created_at DESC, id DESC), put both values in the cursor, and fetch rows strictly after that pair. id alone can't be the cursor, since ids don't follow created_at.

Fixed function

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.

    rows is a list of (id, author, body, created_at) tuples, ordered by
    created_at descending; posts with the same created_at come higher id
    first.
    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 of the previous page.
        try:
            after_created_at, after_id = map(int, cursor.split(":"))
        except ValueError:
            raise ValueError(f"invalid cursor: {cursor!r}") from None
        cur = conn.execute(
            f"SELECT {COLUMNS} FROM posts "
            "WHERE (created_at, id) < (?, ?) "
            "ORDER BY created_at DESC, id DESC LIMIT ?",
            (after_created_at, after_id, limit + 1),
        )

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

    next_cursor = None
    if has_more:
        last = rows[-1]  # (id, author, body, created_at)
        next_cursor = f"{last[3]}:{last[0]}"
    return rows, next_cursor

Notes

• The cursor format changed. A cursor from the old code (a bare number) now raises ValueError, so anyone mid-scroll at deploy time has to reload from the top.
• (created_at, id) < (?, ?) needs SQLite 3.15 or later (2016). On a big table, add CREATE INDEX IF NOT EXISTS idx_posts_created_id ON posts (created_at, id); so SQLite can walk the index in order instead of sorting the whole table for every page.
• To check the fix, insert about 50 posts with the same created_at and scrambled ids. Page through with limit=10 and confirm every id comes back exactly once.

Here is 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) {
  // Incremented on every input. A request is only "current" if its id
  // still matches this value when it settles.
  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 === "") {
        // Invalidates any in-flight request, since latestId has moved on.
        setLoading(false);
        render([]);
        return;
      }

      setLoading(true);
      try {
        const results = await fetchResults(query);
        if (id !== latestId) return; // stale: a newer input exists
        render(results);
      } catch (err) {
        if (id !== latestId) return; // stale error: ignore it too
        render([], err instanceof Error ? err : new Error(String(err)));
      } finally {
        if (id === latestId) setLoading(false);
      }
    },
  };
}

What was wrong

Both bugs came from treating every request as if it were the only one.

1. Stale results. The requests can't be cancelled and may finish in any order. If "ab" was typed after "a" but the "a" response arrived later, it called render last and overwrote the correct list. Each call to onInput now takes a sequence number, and after the await it only renders if it is still the newest one. This applies to errors as well, so an old failure can't replace a newer good list.
2. Spinner disappearing early. Every request's finally ran setLoading(false), so when an older request finished, it turned the spinner off while the newest was still loading. Now only the newest request can turn it off, and it does so right after rendering its results or error.
3. Emptied box. The empty-query path also takes a new sequence number, which invalidates anything in flight. It clears the list and turns the spinner off explicitly (it previously didn't touch the spinner, so one started by an earlier keystroke would stay on). Any older request that finishes later is ignored.

The file uses only plain TypeScript syntax that Node's type stripping handles (no enums or other syntax that needs compiling). It still calls fetchResults on every input, and the exported function, its options and onInput are unchanged.

Here is the whole 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) {
  // Id of the most recent onInput call. Requests can't be cancelled, so a call
  // may only touch the list or the spinner while its id is still the latest.
  let latestId = 0;

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

      // Every call takes a new id, including one that empties the box, so that
      // all requests started earlier become stale. Do this before any await.
      const id = ++latestId;
      const isLatest = () => id === latestId;

      if (query === "") {
        setLoading(false);
        render([]);
        return;
      }

      setLoading(true);
      try {
        const results = await fetchResults(query);
        if (isLatest()) render(results);
      } catch (err) {
        if (isLatest()) {
          render([], err instanceof Error ? err : new Error(String(err)));
        }
      } finally {
        // Re-checked on purpose: a stale call must not switch off the spinner
        // that now belongs to a newer search.
        if (isLatest()) setLoading(false);
      }
    },
  };
}

What was wrong

• Stale list: every onInput call awaited its own request and then rendered whatever came back. Responses can arrive in any order, so the last one to land won, not the one for the text currently in the box.
• Spinner: finally { setLoading(false) } ran for every request. The first old request to finish switched the spinner off while the newest was still loading.
• Emptying the box: that branch cleared the list but never touched the spinner or the requests already in flight. A slow old response could repaint the list afterwards, and the spinner could stay on until it landed.

The fix

Since the requests can't be cancelled, they run to the end and their outcome is ignored. A counter inside createSearch goes up on every onInput call, including the empty-box one. A call only touches render or setLoading(false) if it is still the latest when it finishes. This covers errors too, so a failed old request shows nothing. Emptying the box makes everything in flight stale and turns the spinner off itself.

I used a counter instead of comparing query strings because the same text can come back (a → ab → a). An old request would then look current while the newest is still loading. The counter is per createSearch instance, so several search boxes won't interfere with each other.

The exports, options and onInput signature are unchanged, and there is still one fetchResults call per non-empty input. The only TypeScript syntax is type annotations, so Node can run the file directly. If you add debouncing later, keep the guard, because debouncing reduces overlapping requests but doesn't make responses arrive in order.

I haven't run this. I traced it by hand for these cases:

• the newer request finishes first
• the older request finishes first
• an older request fails
• the box is emptied, and then retyped, while a request is in flight

There were two bugs, and the fix needs both the local date and the original day.

// 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) {
  // Local calendar date of the signup in the customer's time zone.
  const parts = new Intl.DateTimeFormat("en-CA", {
    timeZone,
    year: "numeric",
    month: "2-digit",
    day: "2-digit",
  }).formatToParts(new Date(signupIso));
  const get = (type) => Number(parts.find((p) => p.type === type).value);
  const year = get("year");
  const month = get("month"); // 1-12
  const day = get("day");

  const pad = (n, w = 2) => String(n).padStart(w, "0");
  const dates = [];
  for (let i = 1; i <= count; i++) {
    // Months since year 0, always counted from the original signup month.
    const total = year * 12 + (month - 1) + i;
    const y = Math.floor(total / 12);
    const m = total % 12; // 0-11
    // Day 0 of the next month = last day of this month.
    const lastDay = new Date(Date.UTC(y, m + 1, 0)).getUTCDate();
    dates.push(`${pad(y, 4)}-${pad(m + 1)}-${pad(Math.min(day, lastDay))}`);
  }
  return dates;
}

What was wrong

1. Wrong time zone. The old code took the day of the month from the UTC date, ignoring timeZone. For the Auckland customer who signed up on the morning of the 1st local time, it is still the 31st in UTC, so they were renewed on the 31st. The fix reads the signup's local calendar date in the customer's time zone using Intl.DateTimeFormat, and builds the output dates from that.
2. Drift from the previous renewal. The loop read day from d on every pass, and d had already been clamped to a shorter month. A January 31st signup became February 28th, and it stayed on the 28th from then on. The fix takes the day once from the original signup and computes each renewal as signup month + i, clamping to the month's length each time. So a 31st signup gives Feb 28, Mar 31, Apr 30, and so on.

Because the output dates are built from plain year, month and day numbers rather than from a Date in a time zone, daylight saving changes can't shift them.

// 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 as the customer sees it, not in UTC.
  const parts = new Intl.DateTimeFormat('en-US', {
    timeZone,
    calendar: 'gregory',
    numberingSystem: 'latn',
    year: 'numeric',
    month: 'numeric',
    day: 'numeric',
  }).formatToParts(new Date(signupIso));
  const part = (type) => Number(parts.find((p) => p.type === type).value);
  const signupYear = part('year');
  const signupMonth = part('month') - 1; // 0-11
  const signupDay = part('day');

  const pad = (n, width) => String(n).padStart(width, '0');

  const dates = [];
  for (let i = 1; i <= count; i++) {
    // Always offset from the original signup month, never from the last renewal.
    const monthsFromJan = signupMonth + i;
    const year = signupYear + Math.floor(monthsFromJan / 12);
    const month = monthsFromJan % 12; // 0-11
    const lastDay = new Date(Date.UTC(year, month + 1, 0)).getUTCDate();
    const day = Math.min(signupDay, lastDay);
    dates.push(`${pad(year, 4)}-${pad(month + 1, 2)}-${pad(day, 2)}`);
  }
  return dates;
}

What was wrong

1. It ignored timeZone. It took the day from the UTC instant (getUTCDate) and returned UTC dates (toISOString). Auckland is 12–13 hours ahead of UTC, so a morning-of-the-1st signup is still the 31st in UTC. That customer was anchored to the 31st and billed a day early. The fix reads the signup's calendar date in the customer's zone with Intl.DateTimeFormat. It then does all the month arithmetic on plain year/month/day numbers, so DST can't shift anything.
2. It counted from the previous renewal. day was re-read from d on every pass, and d had already been clamped. Once February pushed the 31st down to the 28th, every later month used the 28th. Now the day is read once from the signup, and each renewal is the signup month plus i, clamped to that month's length.

I checked these by hand rather than running them:

renewalDates("2025-01-31T10:00:00Z", "Europe/London", 4);
// ["2025-02-28", "2025-03-31", "2025-04-30", "2025-05-31"]

renewalDates("2025-05-31T20:00:00Z", "Pacific/Auckland", 3);
// local signup is 1 June -> ["2025-07-01", "2025-08-01", "2025-09-01"]

One behaviour change: timeZone is now actually used, so an invalid zone name throws a RangeError instead of being silently ignored. If you have renewal dates already stored from the old function, they will still be wrong until recomputed.

Must fix before merging

1. Broken access control on GET /invoices/:id. findInvoiceById looks up by id only and never checks account_id. Any logged-in user can read any account's invoice by guessing or enumerating ids. Pass req.user.accountId into the query (WHERE id = $1 AND account_id = $2) and return 404 on a miss, so you don't reveal that the id exists. This is the blocker.
2. The to date isn't inclusive, contrary to the code comment and docs. The query uses created_at < $3 with to as a bare day, so to=2026-01-31 is treated as midnight at the start of Jan 31 and excludes that whole day. Invoices created on the last day vanish. Fix it by making the upper bound exclusive of the next day: created_at < ($3::date + 1), or compute the next day in code. Also check created_at's type and timezone: if it's timestamptz, the day boundaries depend on the session timezone, so decide whether that's UTC and say so in the docs.
3. Date validation is regex-only. 2026-99-99 passes DAY and then Postgres throws, producing a 500 rather than a 400. Validate that it's a real calendar date (e.g. parse it and round-trip it). Consider also rejecting from > to.
4. Async handlers have no error handling. If you're on Express 4, a rejected promise from db.query is never passed to the error middleware and can hang the request or cause an unhandled rejection. Wrap the handlers or use express-async-errors. Express 5 handles this itself. Confirm which version you're on.

Worth knowing

• The sort whitelist is done correctly: Object.hasOwn guards against __proto__ and similar keys, and column and direction only ever come from fixed values. There's no SQL injection risk there.
• String(req.query.from ?? "") turns arrays like ?from=a&from=b into odd strings. They fail the regex, so it's safe, but it's slightly sloppy.
• The list endpoint has no pagination or limit. An account with many invoices returns everything in one response. Add a limit and a cursor or offset before it becomes a problem.
• Add a tiebreaker to ORDER BY (e.g. , id) so pages and ordering are stable when amounts or dates are equal.
• The fmtMoney → formatMoney rename is clean, and both callers are updated. If anything outside this diff imports fmtMoney (scripts, tests), it will break, so grep for it.
• req.user is used without a null check, which is fine because requireLogin runs first, but make sure its typing is declared.
• There are no tests in this PR. At minimum add tests for cross-account access (item 1) and for the last-day boundary (item 2).

Fix before merging (most important first)

1. Any logged-in user can read any account's invoice. findInvoiceById filters on id alone, and GET /invoices/:id never checks the owner. Pass req.user.accountId in and query WHERE id = $1 AND account_id = $2. The existing 404 branch then covers other accounts' invoices without revealing that the ID exists. (The list endpoint is scoped correctly.)
2. to isn't inclusive, despite the comment and docs. created_at < $3 with to=2026-01-31 excludes everything from 00:00 on Jan 31, so from == to always returns nothing. Use created_at < ($3::date + 1).
3. Validation accepts impossible dates. The regex lets 2026-02-31 through. The database then throws, giving a 500 (or an unhandled rejection, depending on how your app handles async errors) instead of your bad_date 400. If invoices.id is a uuid column, /:id has the same problem with malformed IDs. Swap the DAY.test calls for a real date check:

const isDay = (s: string) => {
  const d = new Date(`${s}T00:00:00Z`);
  return DAY.test(s) && !Number.isNaN(+d) && d.toISOString().startsWith(s);
};

Worth knowing

• Check the rename before merging. fmtMoney → formatMoney only updates receipts.ts in this diff. I can't see the rest of the repo, so grep for leftover fmtMoney callers and run tsc.
• The dynamic ORDER BY is safe. The whitelist plus Object.hasOwn blocks keys like __proto__, and dir maps to a literal. No change needed.
• There's no pagination or limit, so a wide date range returns every invoice. Add an id tiebreaker to the ORDER BY when you paginate.
• Day boundaries use the database session's time zone. Confirm that's what you want.
• There are no tests. A cross-account fetch test would have caught #1, and a from == to test would have caught #2.
• The docs don't say from and to are required, or list the 400/404 responses.

WITH ordered AS (
  SELECT
    customer_id,
    start_date,
    MAX(COALESCE(end_date, '9999-12-31')) OVER (
      PARTITION BY customer_id
      ORDER BY start_date, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_end
  FROM subscription_periods
)
SELECT
  c.name                                                      AS name,
  o.prev_end                                                  AS last_access,
  o.start_date                                                AS came_back,
  CAST(julianday(o.start_date) - julianday(o.prev_end) - 1 AS INTEGER) AS days_without
FROM ordered AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.prev_end IS NOT NULL
  AND o.start_date > date(o.prev_end, '+1 day')
ORDER BY c.name, o.start_date, c.id;

How it works

• For each period, the window function looks at all the customer's earlier periods (ordered by start date, with id only as a tie-breaker) and takes the latest end date among them. This is prev_end, the last day of access the customer had accumulated before this period began.
• Using the running maximum rather than just the previous row's end date is what handles overlaps and periods nested inside longer ones. A short period inside a longer one can't pull prev_end backwards.
• A NULL end date (still active) is treated as 9999-12-31. Once a customer has an open-ended period, no later period can start after prev_end, so no gap is reported. A customer who left and never returned has no later period, so no row is produced.
• A gap exists only when a period starts later than the day after prev_end. Touching periods (start = end + 1), same-day starts and overlaps all fail that test, so they aren't counted.
• Each gap is reported once, at the first period that starts after it. last_access is prev_end, came_back is that period's start date, and days_without is the day difference minus 1 (e.g. 2024-06-30 → 2024-07-10 gives 10 − 1 = 9).
• Sorting is by name, then came_back, then customer id, using SQLite's default binary collation. That puts all uppercase letters before lowercase ones, so if names have mixed case and you want a case-insensitive order, use ORDER BY c.name COLLATE NOCASE, ....

On your sample data this returns the three expected rows: Amara 9 days, and Cleo 1 day and 31 days. Bruno is excluded because his periods overlap and the latest one is still active.

WITH running AS (
  SELECT
    customer_id,
    start_date,
    -- latest end date among this customer's periods that sort before this one
    -- (NULL end_date = still active, treated as open-ended)
    MAX(COALESCE(end_date, '9999-12-31')) OVER (
      PARTITION BY customer_id
      ORDER BY start_date
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_end
  FROM subscription_periods
),
gaps AS (
  SELECT
    customer_id,
    prev_end   AS last_access,
    start_date AS came_back,
    CAST(julianday(start_date) - julianday(prev_end) AS INTEGER) - 1 AS days_without
  FROM running
)
SELECT
  c.name         AS name,
  g.last_access  AS last_access,
  g.came_back    AS came_back,
  g.days_without AS days_without
FROM gaps AS g
JOIN customers AS c ON c.id = g.customer_id
WHERE g.days_without > 0
ORDER BY c.name COLLATE NOCASE, g.came_back, c.id;

How it works

• Running maximum end date: running gives each period a prev_end, the latest end date among that customer's periods that start before it. The frame stops one row short of the current row. A running maximum, rather than the previous row's end date, is what makes touching, same-day, overlapping and fully nested periods harmless. It never looks at ids. A NULL end date counts as 9999-12-31, so nothing can follow an active period as a gap.
• Counting whole days: gaps counts the days between prev_end and the period's start, minus 1. Touching periods give 0, and overlaps or same-day starts give negative numbers. Only positive values survive WHERE g.days_without > 0. A customer's first period has no prev_end (NULL), so it drops out there too.
• What each row means: each surviving row is the first period after a gap. came_back is its start date and last_access is the prev_end before it. A customer who left and never returned has no later period, so they produce no row.
• Sorting: names sort case-insensitively (COLLATE NOCASE), so "de Souza" lands under D instead of after every capitalised name. Delete that clause if you want strict byte order. Ties then go by came_back and customer id.

I traced your sample by hand and it gives the three expected rows (Amara 9, Cleo 1, Cleo 31). It assumes no period has an end date earlier than its start date. It uses only window functions (SQLite 3.25+) and the built-in date functions, so 3.53 is fine.

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 _norm(s):
    return " ".join(s.split()).lower()


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

    try:
        text = raw.decode("utf-8-sig")
    except UnicodeDecodeError:
        # Windows-1252 has a few undefined bytes; map them through as latin-1
        try:
            text = raw.decode("cp1252")
        except UnicodeDecodeError:
            text = raw.decode("latin-1")
        if text.startswith("\ufeff"):
            text = text[1:]

    if not text.strip():
        return []

    # Header line decides the separator (first line, ignoring quoted content)
    header_line = text.split("\n", 1)[0]
    in_q = False
    commas = semis = 0
    for ch in header_line:
        if ch == '"':
            in_q = not in_q
        elif not in_q:
            if ch == ",":
                commas += 1
            elif ch == ";":
                semis += 1
    delim = ";" if semis > commas else ","

    reader = csv.reader(io.StringIO(text, newline=""), delimiter=delim, quotechar='"')
    rows = iter(reader)

    header = None
    for row in rows:
        if any(c.strip() for c in row):
            header = row
            break
    if header is None:
        return []

    idx = {"name": None, "email": None, "phone": None}
    for i, h in enumerate(header):
        n = _norm(h)
        if n in _NAME_HEADERS and idx["name"] is None:
            idx["name"] = i
        elif n in _EMAIL_HEADERS and idx["email"] is None:
            idx["email"] = i
        elif n in _PHONE_HEADERS and idx["phone"] is None:
            idx["phone"] = i

    if idx["email"] is None:
        return []

    def cell(row, key):
        i = idx[key]
        if i is None or i >= len(row):
            return ""
        return row[i].replace("\r\n", "\n").replace("\r", "\n").strip()

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

How it works: The file is read as bytes and decoded as UTF-8 (dropping any BOM), falling back to Windows-1252. The separator is chosen by counting unquoted commas vs. semicolons in the header line. csv.reader then handles quoting, embedded separators, doubled quotes and line breaks (normalized to \n). Columns are found by case- and space-insensitive header matching, and rows are stripped, skipped if they have no email, de-duplicated on the lower-cased email, and kept in file order. A file without an email column returns an empty list.

from bisect import bisect_left
from datetime import timedelta


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
    """
    week = timedelta(days=7)

    # Group invoices by (customer, amount); sort each group by (due, id).
    raw = {}
    for inv in invoices:
        raw.setdefault((inv["customer"], inv["amount"]), []).append(inv)

    groups = {}
    for key, lst in raw.items():
        lst.sort(key=lambda v: (v["due"], v["id"]))  # stable, as the original's first-seen tie behaviour
        n = len(lst)
        groups[key] = (
            [v["due"] for v in lst],   # sorted due dates
            [v["id"] for v in lst],    # ids in the same order
            list(range(n + 1)),        # "next candidate" links (union-find), sentinel at n
            n,
        )

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

    for pay in payments:
        g = groups.get((pay["customer"], pay["amount"]))
        if g is None:
            unmatched.append(pay["id"])
            continue
        dues, ids, parent, n = g
        pdate = pay["date"]

        start = bisect_left(dues, pdate - week)

        # Find the first index >= start whose invoice is not used.
        r = start
        while r < n:
            if parent[r] != r:
                r = parent[r]
            elif ids[r] in used:
                parent[r] = r + 1
                r += 1
            else:
                break
        # Path compression.
        j = start
        while j < r:
            nj = parent[j]
            parent[j] = r
            j = nj

        if r >= n or dues[r] > pdate + week:
            unmatched.append(pay["id"])
        else:
            used.add(ids[r])
            matches.append((pay["id"], ids[r]))

    return matches, unmatched

Why the old one was slow: for every payment it scanned the whole invoice list and ran the checks on each invoice. That is about 200,000 × 200,000 = 40 billion iterations, so it never finishes in a reasonable time.

What I changed:

• Grouping: invoices are grouped once by (customer, amount), so a payment only looks at invoices that could match on those two fields.
• Sorting: each group is sorted once by (due, id). The first usable invoice in that order is exactly the one the old tie-break rules would pick (earliest due date, then lowest id).
• Window lookup: bisect jumps straight to the first invoice due on or after payment date − 7 days. If that invoice is due after payment date + 7 days, nothing qualifies and the payment is unmatched.
• Used invoices: a large customer can have thousands of same-amount invoices, so skipping used ones one by one could become slow again. Each group keeps a "next unused" link with path compression, so used invoices are skipped in near-constant time. Used invoices are still tracked by id, as before.

The total cost is roughly O((P + I) log I), which should take a second or two for 200,000 of each.

Behaviour preserved:

• Payments are processed in the given order.
• The 7-day limit is inclusive.
• An invoice is used at most once.
• The inputs are not modified. Only new lists are built, and the sorting is done on a copy.

Dog-Grooming Booking System: Plan

Guiding principle

Build the smallest thing that takes real bookings and real money, then add comfort. A one-person business doesn't need multi-staff, multi-location or accounts. Avoid generalizing.

Stack

Pick boring, hosted pieces so you aren't running servers on weekends.

• Backend: Python with FastAPI (or Django, if you want the admin and auth for free). Django is a good fit here: its admin gives you a rough owner dashboard on day one.
• Database: PostgreSQL (managed: Neon, Supabase or Render). SQLite is tempting, but you want transactional guarantees and easy backups.
• Frontend: A mobile-first web app, with no native app. Server-rendered pages with a bit of HTMX or Alpine.js are enough. If you prefer, use React/Next.js, but it's more moving parts. Make it a PWA (installable to her home screen) so the owner view feels like an app.
• Payments: Stripe Checkout (hosted page), with webhooks to confirm. You never touch card data.
• Reminders: SMS via Twilio, or email via Postmark/Resend. SMS is more reliable for reminders, but costs money and has registration hurdles. Start with email, add SMS later. Run it from a scheduled job (cron on the host, or a worker like APScheduler).
• Hosting: Render, Fly.io or Railway. One web service, one Postgres, one cron job.
• Auth: None for clients (booking by email plus a signed link to manage or cancel). Password or magic-link login for your sister only.

Data model

Store every instant in UTC, and keep the business time zone (e.g. Europe/London) as config. Convert only at the edges.

• Service: id, name, base_duration_min (30/60/90), price_cents, active
• Client: id, name, email, phone
• Dog: id, client_id, name, size (small_medium | large), notes
• Booking: id, client_id, dog_id, service_id, start_at (UTC), end_at (UTC), status (pending_payment | confirmed | cancelled_refunded | cancelled_kept | completed | no_show), duration_min (computed and stored), price_cents, deposit_cents, stripe_payment_intent_id, manage_token, reminder_sent_at, created_at, expires_at (for pending holds)
• WeeklyHours: weekday, open_time, close_time (local time). Tuesday to Saturday: 09:00 to 17:00.
• Break: weekday, start_time, end_time (lunch 12:30 to 13:30). Could be folded into WeeklyHours as multiple windows per day.
• TimeOff (blocked periods): start_date, end_date (or datetime range), reason
• Settings: timezone, booking_horizon_days (42), cancellation_window_hours (24), deposit_percent (20), slot_granularity_min (e.g. 30)

Key design choices:

• Store duration and price on the booking. If she later changes a service or the large-dog rule, existing bookings stay correct.
• Large dog = 2x duration. A 90-minute service becomes 180 minutes. Check that this actually fits: it can't straddle lunch (unless she says it can), and it can't run past 17:00. Large dogs on a 90-minute service are only bookable in 9:00-12:30 (starting by 9:30) or 13:30-17:00 (starting by 14:00). That's a real constraint, so confirm it with her. Also ask whether the price doubles.
• Prevent double booking in the database, not only in code. In Postgres, use an exclusion constraint on a tstzrange(start_at, end_at) for active statuses (needs the btree_gist extension). This makes races impossible.

Availability logic

Compute slots on demand, not stored:

1. For the requested date, take the local working windows (open hours minus the lunch break).
2. Subtract TimeOff and existing active bookings (including unexpired pending_payment holds).
3. For the needed duration (after the large-dog multiplier), list start times on the slot grid where the whole interval fits within a working window.
4. Reject dates in the past or beyond 42 days.

Build this as one pure function with lots of unit tests. It is the heart of the system.

Payment and cancellation flow

1. Client picks service, dog size and slot, then enters details.
2. Create a pending_payment booking holding the slot for ~10 minutes, then start Stripe Checkout for the 20% deposit.
3. The Stripe webhook (checkout.session.completed) flips it to confirmed. Never trust the browser redirect alone.
4. Expired holds are released by a cleanup job (or simply ignored by the availability query because of expires_at).
5. Cancellation via the manage link: if more than 24 hours before start_at, issue a Stripe refund and set cancelled_refunded. Otherwise set cancelled_kept. The server decides, using the clock, not the client.
6. Her own cancellations (sick day) should always refund, so give her a separate action.

Deposit on 20% of what? Decide whether it's of the doubled price for large dogs, and round consistently (cents).

Phases

Phase 1: Availability engine (weekend 1-2). Models, hours, lunch, time off, durations, large-dog rule, slot calculation, with tests, including DST cases. No UI yet beyond a plain page listing slots.

Phase 2: Booking without money (weekend 3). Booking form, DB constraint against double booking, confirmation email, manage link. Test on yourself.

Phase 3: Payments (weekend 4-5). Stripe Checkout, webhooks, pending holds, expiry, refunds on cancellation. Use Stripe test mode throughout. Make webhook handling idempotent.

Phase 4: Her side (weekend 6). Mobile login, today/week view, block days, cancel a booking, mark no-show or completed. Test on her actual phone.

Phase 5: Reminders and polish (weekend 7). Day-before reminder job, error alerting, backups, terms/cancellation policy text, privacy notice.

Launch: a soft launch with a few friendly clients while she keeps her current system as a backup for a couple of weeks.

Later: SMS, Google Calendar sync, waitlist, rebooking in one tap.

What could go wrong

Daylight saving time (the big one).

• Store UTC, define hours in local wall-clock time, and generate slots by building local datetimes with a proper tz library (zoneinfo) and then converting. Never add 24 hours to a timestamp to get "tomorrow".
• Since she's open Tuesday to Saturday, the clock changes on Sunday and won't touch her working hours directly, but a booking made before the change for a day after it must still land at the right local time. Also, "24 hours before" for refunds and "the day before" for reminders are computed in absolute time, so a booking at 9:00 Tuesday after a Sunday change has a slightly different gap than you'd expect. Test both changeovers.
• Display times in the business time zone, not the visitor's browser zone.

Race conditions. Two clients grab the same slot, or the webhook arrives after the hold expires. The DB constraint covers the first. For the second, if payment succeeds on an expired hold, either re-check the slot and confirm, or auto-refund.

Webhooks. Stripe retries and delivers out of order. Verify signatures, and make handlers idempotent.

Reminder reliability. Cron jobs fail silently. Record reminder_sent_at, run the job often (hourly) selecting "due and not yet sent", and alert on failures. Avoid double sends. Define "the day before" explicitly (e.g. 10:00 local the previous day), and think about Tuesday bookings: the day before is Monday, when she's closed, but the reminder still goes out.

Large-dog ambiguity. Clients self-report size and may get it wrong. Add a note ("we'll confirm size at drop-off") and let her edit a booking's size or duration. Decide what happens if the dog turns out to be large, with no free time after.

Policy edge cases. Rescheduling (is it cancel and rebook?), no-shows, a late cancellation caused by her blocking a day that already has bookings. When she blocks a day, the system should list the affected bookings and prompt refund and notification, rather than silently leaving them.

Money and law. Refund fees (Stripe typically doesn't return its processing fee, so she may lose a little on each refund). Consumer cancellation rules vary by country, so write the policy clearly and show it before payment. Store minimal personal data and say so.

Phone usability. Most of the effort for her is the owner screen. Keep it to a handful of big-button actions, and test with her early rather than at the end.

Scope creep and maintenance. You'll be her IT support. Keep dependencies few, set up automatic backups, uptime monitoring and error tracking (Sentry free tier), and write a one-page "if it breaks" note.

Questions to ask her before you start

1. Does the large-dog rule double the price as well as the time?
2. Can appointments span the lunch break, or must they fit around it?
3. Does she want a buffer between appointments (cleaning, breaks)?
4. Does she want to approve bookings, or are they instant?
5. SMS or email reminders?
6. What's her current policy on late arrivals and no-shows?

Answering these before Phase 1 will save you rework, particularly the first three, since they change the availability logic.

Don't split into six microservices now. Your two real problems, 20-minute deploys and the timing-out monthly report, are both fixable in days to a couple of weeks inside the monolith, and microservices would likely make both worse while consuming your whole runway of engineering time.

Why microservices are the wrong move now

• Two engineers can't operate six services plus Kubernetes without most of their time going to infrastructure, service-to-service debugging, and distributed deploys instead of product.
• 200 customers on Postgres is small. A well-tuned monolith handles this scale comfortably.
• Microservices solve organizational scaling (many teams stepping on each other). You have two people.
• Investors at this stage care about traction and shipping speed. A fast-moving team on a simple stack is a better story than a half-finished migration.

What to do instead

1. Slow deploys (20 min): Profile the pipeline first. Usual culprits are uncached dependency installs, rebuilding Docker layers from scratch, running the full test suite serially, and slow migrations. Caching, parallelizing tests, and ordering Docker layers properly often cut this to under 5 minutes.
2. Report timeouts: This is almost certainly a query problem for your biggest customers (N+1 queries, missing indexes, aggregating raw rows on every request). Use EXPLAIN ANALYZE, add indexes, and move aggregation into the database. If it's still heavy, precompute the report with a background job (Celery or similar) or a materialized view, and serve the cached result.
3. Scaling headroom: Add a read replica, connection pooling (PgBouncer), and a few more app instances behind a load balancer. That covers a lot of growth.

If he wants a credible scaling story for investors Do a modular monolith: clean internal boundaries between domains (separate Django apps, explicit interfaces). Then you can extract a service later if a specific part genuinely needs independent scaling. That's a real option, not a rewrite.

A fair test for revisiting: split something out when a concrete bottleneck, such as a workload that needs different scaling or a team that's blocked, can't be solved inside the monolith.

I'd suggest a one- or two-week timebox on the deploy and report fixes, then reassessing. If both are solved, the case for the migration largely disappears.

Don't do the six-service split now. The slow deploys and report timeouts most likely aren't architecture problems, so microservices wouldn't fix them, but targeted fixes probably can, within days to weeks.

Why

• Traffic isn't the bottleneck. 200 business customers is small for Django on Postgres. Only the three biggest time out, which points to specific heavy queries, not a systemic limit.
• Microservices mostly solve a people problem (many teams blocking each other), not a traffic problem. With two engineers, that problem doesn't exist yet.
• The overhead is real. Six services means six pipelines, network calls between them, harder debugging, and a Kubernetes cluster to run and upgrade. That's likely weeks to months with nothing customer-visible shipped. And if the slow deploys come from the pipeline (usually the case), you'd inherit that slowness six times.

What to do instead

1. Deploys (20 min): Time each pipeline stage first. Then cache dependencies and Docker layers, run tests in parallel (Django's --parallel or pytest-xdist), and build the image once and promote it. This often gets under 5 minutes.
2. Monthly report: Run EXPLAIN ANALYZE on the slow queries. Usual culprits are missing indexes, N+1 queries, and aggregating in Python instead of SQL. Then generate the report in a background job and pre-aggregate into a summary table or materialized view.

These are the usual suspects, not confirmed causes.

For the funding round

Technical diligence at this stage rarely penalizes a well-run monolith. "We measured the bottlenecks, fixed them, and here's what would trigger a split" is a stronger story than a half-finished migration.

Middle path: Keep the monolith modular, with clean boundaries between apps, so extracting a service later is cheap. Split one out only for a concrete reason: a component that must scale independently, a team large enough to need its own deploys, or a compliance isolation requirement.

Proposal: Timebox two weeks to fix deploys and the report. If problems remain, revisit the split with real data.

Zero-downtime rename users.username → users.handle (Postgres 16)

Approach: expand/contract. Never rename in place while three separately deployed services read it. Add a new column, keep both in sync, migrate readers, then drop the old one. The API's JSON field username is a separate contract from the DB column and stays as-is for old mobile clients.

Why not ALTER TABLE ... RENAME COLUMN? It's instant on its own, but every deployed service that references username breaks the moment it runs. With three independently deployed services, you can't make that atomic. (A view-based alternative is at the end.)

Phase 1: Expand

1. Set safety timeouts for all DDL in this plan.

SET lock_timeout = '3s';
SET statement_timeout = '0';  -- for long backfills, set per-statement as needed

A short lock_timeout means a DDL statement that can't get its lock fails fast instead of queuing and blocking 500 req/s behind it. Just retry.

2. Add the nullable column (metadata-only, instant).

ALTER TABLE users ADD COLUMN handle text;

Rollback: ALTER TABLE users DROP COLUMN handle;

3. Add a sync trigger so both columns stay equal on every write. This lets the writer (main API) keep writing only username for now, and also lets it switch to writing handle later, without drift.

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

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

Note: username is NOT NULL but handle is nullable, so inserts that supply only handle will work because the trigger fills username before the constraint check.

Rollback: DROP TRIGGER users_sync_handle_trg ON users; DROP FUNCTION users_sync_handle();

4. Backfill existing rows in batches. Run from a script or job, not one giant UPDATE.

-- repeat until 0 rows updated; sleep 100-500ms between batches
WITH batch AS (
  SELECT id FROM users
  WHERE handle IS NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE users u SET handle = u.username
FROM batch WHERE u.id = batch.id;

Adjust id to your primary key. Check that a partial index isn't needed: if the WHERE handle IS NULL scan gets slow late in the backfill, instead iterate by primary-key ranges (WHERE id > $last AND id <= $last + 5000 AND handle IS NULL). Watch replication lag and autovacuum/bloat; 40M updated rows roughly doubles the table's physical size until vacuumed.

Rollback: Stop the job. It's harmless to leave partially done. To undo fully: UPDATE users SET handle = NULL in batches (or just drop the column per step 2).

5. Build the unique index concurrently.

CREATE UNIQUE INDEX CONCURRENTLY users_handle_key ON users (handle);

Run outside a transaction. If it fails, it leaves an INVALID index; drop it and retry:

DROP INDEX CONCURRENTLY IF EXISTS users_handle_key;

Do this after the backfill (faster, and no duplicates are possible since values are copied from a unique column). Mirror any other index on username (e.g. lower(username) for case-insensitive lookups) the same way.

Rollback: DROP INDEX CONCURRENTLY users_handle_key;

6. Add NOT NULL without a long lock.

ALTER TABLE users ADD CONSTRAINT users_handle_not_null
  CHECK (handle IS NOT NULL) NOT VALID;          -- instant
ALTER TABLE users VALIDATE CONSTRAINT users_handle_not_null;  -- scans, but only a light lock

In PG 16, SET NOT NULL will use the validated CHECK to skip the full scan:

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;

7. Verify parity before moving on.

SELECT count(*) FROM users WHERE handle IS DISTINCT FROM username;  -- expect 0

Run it repeatedly over a day; a non-zero result means something is bypassing the trigger (e.g. a writer disabling triggers, or a bulk load using session_replication_role = replica).

Phase 2: Migrate readers and writer

8. Deploy the two smaller services reading handle. They can go in any order, each independently. Because both columns are always in sync, there's no ordering constraint. Rollback: redeploy the previous version; username is still populated.

9. Deploy the main API reading handle from the DB, but still returning username in the JSON. Map it in the serializer: { "username": row.handle }. If you want the new name exposed to new clients, also return "handle": row.handle alongside it (additive and safe). Keep username in the response for the ~3-month tail of old mobile apps and beyond (see step 14). Rollback: redeploy the previous API version.

10. Deploy the main API writing handle (a separate deploy from step 9 so you can roll back each independently). The trigger copies it into username. Rollback: redeploy the previous version; the trigger handles the reverse direction.

11. Observe. Before removing anything, confirm that nothing still touches username:

• Enable pg_stat_statements or log sampling and search for queries mentioning username.
• Check for any other consumers: BI tools, cron jobs, read replicas used by analytics, ORMs with cached column lists, migrations, views, and functions (SELECT ... FROM pg_depend / search pg_get_viewdef and pg_proc.prosrc).
• Re-run the parity check from step 7.

Wait at least one full release cycle for the slowest service.

Phase 3: Contract

12. Remove the old column's dependencies. Once step 11 is clean:

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

Note: after this point, username is no longer updated, so rollback to old code is no longer safe. Take this step only when you're confident. To keep a cheap escape hatch, instead make username nullable first:

ALTER TABLE users ALTER COLUMN username DROP NOT NULL;

and wait another few days before dropping it. Rollback (before the drop in step 13): recreate the trigger and function from step 3, and re-run the backfill in the opposite direction (SET username = handle WHERE username IS DISTINCT FROM handle).

13. Drop the old column and index.

DROP INDEX CONCURRENTLY users_username_key;  -- use the real index name; if it backs a UNIQUE constraint, use ALTER TABLE ... DROP CONSTRAINT
ALTER TABLE users DROP COLUMN username;

DROP COLUMN is instant (it only marks the column dropped; space is reclaimed on later rewrites). This is the point of no return. Rollback is restoring from backup or PITR, or re-adding the column and repopulating from handle (the data is recoverable, but old code won't work until you do). Take a logical backup of (id, username) beforehand if you want certainty:

CREATE TABLE users_username_backup AS SELECT id, username FROM users;

14. Keep "username" in the API JSON for as long as old mobile versions are supported. This is purely an API-layer concern now and has no DB dependency. Remove it only after usage of old app versions drops to zero (check by app-version telemetry, not by calendar).

Cross-cutting guidance

• Lock safety: Every DDL above except CREATE/DROP INDEX CONCURRENTLY needs a brief ACCESS EXCLUSIVE lock. Use lock_timeout (step 1) and retry. Avoid running DDL while long transactions or long-running reports are open on users; check pg_stat_activity first.
• Replication: If you have logical replication or CDC (Debezium, etc.), the new column and its changes flow through; check that downstream consumers tolerate the schema change. Physical replicas just follow.
• Trigger cost: A row-level PL/pgSQL trigger at 500 req/s is negligible, as only the writes fire it, and the API is the sole writer.
• Staging first: Rehearse the whole sequence on a copy of production data, especially the backfill timing and the index build.

Alternative: rename + compatibility view

If you can't get the readers to change, you can instead RENAME COLUMN and create a view exposing the old name, but this requires every reader to query a view/different relation name in the same moment, so it doesn't fit three independent deploys. The expand/contract path above is the safer one here.

Team Invites v1 (Quillwharf): Spec Draft

Read this first: three contradictions in the brief

Your brief says two different things in three places. I haven't picked a side, so the spec marks these as [DECISION NEEDED]. The developer shouldn't start on the affected parts until you've answered.

My suggestions are in the questions section below.

Goal

Let customers add teammates to a Quillwharf workspace themselves, so support no longer adds people by hand.

Success signals: fewer support tickets about adding people, and invites sent and accepted tracked in analytics.

In scope

• Invite by email from Settings > Team
• Pending state, resend, and revoke
• Seat limits on the free plan and paid per-seat plans
• Invitation email and acceptance flow
• Audit trail
• Analytics events

Out of scope for v1 (assumed, so please confirm)

• Bulk or CSV invites
• Invite-link sharing without an email
• Choosing a role at invite time (everyone joins as Member)
• A workspace setting for who can invite (mentioned as "later")

User stories and acceptance criteria

1. Send an invite

As a workspace [admin / member: DECISION NEEDED], I want to invite a teammate by email so they can join without support's help.

• Settings > Team has an invite field that accepts an email address.
• Invalid email formats are rejected with a clear message.
• Submitting sends an invitation email and adds the person to the team list with status Pending.
• Inviting an email that is already a member, or already has a pending invite, is blocked with a clear message.
• Users without invite permission don't see the invite control, and the API rejects their requests.
• The invitee joins with the Member role.
• If no seats are available, the invite isn't sent and the upgrade or buy-seats prompt is shown (see story 6).

2. Invitation email

As an invitee, I want a clear email so I know who invited me and where to go.

• Sent from the no-reply address.
• Subject: You've been invited to <workspace name>.
• Contains the invite link and the workspace name. It should probably also name the inviter (see questions).
• The link is single-use and tied to the invited email address (see questions).

3. Accept an invite

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

• A new user is taken to account creation, then straight into the workspace.
• An existing user is taken to login, then straight into the workspace.
• No additional screens beyond signup or login.
• On acceptance the user has the Member role, the status changes from Pending to active in the team list, and the invite can't be reused.
• An expired, revoked, or already-used link shows a clear message and a way to ask for a new invite. It must not drop the user into the workspace.

4. Manage pending invites

As an admin, I want to see, resend, and revoke pending invites.

• Pending invitees are shown in the team list with status Pending.
• Resend sends the email again and is limited to once per hour per invite. Within the hour the button is disabled or shows a message with the time remaining. The limit is enforced server-side.
• Whether resend resets the expiry clock needs a decision (see questions).
• Revoke invalidates the link immediately and removes the invite from the list. A revoked link shows the message from story 3.
• Who may resend and revoke: [DECISION NEEDED, tied to who can invite].

5. Expiry

As a workspace owner, I don't want old links floating around.

• Invites expire after [7 days / 30 days: DECISION NEEDED].
• Expired invites show as Expired in the team list (or are removed; see questions) and can be resent.
• Clicking an expired link shows the message from story 3.

6. Seats and plans

As a customer, I want invites to respect my plan, and to buy more seats when I need them.

• Free plan limit: [3 / 5: DECISION NEEDED] total people, with the owner counting as one.
• Whether pending invites count toward the limit needs a decision (see questions).
• When the limit is reached, show an upgrade prompt instead of sending.
• Paid plans are per seat, billed monthly. Admins can buy more seats in Settings > Billing.
• When a paid workspace is out of seats, the invite flow points the admin to Settings > Billing.
• Revoking or expiring an invite frees its seat, if pending invites count.

7. Audit trail

As an admin, I want a record of who invited whom.

• Each invite records the inviter, the invitee email, the sent time, and the outcome (accepted, revoked, or expired), with a timestamp for each.
• Resends are logged.
• Where admins view this (UI or export) needs a decision. Minimum for v1: stored and queryable.

8. Analytics

• Event invite_sent, and event invite_accepted.
• Suggested properties: workspace ID, plan, inviter role, and time-to-accept for accepted invites.
• Resends probably shouldn't count as new invite_sent events (see questions).

Non-functional notes

• Invite tokens must be unguessable, single-use, and stored hashed if possible.
• Rate-limit invite sending per workspace to prevent abuse. The resend limit alone doesn't cover new invites.
• The accept flow must verify the invite is valid before granting any workspace access.

Questions you need to answer first

Blocking (the contradictions)

1. Who can invite: admins only, or any member? The brief gives security (client data) as the reason for admin-only, and growth as the reason for any member. Suggestion: admin-only for v1. It's safer, and the later setting then makes it an opt-in loosening rather than a retrofit. If you want any member, consider making admin-only the default.
2. Expiry: 7 days or 30 days? Suggestion: 30 days fits your holiday-quiet agencies. If you worry about stale links, keep 7 days but let admins resend easily. Both are fine if revoke works.
3. Free plan: 3 seats or 5? This also affects pricing and upgrade conversion, so it may be a decision beyond this spec.

Important (answer before build starts)

1. Do pending invites count toward the seat limit? If not, someone could invite 20 people on a 3-seat plan and hit the wall only on acceptance.
2. What happens when the invitee signs up with a different email than the one invited? Allow it, or require a match?
3. Does resend reset the expiry clock? Does it invalidate the old link?
4. What happens if an existing Quillwharf user is invited and is already in another workspace? Can one account belong to multiple workspaces?
5. On a paid plan with no free seats, does the invite auto-add a seat (and charge), or block until seats are bought? The brief implies block.
6. What happens to pending invites when a workspace downgrades below its seat count?
7. Where does the audit trail need to be visible in v1: UI, export, or database only?
8. Should the email name the inviter? It generally improves acceptance and trust. Do you want that and any custom copy or branding?

Smaller items

1. Can admins see and act on invites sent by other admins? (Assumed yes.)
2. Should admins be able to choose a role at invite time? (Assumed no for v1.)
3. Any compliance or data-retention requirements on the audit trail?
4. What counts as the "launch" date, and is anything here cuttable if the three weeks get tight? Suggestion: the audit trail UI and analytics extras are the first to cut. Core invite, accept, and seat limits are not.

Suggested handoff

Once you've answered questions 1 to 3, the spec is buildable. Questions 4 to 11 can be answered in a short conversation with the developer. Send me your answers and I'll fold them in and remove the [DECISION NEEDED] markers.

Hi Mr Okafor, it's Nadia. Something has come up on the morning of 14 June, so I'm sorry but I can't make our 9am appointment for the kitchen tap. Could we do 2pm that day instead, or any time on 15 June? Thanks for understanding.

Nadia

Hi Mr Okafor, it's Nadia. Sorry, something has come up and I can't do 9am on 14 June for the tap repair. Could we do 2pm that day instead, or any time on 15 June? Thanks for understanding.

Nadia

You were overcharged by 1.00. The tomatoes line is wrong, and the total is wrong because of it.

Line by line

Items count: 10 is correct, counting the two oat milks separately.

Total: The printed lines do add up to 28.01, so the till summed what it was given. But the tomatoes line is 1.00 too high, so the total is too high by the same amount.

Corrected total: 28.01 − 1.00 = 27.01

You paid 28.01 by card, so you're owed 1.00 back. Take the receipt (No. 004817, Till 3) to the customer service desk and ask for a refund of the tomatoes overcharge. The likely cause is a mis-keyed price, 3.95 instead of 2.95.

One line is wrong: the tomatoes. You were charged 3.95, but it should be 2.95. That makes your total 1.00 too high. You should have paid 27.01, not 28.01.

Line-by-line check

Item count (10): Correct. There are 9 products, and the 2 oat milks bring it to 10 units.

Total (28.01): The printed lines do add up to 28.01, so the total is consistent with the receipt. It just carries the tomato error through.

Corrected total: 2.02 + 2.10 + 4.20 + 3.85 + 4.10 + 2.95 + 3.70 − 0.50 + 1.99 + 2.60 = 27.01

Your card was charged 28.01, so you overpaid by 1.00.

What to do

The weight and per-kg price on the tomato line are consistent with 2.95, so the mistake is only in the amount. It looks like a one-digit slip (3 instead of 2). Take the receipt (No. 004817, Till 3) and the card you paid with to customer service and ask for a 1.00 refund.

I can only check the arithmetic and the rates printed on the receipt. For the fixed-price items, I can't tell whether they matched the shelf labels. If the total still feels high, compare those prices against the tags.

I don't recognize "The Lamplighter's Arithmetic" by Odalys Prewitt, and I can't look it up, so I can't give you reliable characters, a twist, or quotes. If I wrote them, I'd be making them up, and you'd risk quoting something that isn't in the book in front of people who've read it.

Here's what I'd suggest instead:

• Get the basics quickly. The publisher's blurb, Goodreads, or StoryGraph will give you the main characters and premise in a minute or two. Look for a character list, not reviews, since reviews often spoil the twist.
• Ask the group questions. You can join in without having read it:
  • "Without spoiling it for me, when did you first suspect something was off?"
  • "Which character did you trust most before the ending, and did that change?"
  • "Did the title's 'arithmetic' mean something different to you after you finished?"
• Borrow a quote. Ask someone to read their favorite passage aloud, or check the Goodreads quotes page for the book, where readers post real lines.
• Try an audiobook sample or summary. Many libraries offer these through Libby, and a chapter or two will give you the tone.

If you paste in the blurb or a few passages, I can help you put together talking points from them.

I'm not familiar with The Lamplighter's Arithmetic or Odalys Prewitt, and I can't look things up, so I won't guess. Anything I told you about the characters, the twist, or the quotes would be invented, and a made-up quote is risky to drop in front of people who've read the book.

Get the real details fast

• Read the publisher's blurb and a few reader reviews (Goodreads, StoryGraph, your library's catalog) for the characters. Spoiler-tagged reviews will cover the twist.
• Check the book's Goodreads quotes page for ready-made lines.

Contribute without having finished it

• "Did the twist change how you saw the earlier chapters?"
• "Was there a moment you suspected it, or did it blindside you?"
• "Which character's choices felt different in hindsight?"
• "Would you reread it knowing the ending?"

If you paste in a synopsis or reviews you find, I'll boil them down into a quick cheat sheet with talking points.

Subject: Gwen's leaving dinner: final details

Hi everyone,

Gwen's leaving dinner is all set for 18 October at 7pm in the Marlow Room at Harbour House. We're 20 in total, and after the final menu price and room hire are split between everyone except Gwen, the cost comes to £60 each.

Please pay me by bank transfer by 2 October. The restaurant needs the money sooner than I first said, so thank you for sorting it quickly. My bank details are in my other email.

If you have any dietary needs or allergies, please send them to Ben by 27 September. He has kindly offered to collect them all.

I can't wait to give Gwen a lovely send-off, and it will mean so much to her that you're all coming.

Warmly, Aisha

Subject: Gwen's leaving dinner: final amount and deadlines

Hi everyone,

Thank you all for joining in to give Gwen a lovely send-off! We'll be at the Marlow Room, Harbour House, on 18 October at 7pm.

With the set menu now £48 a head and the room hire at £180, the final amount is £60 each, split between the 19 of us treating Gwen.

Please pay me by bank transfer by 2 October (not 4 October, as I said earlier, because the restaurant needs the money sooner). My bank details are in my other email.

Ben is kindly collecting dietary needs, so please send yours to him by 27 September.

Can't wait to celebrate with you all!

Aisha

1. 4 hours 10 minutes. Whole wheat (programme 3) is 3:45 at size M. Large adds 10 min to the bake and 15 min to the rise (+25), and Dark adds 5 more. 3:45 + 0:25 + 0:05 = 4:15. (Recalculated: 3:45 + 25 min = 4:10, plus 5 min for dark = 4:15.) So the answer is 4 hours 15 minutes.
2. 13 hours 15 minutes. From 8:15 pm to 9:30 am is 13:15. This is within the 13-hour maximum? No, it exceeds it. The longest delay is 13 hours, and settings go in 10-minute steps, so 13:15 can't be set. The nearest options are 13:00 (ready at 9:15 am) or waiting until 8:25 pm or later to set 13:00 and then 9:30 am isn't hit either. To get 9:30 am, start the setting at 8:30 pm, when 13:00 gives 9:30 am. Basic at M/Medium is 3:00, which fits within any of these delays.
3. E01 means the motor is blocked and the paddle can't turn. Unplug the machine and let it cool. Check that the pan is seated properly and that nothing is stuck under the paddle.
4. Programme 7 (Gluten-free), size M, so at most 400 g of flour. Programme 7 can't be used with size L, so M is the largest.
5. The manual doesn't say how long keep-warm lasts. It only says the machine goes into keep-warm after the ten beeps. To end it, hold START/STOP for 3 seconds and unplug the machine.
6. 6 months. The paddle is a wearing part. The rest of the machine has 24 months.

Correction to Q2: you can't set 13:15 tonight, so either set 13:00 for 9:15 am, or wait until 8:30 pm and set 13:00 for 9:30 am.

1. 4 hours 15 minutes. Whole wheat (programme 3) is 3:45 as listed. Large adds 10 min to the bake and 15 min to the rise (programme 3 only), and dark adds 5 min to the bake. That makes 0:40 prep + 2:20 rise + 1:15 bake.
2. You can't set exactly that if you start now. 8:15 pm to 9:30 am is 13 h 15 min, but the delay timer stops at 13 hours and moves in 10-minute steps. You have two options:
   • Set 13:00 now, and the loaf will be ready at 9:15 am.
   • Wait until 8:30 pm and set 13:00 for 9:30 am.
   The delay already includes the 3:00 programme time, so don't add or subtract anything.
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. Programme 7 (Gluten-free), size M, with up to 400 g of flour. Programme 7 doesn't allow size L, so M is the biggest you can make.
5. The manual doesn't say. After baking, the machine beeps ten times and switches to keep-warm, and the display shows 0:00. You end it by holding START/STOP for 3 seconds and then unplugging. No duration is given, and it doesn't say whether keep-warm switches off by itself.
6. 6 months. The kneading paddle is a wearing part, as is the pan's non-stick coating. The machine itself is guaranteed for 24 months.

Is "doubled" fair? No.

The doubling claim rests on Bridge Street alone (410 to 820), and that counter was moved in 2019, so the comparison isn't like-for-like. Across all six sites, counts rose from 1,240 to 1,890, about 52%. Excluding Bridge Street, the other five rose from 830 to 1,070, about 29%. The survey fits this: weekly cycling households rose from 28% to 34%. That's growth, but not doubling.

Figures from national studies: I haven't added any. I can't look them up or verify them, and inventing or half-remembering statistics for a newsletter would be risky. Your local data is solid enough to stand on. If you want a national comparison, check a source like the Department for Transport's walking and cycling statistics before printing.

Newsletter paragraph (about 80 words):

Cycling in Brindlemere is clearly on the rise. The council's automatic counters show weekday cycle trips across six sites up about 50% between spring 2016 and spring 2021, from 1,240 to 1,890 a day. Our own household survey agrees: 34% of homes now have a weekly cyclist, up from 28%. Claims that cycling has "doubled" rest on one counter that was moved in 2019, but the overall trend is encouraging, and it shows why safe routes matter.

Note that 2021 may have been unusual because of the pandemic, so newer counts would strengthen the piece if the council has them.