Interview Bank

From Frontend Engineer to Agent Engineer in 30 Days

D8 Why Split Gateway and Worker; Postgres Table Design (sessions/runs/messages) + Drizzle

• With at-least-once delivery, how do you guarantee a redelivered message does not create two runs?消息总线是至少一次投递，同一条消息被重复投递时，怎么保证不会产生两条 run？

  Common in ChinaCommon overseasDeep dive#idempotency#database#reliability

  How to reason about it · think before answering

  1. This question is about which layer idempotency lives in. Anyone who answers 'check whether it exists, then insert' has usually just failed it — that is exactly the answer being screened out.
  2. State the premise: duplicates are not accidents. The bus is at-least-once, clients retry on timeout, users double-click. The same message arriving twice is certain, so the goal is not to prevent duplicates but to make duplicates produce the same result.
  3. Then derive the key: idempotency needs a key derived from request content. A random UUID differs every time and buys nothing; hash the session id, the client message id and the message body together, falling back to content plus a coarse time bucket when the client has no id.
  4. Land it in storage: put a unique constraint on that column in the runs table, write the insert as on-conflict-do-nothing, and when it returns zero rows read back the existing run and return the same run id. Two requests, one run, one id.
  5. Explain why check-then-insert fails, which is the whole point: two gateway instances can query, both see nothing, and both insert. The window between the two statements cannot be closed in application code, it is too narrow to reproduce under load tests, and it leaks a few bad rows every day in production. The database's unique constraint has to be the final arbiter; the application-level check only saves a wasted insert.
  6. Expect the follow-up: what about duplicate execution on the consumer side? The unique constraint gives you one run, but a worker can still receive it twice, so status changes need conditional updates (move to running only if the current status is pending) plus an explicit transition whitelist that blocks a finished run from being pushed back to running and overwriting a reply the user already saw.

  分析过程 · 先想清楚再作答

  1. 这题在考幂等的落点在哪一层。凡是答「在代码里先查一下有没有，没有再插入」的，基本当场结束——因为那正是这题想筛掉的答案。
  2. 先把前提摊开：重复不是意外。总线是至少一次语义、客户端会超时重发、用户会手抖双击，同一句话到达两次是必然事件。所以设计目标不是「避免重复到达」，而是「重复到达时结果相同」。
  3. 然后给推导：幂等需要一个由请求内容决定的键。随机 UUID 每次都不同，等于没有幂等；正确取法是把会话 id、客户端消息 id、消息内容拼起来做哈希，客户端没有消息 id 时退用内容加一个粗粒度时间窗。
  4. 结论落在存储层：在 runs 表的这一列上加唯一约束，插入写成「冲突就什么都不做」，返回零行时回查那条已有的 run，把同一个 runId 返回给用户。两次请求、一条 run、一个 runId。
  5. 解释为什么「先查后插」不行，这是本题的分水岭：两个 Gateway 实例可以同时查、同时发现没有、同时插入，这两步之间有一个应用层拦不住的时间窗；它窄到压测复现不出来，上线后每天漏几条。**幂等的最终裁判必须是数据库的唯一约束**，应用层的判断只是为了少一次插入尝试。
  6. 可以预期的追问：那消费侧的重复执行呢？答：唯一约束保证了只有一条 run，但 Worker 可能重复拿到同一条 run，所以状态迁移也要带条件更新（只有当前状态是 pending 时才能改成 running），并且用一个显式的迁移白名单挡住「已完成的 run 被推回运行中」这种会覆盖用户已收到回复的情况。

  Key points

  • Redelivery is certain, so the goal is identical outcomes on duplicates, not preventing duplicates
  • The idempotency key must be derived from request content — session id plus client message id plus body, hashed; a random UUID buys nothing
  • Put a unique constraint on that column, insert with on-conflict-do-nothing, and read back the existing run when zero rows return
  • Check-then-insert races under concurrency; the window between the statements cannot be closed in application code, so the unique constraint must be the final arbiter
  • On the consumer side add conditional status updates and a transition whitelist so a finished run is never re-run or overwritten

  答题要点

  • 重复投递是必然事件，设计目标是「重复到达时结果相同」，不是「避免重复」
  • 幂等键必须由请求内容决定：会话 id 加客户端消息 id 加内容做哈希，随机 UUID 等于没有幂等
  • 在 runs 的幂等键列上建唯一约束，插入用「冲突就什么都不做」，零行时回查已有 run 返回同一个 runId
  • 先查后插在并发下必然出双份，两条语句之间的时间窗应用层拦不住，幂等的最终裁判是数据库唯一约束
  • 消费侧还要用条件更新加状态迁移白名单，避免同一条 run 被重复执行或把已完成的回复覆盖掉
• How would you design primary keys and indexes for sessions, runs and messages, and why avoid auto-increment ids?sessions / runs / messages 这三张表你会怎么设计主键与索引？为什么不用自增主键？

  Common in ChinaCommon overseasIntermediate#database#schema-design#idempotency

  How to reason about it · think before answering

  1. It looks like a trivia question, but every choice sits on a concrete constraint. The test is whether you can say what breaks if you choose otherwise.
  2. Start with why three tables rather than one: the grains differ. A session is a long-lived container, a run has a lifecycle and can fail and be retried, a message is an immutable fact. Without the run layer there is nowhere to answer 'did this finish', 'should we retry', or 'what did this turn cost'.
  3. Use text primary keys generated in the application (UUID or ULID), because the gateway must put the id into the 202 response before the row is written. Auto-increment ids are only known after the insert, which parks a round trip in the user's wait path and cannot be pre-allocated across instances. A bonus is that sharding later needs no renumbering.
  4. Index by query path, not by instinct: sessions need an index on user_id to list a user's conversations, messages need one on session_id to load history, and foreign key columns need indexes or deleting a parent row triggers a full scan. Extra indexes are not free — each one slows writes.
  5. The two unique constraints carry the design: a unique idempotency key on runs blocks duplicate delivery, and a composite unique on run id plus sequence in messages both fixes output ordering for one run and lets a reconnect replay idempotently by sequence. The sequence must start at zero and never skip, otherwise resume cannot find the cut point.
  6. Expect the follow-up: ULID or UUIDv4? Choose ULID or UUIDv7 — they are time-ordered so inserts land at the right edge of the B-tree, whereas random UUIDv4 scatters writes, splits pages and hurts cache hit rates. Mentioning this shows you have watched write performance.

  分析过程 · 先想清楚再作答

  1. 这题看着像八股，其实每一个选择背后都有一个具体约束。判断标准是：你能不能为每个决定说出「不这么做会发生什么」。
  2. 先讲为什么是三张表而不是一张：粒度不同。会话是长期容器，一次执行有生命周期且可能失败重来，消息是不可变事实。少了「一次执行」这一层，你就没有地方回答「这次跑完没有」「该不该重试」「这轮花了多少钱」。
  3. 主键选文本型的应用侧 id（UUID 或 ULID），理由是接入层必须在写库之前就把 id 放进 202 响应体返回给客户端；自增主键要等数据库插完才知道值，那次往返就被卡在用户的等待路径上，而且多实例无法预分配。附带好处是将来分库分表不用重编号。
  4. 索引按查询路径建，不按直觉建：按用户拉会话列表要 sessions 的 user_id 索引，按会话拉历史要 messages 的 session_id 索引，外键列本身要索引否则删除父行会全表扫。多余的索引不是免费的，每个都让写入变慢。
  5. 两条唯一约束才是这套设计的灵魂：runs 的幂等键唯一，挡住重复投递；messages 的「run id 加序号」复合唯一，既保证同一次执行的输出顺序稳定，又让断线重连可以按序号幂等回放。序号要从 0 开始、连续、不跳号，否则续传就找不到断点。
  6. 可以预期的追问：ULID 和 UUIDv4 选哪个？答 ULID 或 UUIDv7——它们按时间有序，插入时集中在 B 树右端，不像 UUIDv4 那样随机分布导致页分裂和缓存命中率下降。这个细节能直接体现你关心过写入性能。

  Key points

  • Three tables for three grains: a long-lived session, a run with a lifecycle, and immutable messages; without runs you cannot answer completion, retry or cost questions
  • Application-generated text ids, because the gateway must return the run id in the 202 before the write, and auto-increment ids cannot be pre-allocated across instances
  • Index the real query paths — user_id on sessions, session_id on messages, plus foreign key columns; extra indexes slow writes
  • Two unique constraints carry the design: a unique idempotency key on runs, and a composite unique on run id plus sequence in messages for ordering and idempotent replay
  • Prefer time-ordered ids such as ULID or UUIDv7 over random UUIDv4 to avoid page splits and cache misses

  答题要点

  • 三张表对应三种粒度：会话是长期容器、run 是一次有生命周期的执行、message 是不可变事实；少了 run 就无法回答是否跑完、该不该重试、花了多少钱
  • 主键用应用侧生成的文本 id，因为 Gateway 要在写库之前把 runId 放进 202 响应里，自增主键必须等插入完成且无法跨实例预分配
  • 索引按实际查询路径建：sessions 的 user_id、messages 的 session_id、以及外键列；多余索引会拖慢写入
  • 两条唯一约束是灵魂：runs 的幂等键唯一挡重复投递，messages 的「run id 加序号」复合唯一保证保序与幂等回放
  • id 优先选 ULID 或 UUIDv7 这类时间有序的方案，避免随机 UUID 造成的页分裂与缓存失效