Aggregate Intelligence Software Engineer Interview Guide

The 60-second brief

Candidates describe Aggregate Intelligence as a company whose products take large volumes of data, aggregate it and turn it into information clients use to make decisions. The Software Engineer posting centres on backend services and algorithms behind its core product offerings, query optimisation, code reviews, architecture discussions, production troubleshooting and documentation of the systems you own. Python is the language the posting names, and experience with SQL or NoSQL databases is listed alongside it.

This guide is for candidates interviewing for a Software Engineer role at Aggregate Intelligence, including those whose experience comes from internships, which the posting accepts alongside professional work. It covers the five reported technical questions, the Python, OOPs and database fundamentals candidates say are expected, the reported group discussion and HR stages, and how to prepare to defend every item on your resume. It does not claim a round order, because none has been reported.

Candidates describe a progression from objective assessments through technical interviews to a final HR stage, with a group discussion among the stages. No round count, order or duration is reported, so confirm the format with your recruiter.

Focus areas

• Python
• Object-Oriented Programming (OOP)
• Coding / Programming Skills

Overview

The reported work is backend engineering: services and algorithms behind a data product, queries that need tuning, code reviews and production troubleshooting. The five reported questions follow that shape. Explain OOPs and how you applied it, compare database indexing strategies, debug a performance bottleneck in production, walk through the time complexity of your latest algorithm, and keep code maintainable as requirements change. Three of the five ask you to draw on your own projects or code, so generic textbook answers will not be enough: each answer needs a concrete example from something you built.

Candidates report Python, OOPs and database management as the expected core, with SQL or NoSQL experience listed in the requirements. The evaluation areas candidates describe are programming logic written to be optimised, database design (normalisation, schemas, query optimisation) and multi-threading, concurrency and API design patterns. Two example scenarios are reported: design a schema for a user-activity tracking system, and optimise a code snippet for better time complexity. Prepare both as rehearsed skills, in Python, with the data structure or index you would choose and the reason.

Communication is reported as its own theme. Candidates mention a group discussion, explaining technical ideas to non-technical stakeholders, discussing past projects in detail, and being asked about any technology on the resume. Some candidates also report being asked to solve problems on paper or a whiteboard without an IDE. That means practising away from an editor, narrating assumptions and trade-offs aloud, and auditing your resume so that every line is something you can explain.

Where to focus your preparation

No round sequence is published, so the single entry below combines what candidates describe across the assessment, group discussion, technical and HR stages.

Preparation focus

Candidates describe a process that starts with objective assessments and ends with technical and HR interviews, with a group discussion mentioned as one of the stages. The technical conversation is reported to cover OOPs, database management, Python, algorithm complexity, production debugging and past projects, and some candidates report solving problems on paper without an IDE. The order, number and length of stages are not reported, so ask your recruiter which apply to you and whether coding is on paper or on a machine. Then prepare each reported question aloud with a project example behind it, and practise explaining your reasoning to a listener who may not be technical.

What to demonstrate

• Command of OOPs and database fundamentals, which candidates report as the expected core
• Python proficiency and the ability to write and optimise code by hand
• Clear explanation of assumptions, approach and trade-offs while solving
• Ability to explain every technology and project listed on your resume

How to prepare

• Rehearse the five reported questions aloud: OOPs applied in projects, indexing strategies, production performance debugging, complexity of your latest algorithm, and maintainable code
• Write a user-activity tracking schema with indexes, and rewrite one slow snippet to a better complexity, both on paper
• Audit your resume and write why you chose each technology, so any line can be defended
• Ask the recruiter which stages apply, whether any coding is without an IDE, and whether a group discussion is included

Where candidates lose points

Reciting the OOPs pillars as definitions with no example from your own code

For encapsulation, inheritance, polymorphism and abstraction, pick one real class or module you wrote and say what it hid, what it extended or what interface it satisfied. Include one place you chose composition over inheritance and why, since inheritance misuse is the usual follow-up.

Answering the indexing question with 'indexes make queries faster'

Name the structures: B-tree indexes for equality and range, hash indexes for equality only, clustered versus non-clustered, covering indexes, and composite indexes that serve a leftmost prefix. State the costs (slower writes, extra storage, low-selectivity columns gain little) and say you would confirm with EXPLAIN.

Proposing fixes for a production slowdown before measuring anything

Give an ordered sequence: confirm the symptom with metrics, find what changed, locate the hot spot with a profiler, slow-query log or EXPLAIN, check locks, connection pools and thread contention, change one thing, then verify against the original metric.

Quoting a complexity for your own algorithm that you cannot justify, or ignoring hidden costs

Write the algorithm out on paper before the interview and derive time and space line by line. Check for hidden linear work such as membership tests on a Python list, sorting inside a loop or building strings with repeated concatenation, and know the faster dict, set, heapq or join alternative.

Listing resume technologies you cannot explain in depth

Candidates report that any resume detail can be asked about. For each project, prepare the architecture, one challenge, one decision with the alternative you rejected and the outcome. Remove or downplay any skill you could not defend for several minutes.

Questions & practice

Can you walk us through the time complexity of your most recent algorithm implementation?

Medium · Technical Foundations and Core Subjects

Approach

1. Choose a real implementation you can explain line by line, and define your variables before giving a Big-O: what n is, any second dimension (m, k), and the input sizes it actually ran on.
2. Walk the code structurally: nested loops multiply, sequential steps add, and recursion follows its recurrence (for example, T(n) = 2T(n/2) + O(n) gives O(n log n)). Report time and space separately.
3. Account for the hidden costs of Python built-ins: x in list is O(n) but O(1) on average for a set or dict; list.pop(0) is O(n) while collections.deque.popleft() is O(1); sorted() is O(n log n) Timsort; repeated string concatenation in a loop can turn quadratic, and ''.join is linear.
4. Distinguish best, average, worst and amortised cases: a hash lookup is O(1) on average but O(n) when keys collide, and list.append is amortised O(1) because of occasional resizes.
5. Finish with the optimisation you made or would make, plus its trade-off: replacing a nested pairwise scan, O(n^2), with a dict lookup, O(n) time and O(n) extra memory; or heapq for a top-k query in O(n log k) instead of sorting everything.

Follow-up

• How does the complexity change if the input is already sorted, or if every key hashes to the same bucket?
• Could you cut the memory usage, and what would it cost in running time?
• How would you confirm the complexity empirically rather than by reading the code?

Canonicalise a request body into a stable idempotency fingerprint

Medium · parsing · canonicalisation · hashing

idempotency_key.request_fingerprint is a SHA-256 over the method, path and canonicalised body, and a retry whose fingerprint differs must be rejected with 422 rather than served the stored response. Write the canonicaliser. Bodies are JSON up to 256 KB nested at most 32 levels; clients vary key order, whitespace and unicode escaping, and some send 64-bit ids as JSON numbers. Produce a deterministic byte string such that semantically identical bodies match and any semantic difference does not. State your complexity and name two normalisations you refuse to perform.

Approach

1. Parse once into a tree, then re-serialise under fixed rules: object keys sorted, array order preserved, one escaping convention, no insignificant whitespace. Parsing is O(n) and sorting keys is O(k log k) per object, so O(n log n) overall with O(depth) stack, and the 32-level cap is enforced during parsing because hostile nesting is how a canonicaliser becomes a stack overflow.
2. Sort keys by their UTF-8 bytes and say why the obvious implementation is wrong in some runtimes: a default string comparison that orders by UTF-16 code units places surrogate pairs, meaning code points from U+10000 up, below U+E000 to U+FFFF, which is not UTF-8 byte order, so two services written in different languages disagree on the same document.
3. Do not re-encode numbers through a double. IEEE-754 binary64 represents integers exactly only up to 2^53, so normalising a 19-digit id through a float changes it, and 1 against 1.0 cannot be reconciled without deciding whether they are the same value. Preserve the literal token, and require ids as strings at the API boundary if you want them comparable.
4. Reject duplicate keys rather than picking one. JSON permits them and parsers disagree, most keeping the last, so any choice you make ties the fingerprint to a parser detail that the code handling the request does not necessarily share.

Follow-up

• A client sends the same logical request with an extra field your API ignores. Same key, different fingerprint, so you return 422. Is that the right answer?
• Where does the fingerprint get computed relative to request decompression and the body-size limit?

Identify the heaviest tenants in a five-minute window under memory pressure

Medium · top-k · heavy hitters · streaming

The edge service handles about 3,000 requests per second across roughly 50,000 tenants, peaking near 9,000. Expose the 50 heaviest tenants by request count over the trailing five minutes so limits can be tightened before one tenant's backfill starves the fleet. You may not retain five minutes of raw records. Give the exact solution and its memory, then the bounded-memory approximation with its error stated as a formula, and say which you would ship and at what tenant cardinality that choice changes.

Approach

1. Do the exact version first, because it is affordable at this cardinality: a ring of 300 one-second counters per tenant, advanced lazily, is 1,200 bytes of counters per tenant and roughly 60 to 90 MB for 50,000 tenants with overhead. Carry a running total and subtract the bucket you overwrite so a window read is O(1) rather than 300 adds.
2. Extract the top 50 with a size-k min-heap over the tenant sums: O(d log k) for d tenants, against O(d log d) to sort them all. Maintaining the heap continuously instead of on query requires a tenant-to-heap-index map, because incrementing a count already inside the heap means sifting from a known position, and without that map you rebuild the heap on every request.
3. State the approximation precisely rather than gesturing at sketches. Misra-Gries with m counters retains every item whose true count exceeds N/(m+1), and each retained count underestimates the truth by at most N/(m+1). With m = 1,000 and N = 900,000 requests in the window the error is roughly 900 requests, which is fine for spotting a tenant sending 50,000 and useless for ranking two tenants 200 apart.
4. Say what breaks when the window slides: Misra-Gries and Space-Saving are insert-only and cannot be decremented as records age out. The workable construction is one summary per sub-window, say ten seconds, with 30 summaries merged at query time, and the merged error is the sum of the per-summary errors, so the bound degrades linearly in the number of sub-windows.

Follow-up

• The heaviest tenant is heavy because of one export job rather than user traffic. Should the limiter treat those as the same tenant?
• Two tenants sit tied at the boundary of the top 50. Does your answer flap, and does the flapping matter?

Write the update path that detects a concurrent edit

Medium · optimistic concurrency · transactional outbox · audit history

resource carries version INT NOT NULL DEFAULT 1. resource_revision holds revision_id, resource_id, version, actor_user_id, change_kind, patch JSONB, request_id, created_at with UNIQUE (resource_id, version). outbox_event holds aggregate_type, aggregate_id, aggregate_version, event_type, payload, status. A PUT carries the version the client read. Write the exact statements for the single transaction that applies the edit, records the revision and enqueues 'resource.updated', and give the handler's branch on zero affected rows. Then say what PostgreSQL 16 does under READ COMMITTED when two of these updates hit one row at once.

Approach

1. One transaction, three writes, no network call inside it: UPDATE resource SET title = $3, version = version + 1, updated_at = now() WHERE resource_id = $1 AND tenant_id = $4 AND version = $2; then INSERT the resource_revision row at version $2 + 1; then INSERT the outbox_event row at the same aggregate_version. The event goes to a table rather than a broker because no transaction spans both.
2. Branch on the affected-row count before doing anything else. Zero has three causes — stale version, wrong tenant, row gone — so re-read once and map to 409 carrying the current version, or 404 for an id outside the caller's tenant, which also stops the endpoint confirming that another tenant's id exists.
3. State the engine behaviour instead of assuming it. Under READ COMMITTED the second UPDATE blocks on the row lock, and when the first commits PostgreSQL re-evaluates the WHERE clause against the newly committed row, so the version predicate now fails and the statement reports zero rows. Under REPEATABLE READ the identical collision raises SQLSTATE 40001 instead, so the handler must fold both shapes into one conflict response.
4. Keep UNIQUE (resource_id, version) even though the predicate already serialises writers. It is what makes a lost update unwritable if any other path ever reaches the revision table, and it converts a logic bug into 23505 rather than into a silently missing history row.

Follow-up

• A client sends the version it read ten minutes ago and the resource has moved three versions. What is in your 409 so it can resolve the conflict without a full re-fetch?
• Two editors, two disjoint fields, no overlap. Does your answer still refuse the second write, and should it?

Find version gaps and relay lag with window functions

Medium · window functions · event tables · frame semantics

outbox_event holds event_id, aggregate_type, aggregate_id, aggregate_version, event_type, payload, status ('pending','published','dead'), attempts, created_at, published_at. A projection is missing rows and you must decide whether the relay skipped events or the consumer dropped them. Write three queries over the last seven days: one listing every aggregate_id whose published aggregate_version sequence has a hole, one giving per-day counts with a running total, and one returning the newest published event per aggregate. For each, say where the window function is evaluated relative to WHERE and LIMIT. PostgreSQL 16.

Approach

1. Gaps: compute lead(aggregate_version) OVER (PARTITION BY aggregate_id ORDER BY aggregate_version) in a subquery, then filter next_version <> aggregate_version + 1 in the outer query. Window functions are evaluated after WHERE, GROUP BY and HAVING and before the outer ORDER BY and LIMIT, so the predicate cannot sit in the same WHERE clause and PostgreSQL 16 has no QUALIFY.
2. Say what the seven-day filter does to the answer: it truncates every partition, so the first row per aggregate has no predecessor inside the window and a hole spanning the boundary is invisible. Widen the window, or join to resource.version as the authority for the true maximum.
3. Running total: SELECT date_trunc('day', created_at) AS d, count() AS n, sum(count()) OVER (ORDER BY date_trunc('day', created_at) ROWS UNBOUNDED PRECEDING). An aggregate inside a window call is legal because grouping runs before windowing. The grouping key is unique per row here so ROWS and RANGE agree, but write the frame anyway — over ungrouped rows with tied timestamps the default RANGE frame pulls in every peer row and the total jumps.
4. Newest per aggregate: DISTINCT ON (aggregate_id) ... ORDER BY aggregate_id, aggregate_version DESC is the cheap PostgreSQL-only form when an index matches that order; row_number() OVER (PARTITION BY aggregate_id ORDER BY aggregate_version DESC) = 1 is the portable form and needs a subquery for the same evaluation-order reason as the gap query.

Follow-up

• Relay failover redelivers events. Does a duplicate break the gap query, and how would you detect one from this table alone?
• Turn the gap check into a continuous monitor rather than a query someone runs after an incident. What does it watch?

Can you explain the core OOPs concepts and how you have applied them in your projects?

Medium · Technical Foundations and Core Subjects

Approach

1. Name the four concepts and tie each to a class you actually wrote: encapsulation (the object owns its state and exposes methods), abstraction (callers see an interface, not the internals), inheritance (reuse through an is-a relationship), polymorphism (one call, different behaviour per type).
2. Show the Python specifics: privacy is by convention (a single underscore), a double-underscore attribute is name-mangled to _ClassName__attr rather than truly hidden, and @property lets you validate on assignment without changing the caller's syntax.
3. For abstraction and polymorphism, use abc.ABC with @abstractmethod: instantiating a subclass that leaves an abstract method unimplemented raises TypeError. Mention duck typing and typing.Protocol as the structural alternative to a shared base class.
4. For inheritance, explain super() and the method resolution order (C3 linearisation, visible in ClassName.mro), then say when you chose composition instead: a has-a relationship, or a subclass that would override a parent method only to disable it.
5. Close with the project payoff: an if/elif chain on a type string replaced by polymorphic classes, so adding a new type meant adding a class without editing callers. Flag one pitfall you avoided, such as a mutable list defined as a class attribute and shared by every instance.

Follow-up

• When did you choose composition over inheritance, and what problem did inheritance cause the last time you used it?
• What is the difference between an instance method, a @classmethod and a @staticmethod in Python, and when would you use each?
• How does Python decide which parent's method runs in a diamond-shaped inheritance hierarchy?

What are the primary differences between various database indexing strategies?

Medium · Technical Foundations and Core Subjects

Approach

1. Start with the B-tree, the default in PostgreSQL and MySQL InnoDB: O(log n) lookups that serve equality, range predicates, ORDER BY and prefix matches like LIKE 'abc%', but not a leading wildcard like LIKE '%abc'. Contrast a hash index, which serves equality only.
2. Explain clustered versus secondary indexes: InnoDB stores rows in primary-key order and each secondary index leaf holds the primary key, so a secondary lookup traverses two trees; PostgreSQL tables are heaps and every index is secondary. A covering index (INCLUDE columns in PostgreSQL 11+) enables index-only scans.
3. Cover composite indexes and the leftmost-prefix rule: an index on (user_id, created_at) serves WHERE user_id = ? ORDER BY created_at, but not a filter on created_at alone. Put equality columns before the range column.
4. Name the specialised types and their use cases: partial indexes (WHERE status = 'active'), expression indexes (lower(email)), GIN for jsonb, arrays and full-text search, BRIN for very large append-only tables ordered by time, and LSM-tree storage in write-heavy NoSQL stores.
5. State the trade-off: every index slows INSERT, UPDATE and DELETE and costs storage, and the planner often skips an index on a low-selectivity column. Verify with EXPLAIN ANALYZE before and after adding one.

Follow-up

• For a user-activity tracking table, which indexes would you create and which queries justify each one?
• Why might the query planner ignore an index you just created?
• How does one composite index on (a, b) differ from two single-column indexes on a and b?

How do you ensure your code remains maintainable and scalable as project requirements evolve?

Medium · Technical Foundations and Core Subjects

Approach

1. Describe the structure you use: keep domain logic separate from I/O (the database, HTTP, files), give each module one responsibility, and inject dependencies so a storage or API client can be swapped without touching business rules. In Python, abc.ABC or typing.Protocol defines the seams.
2. Make tests the safety net for change: unit tests on the domain logic, integration tests at the database boundary, and CI that runs them along with a linter and a type checker (for example ruff and mypy over type hints), so a refactor fails fast.
3. Plan for evolving contracts: version public APIs, and make schema changes backward compatible with expand-then-contract (add a nullable column, backfill it, switch reads, then drop the old column). Use feature flags to ship behaviour separately from deploys.
4. Address scale separately from maintainability: keep services stateless so they scale horizontally, move slow work onto a queue, add caching only with an explicit invalidation rule, and measure before optimising.
5. Show judgement about abstraction: wait for the second or third concrete case before generalising, and tell one story from your own project where a requirement change was cheap or expensive, and why.

Follow-up

• Tell me about a requirement change that was expensive in your code. What would have made it cheap?
• How would you rename a database column that a live service reads, without downtime?
• When does an abstraction become a liability rather than a help?

How do you approach debugging a performance bottleneck in a production environment?

Medium · Technical Foundations and Core Subjects

Approach

1. Quantify the symptom before theorising: which endpoint or job, p50 versus p99 latency, throughput and error rate, and when it started. Correlate the start with deploys, traffic changes, configuration changes and data growth.
2. Separate working from waiting: check CPU, memory, disk and network I/O, connection-pool usage and lock waits. A slow request with idle CPU points to I/O, locks or pool exhaustion, not to the code's arithmetic.
3. Narrow down with tracing spans, then profile the hot path. In Python, a sampling profiler such as py-spy attaches to a running process without a restart; cProfile suits a local reproduction. On the database side, use the slow-query log or pg_stat_statements, then EXPLAIN ANALYZE, and look for ORM N+1 query patterns.
4. Test one hypothesis at a time: reproduce at production-like data volume, apply the fix (an index, batching, caching or removing the N+1), and confirm it against the same metric you started with. Roll it out gradually so a regression stays contained.
5. Prevent a repeat: add an alert on the metric that degraded, a load or regression test for the path, and a short written postmortem naming the root cause, not just the trigger.

Follow-up

• The p99 latency doubled but the average barely moved. What does that suggest, and where do you look first?
• How would you profile a Python service in production without restarting it or adding much overhead?
• Your fix made the endpoint faster but database CPU went up. What do you do next?

Suggested practice checklist

The plan follows the reported questions and evaluation areas: OOPs in Python, database indexing and schema design, complexity and on-paper coding, production debugging with concurrency, maintainability and project stories, then a paper-only mock with a group-discussion rehearsal.

Day 1 — Confirm the format and audit your resume

• Email your recruiter to ask which stages apply (assessment, group discussion, technical, HR), whether any coding happens without an IDE, and whether there is a take-home.
• List every technology, project and metric on your resume and write one sentence each on why you used it and what you would change now.
• Mark the three weakest items and decide whether to study each one or cut it.

Deliverable: A sent recruiter email and a resume audit table with a why-sentence for every listed technology.

Day 2 — OOPs applied in Python

• Write by hand a small class hierarchy (for example a report exporter) using abc.ABC, inheritance and polymorphism, then rewrite it with composition and note the trade-off.
• Review Python specifics you can be asked about: @property, classmethod versus staticmethod, dunder methods, the mutable default argument trap, and is versus ==.
• Map each OOPs pillar to a class or module from one of your own projects and say the answer aloud.

Deliverable: A handwritten class design in two versions (inheritance and composition) and a spoken answer to the OOPs question that names one project example per pillar.

Day 3 — Database indexing and schema design

• Draw a B-tree lookup and explain composite index order, covering indexes, clustered versus non-clustered indexes, hash indexes and the write cost of each extra index.
• Design a schema for a user-activity tracking system: tables, keys, normalisation to third normal form, where you would denormalise, and when you would choose NoSQL instead.
• Load sample rows into a local database, run EXPLAIN on one query before and after adding an index, and work the window-function practice question.

Deliverable: A user-activity schema with index choices, the queries each index serves, and EXPLAIN output before and after one index.

Day 4 — Complexity and Python coding on paper

• Pick your most recent algorithm implementation, write it on paper, and derive time and space complexity line by line, naming the dominant term.
• Take three slow snippets (membership tests on a list, sorting inside a loop, repeated string concatenation) and rewrite each with a set, dict, heapq or a single sort, writing the new complexity beside the old.
• Work the practice question on finding the heaviest tenants in a window, then state its complexity and memory use.

Deliverable: Three on-paper solutions with stated time and space, and one before-and-after rewrite showing the complexity change.

Day 5 — Production debugging, concurrency and API design

• Write a one-page runbook for a slow production endpoint: metrics, what changed, profiler or slow-query log, lock and connection-pool checks, one change, verification.
• Study Python threading versus multiprocessing versus asyncio and the GIL, then write a small race condition and fix it with threading.Lock.
• Sketch a REST API for user-activity events covering resource naming, pagination and idempotent writes, and work the idempotency-fingerprint and concurrent-edit practice questions.

Deliverable: A one-page debugging runbook, a working race-condition demo fixed with a lock, and a short API design for the activity endpoint.

Day 6 — Maintainable code and project stories

• Write a maintainability checklist drawn from your own code: module boundaries, tests, naming, configuration, logging, code review and documentation, plus how you handled a requirement change.
• Write two project walkthroughs, each with architecture, one challenge, three decisions and the alternatives you rejected.
• Explain one project aloud to a non-technical listener, then work the deadline and code-review practice questions.

Deliverable: Two project walkthroughs with three decisions each and their rejected alternatives, plus a non-technical version of one walkthrough.

Day 7 — Paper-only mock and group discussion rehearsal

• Answer all five reported questions aloud in a row, then solve one small coding problem on paper with no IDE, narrating assumptions and trade-offs.
• Rehearse a group discussion with two or more people on a technical topic, practising building on others' points instead of talking over them.
• Write two questions for your interviewers about the team's current challenges.

Deliverable: A completed paper-only mock covering the five reported questions, notes from the group discussion rehearsal and two written questions for interviewers.

Behavioural preparation

Candidates describe the non-coding stages as a group discussion, detailed conversation about past projects and a resume check, followed by HR interviews. Prepare to explain what you built, why you chose each technology, how you worked with others and how you explain technical ideas to non-technical people.

1. Walk through a past project: the architecture, the hardest challenge you hit, the tools you chose and what happened after release.
2. Pick one technology on your resume and explain why you chose it over an alternative, and what you would change now.
3. Describe a time you explained a technical concept to someone non-technical: what you left out, what example you used, and how you knew it landed.
4. Describe a group discussion or team meeting where you disagreed with the direction: how you made your point, how you listened, and what was decided.
5. Describe a production issue you helped fix: how you located it, what you changed, and what you documented afterwards.
6. Tell me about feedback you received in a code review and what you changed in your code or habits.

Ship under a deadline and bound the debt you chose

Medium · pagination · technical debt · tradeoffs

You have four days to ship a tenant-facing listing endpoint. The version you would defend uses keyset pagination over (tenant_id, status, updated_at DESC, resource_id DESC); the version you can finish uses LIMIT/OFFSET with no matching index. Describe a deadline call you actually made of this shape: what you shipped, what you knowingly deferred, how you bounded the damage with a mechanism rather than an intention, and the specific numeric condition that would force the follow-up. Name who you told and where you wrote it down.

Approach

1. Name the deferred failure precisely instead of calling it slow. OFFSET n makes the database produce and discard n rows, so cost grows with page depth; without an index matching the sort, every matching row is read and sorted before the limit applies; and rows inserted between two page fetches shift across the boundary so items are skipped or repeated with nothing in the response to signal it.
2. Bound the blast radius with something mechanical rather than a promise: cap maximum page depth, cap page size, restrict the endpoint to one internal caller, or keep it behind a flag. State which failure each cap removes and which it leaves standing.
3. Attach a number to the trigger and wire it to an alarm: the first tenant crossing N resources, or the endpoint's p99 crossing its share of the 400 ms budget, so the debt announces itself instead of waiting to be remembered.
4. Write it where the next engineer looks, which is the code and the ticket, not a chat message: what was deferred, why, the cap, and the trigger.

Follow-up

• At what page depth does the offset version breach your latency budget, given your page size and row counts?
• What breaks first when you switch to keyset pagination later, and what does a client holding an old page token see?

Turn a code review disagreement into a decision

Easy · code review · optimistic concurrency · communication

A colleague's change updates a row with UPDATE resource SET version = version + 1 WHERE resource_id = $1 AND version = $2 and treats an affected-row count of zero as a successful no-op. You read that as a silently lost update; they think returning 200 is friendlier to clients than returning a conflict. Describe how you have handled a review disagreement of this shape: what goes in the comment, when you leave the thread, and who decides. Then write the comment you would leave here, in under 80 words.

Approach

1. Sort the disagreement before writing anything. A silently discarded write is a correctness claim about data; the choice between 409 and 412 is taste. Only the first justifies blocking a merge, and saying which one you are doing is most of the value of the comment.
2. Make the claim reproducible in the comment itself with an interleaving rather than a principle: A reads version 7, B reads version 7, B commits version 8, A's predicate matches zero rows, A is told it succeeded and A's edit is gone.
3. Offer the alternative with its cost attached: return 409 carrying the current version and the revision that won, so the client can re-read and re-apply. Note that automatic retry is not the fix, because a retry re-reads the winner's state and reapplies an intent formed against data that no longer exists.
4. Apply an escalation rule you can state: two round trips on the thread, then a call, and the service's owner decides rather than the reviewer. A reviewer who cannot be overruled is a bottleneck with extra steps.

Follow-up

• Where would you put the test that fails if someone reintroduces the swallowed zero rowcount?
• The author says clients cannot handle a 409. How do you check whether that is true?

Argue against a design, lose, and commit anyway

Medium · disagreement · service boundaries · decision records

Describe a design you argued against and lost. State the failure you predicted as a named mechanism, not a feeling about complexity: two services that would need one transaction, a projection with no rebuild path, a write path with no idempotency key. Say what evidence you brought, what the decision maker weighed instead, and what you did after the decision was made: what you instrumented, what you wrote down, and whether the prediction came true. Five minutes.

Approach

1. State the prediction in falsifiable form up front: the mechanism, the condition that triggers it, and the observable outcome. A prediction that cannot be checked also cannot be credited to you later.
2. Show the evidence you had at the time and label each piece honestly as measured, analogous, or intuition. Keeping the intuition is fine; disguising it as data is the thing that erodes your standing in the next argument.
3. Represent the opposing case at full strength, including the constraint you did not control: a fixed date, a team boundary, or the fact that the decision was cheap to reverse and yours was not.
4. Make disagree-and-commit concrete. Name the artefact you left behind so the prediction could be settled without you: the alert and its threshold, the counter on the dashboard, the decision note that recorded the trade-off and the condition that would revisit it.

Follow-up

• What threshold on that alert would have proved you right, and did anyone ever look at it?
• If the same proposal arrived tomorrow with the same deadline, would you argue it the same way?