Interview guides · Beacon Hill

Beacon Hill Software Engineer Interview Questions & Guide 2026

Software engineer prep for Beacon Hill: design exercises weighted toward the constraints of consulting and client services work.

The 60-second brief

As a Software Engineer at Beacon Hill, you are stepping into a pivotal role that bridges the gap between complex technical requirements and high-impact business solutions. Beacon Hill operates as a premier staffing and consulting firm, meaning your work often involves contributing to diverse, high-stakes projects across various client environments. This position is not just about writing code; it is about providing technical expertise that helps organizations solve critical infrastructure, development, and architectural challenges.

For a design discussion, a catalogue of architectures is worth less than the ability to turn a vague requirement into a data model and an API contract. A box diagram with no schema under it collapses at the first follow-up question.

Focus areas

Version an API you cannot force clients to upgrade
Make integration retries idempotent without target-side idempotency keys
Bound credential lifetime by the engagement end date

Overview

You will find that the role demands both adaptability and a robust technical foundation. Because Beacon Hill serves a wide spectrum of industries, you may find yourself working on everything from full-stack.NET development to specialized cloud infrastructure or network engineering. The complexity of the work is often high, as you are frequently brought in to stabilize environments, architect new systems, or optimize existing pipelines. Success in this role requires a professional demeanor, the ability to communicate technical concepts to non-technical stakeholders, and a commitment to delivering high-quality, scalable solutions in fast-paced settings.

The Beacon Hill interview process

Beacon Hill candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Recruiter Screen

Before anything technical happens, someone has to decide which rung of the ladder your loop is calibrated to, and that decision sets the bar for every round after it. It comes from how you describe scope, not from your title, because titles do not convert cleanly between companies. The weak version of the answer is team size and years. The strong version names the largest change you shipped where nobody reviewed the design, what would have broken if you had been wrong, and what you were paged for. Get the level said out loud on this call, because the range and the loop both follow from it.

What to demonstrate

Whether the scope in your own account maps onto a level the team actually has an opening at, so a mismatch ends the process cheaply rather than after four interviewers have spent a day
Whether your title needs re-mapping: the same word describes very different amounts of independent decision-making at a twenty-person company and a ten-thousand-person one
Whether your compensation expectation can be filled at that level in the structure the role pays in, which is why the number gets asked for before any engineer is scheduled

How to prepare

Write down two changes from the last two years: the largest one you designed with nobody reviewing the design, and the largest one where someone more senior did. Lead with the first when scope comes up, and be ready to say which parts of the second were yours
Ask which level the loop is calibrated to and what changes at the level above it, then plan your weeks from that answer rather than from the posting
Settle a total-compensation range beforehand with the split named, base against bonus against equity and its vesting period, so a question about numbers gets a number instead of the word market

Technical Interviews

The same problem is scored by two different mechanisms depending on the format, and preparing for one does not cover the other. With a person watching, partial progress is visible and a hint is a correction you can absorb; silence is the expensive failure, because nobody can read a half-written function. With an automated grader there is no partial credit for what you were about to do, nobody to ask, and the worked examples in the prompt are the entire specification. Read them as a contract, down to whether an empty result should be an empty list or no output at all.

What to demonstrate

In a live session, whether your commentary tracks what your hands are doing, and whether a hint redirects you or gets defended against
In an automated one, whether you cover the cases the examples do not show, since the hidden cases are where the score moves
Whether you manage the clock on purpose: abandoning an approach that is not converging while there is still time to write something simpler that finishes

How to prepare

Have someone hand you a problem and feed you one deliberately wrong hint. Practise testing it against a concrete case instead of accepting or rejecting it on authority.
Do one timed run a week in a plain browser editor with autocomplete, linting and your own snippets switched off, which is closer to what these environments give you
For the automated format, write the harness before the solution: a main that feeds the worked examples plus an empty and a single-element case and prints expected against actual, so a wrong submission is caught by you first

Where candidates lose points

Long-lived shared credentials for client systems

One static key used by the connector runtime, a debugging script and three engineers cannot be attributed to a person in an audit, cannot be expired at engagement close, and cannot be rotated without breaking an unknown number of consumers at once. The expensive part of the eventual incident is not the leak; it is that rotation requires finding every holder under time pressure, and nothing recorded who they were. Issue per-principal, per-target, short-lived grants from a broker so rotation is a no-op, attribution is automatic, and closure is a TTL rather than a search.

Tenant context leaking across a connection pool

Setting the tenant on a connection and relying on it for the rest of the request looks correct in every test that runs one request at a time. Under transaction pooling the connection goes back to the pool with that session state still attached, and the next checkout, serving a different client, inherits it; row-level security then enforces the previous tenant's policy flawlessly and returns the wrong rows. It reproduces only under concurrency, which is the load profile your integration tests do not have. Use a transaction-scoped setting, and assert that the setting matches the request's tenant immediately before the first query rather than trusting that it was set correctly upstream.

Finishing a solution without stating its complexity

Give time and space in the same breath as the code, and define n explicitly when there are two sizes, since n nodes and m edges are not interchangeable. Space is the half that gets skipped: count the auxiliary structures you allocate and the recursion stack at its deepest, not only the answer you hand back.

Abandoning working code to chase the optimal solution

Get the straightforward version correct, state its complexity, and only then optimise, keeping the working version until the faster one passes the same cases. A correct quadratic solution with a stated path to linear beats a half-written optimal one that never ran.

Questions & practice

Explain the principles of SOLID design and OOP.

Medium · data structures and algorithms

Approach

Name the brute-force solution and its complexity before improving on it.
Choose the data structure from the access pattern, not from familiarity.
State the target complexity and say which constraint rules the naive version out.

Follow-up

What is the worst case, and how likely is it on real data?
Which test case would catch an off-by-one here?

How do you approach unit testing versus integration testing?

Medium · data structures and algorithms

Approach

Choose the data structure from the access pattern, not from familiarity.
Name the brute-force solution and its complexity before improving on it.
Restate the input: its shape, its size, and what is guaranteed about it.

Follow-up

How does this change if the input no longer fits in memory?
Which test case would catch an off-by-one here?

How do you approach debugging complex systems under time pressure?

Medium · data structures and algorithms

Approach

Choose the data structure from the access pattern, not from familiarity.
State the target complexity and say which constraint rules the naive version out.
Name the brute-force solution and its complexity before improving on it.

Follow-up

What is the worst case, and how likely is it on real data?
How does this change if the input no longer fits in memory?

Can you walk me through an implementation solution for a resource-constrained data loader?

Medium · data structures and algorithms

Approach

Walk one small example through your approach before writing the whole thing.
State the target complexity and say which constraint rules the naive version out.
Name the brute-force solution and its complexity before improving on it.

Follow-up

What is the worst case, and how likely is it on real data?
Which test case would catch an off-by-one here?

Collapse connector retry attempts into logical jobs and failure counts

Easy · hash aggregation · idempotency · retries

You are given one UTC day of connector_run rows: (connector_id, idempotency_key, attempt_no, status, failure_class, records_read, records_applied), unique on (connector_id, idempotency_key, attempt_no), up to 5,000,000 rows in arbitrary order. Retries of one logical job share the idempotency_key; the job's outcome is the status of its highest attempt_no. Return, per connector_id: the number of logical jobs, a count of terminally failed jobs by failure_class, and the dedupe hit total, meaning sum(records_read - records_applied) over terminal-succeeded attempts only. One pass over the input; state your memory bound.

Approach

Key a hash map on (connector_id, idempotency_key) and keep only the highest attempt_no seen so far plus that row's status, failure_class and record counts. One pass, O(n) time, O(d) space where d is the number of distinct logical jobs, not O(n).
Treat a repeated (key, attempt_no) as corrupt input and raise, since the table's uniqueness constraint says it cannot happen; silently overwriting hides a double-insert in the runtime.
Fold the d surviving entries into per-connector counters in a second pass over the map, not over the rows. The failure_class histogram is a small fixed-width map per connector because failure_class is an enum.
Compute dedupe hits only from terminal-succeeded attempts. A failed attempt's records_read is work attempted, not work deduplicated, and adding it counts the same source records once per retry.
If d does not fit in memory, partition the input by hash(connector_id, idempotency_key) into p files and aggregate each partition independently. Same O(n) total work, p sequential passes, memory traded for I/O; the partition function must use the full key or a job's attempts split across files.

Worked solution (20 min)

Take six rows: (c=7,K=A,1,failed,client_429,0,0), (c=7,K=A,2,succeeded,null,500,480), (c=7,K=B,1,ambiguous,client_timeout,300,300), (c=7,K=B,2,succeeded,null,300,0), (c=9,K=C,1,failed,schema_mismatch,0,0), (c=9,K=C,2,failed,schema_mismatch,0,0).
Build the map: A -> attempt 2 succeeded, B -> attempt 2 succeeded, C -> attempt 2 failed/schema_mismatch. Three entries from six rows.
Fold per connector: connector 7 has 2 logical jobs, 0 terminal failures; connector 9 has 1 logical job, 1 terminal failure classed schema_mismatch.
Dedupe hits from terminal-succeeded attempts only: (500-480) + (300-0) = 320 for connector 7, 0 for connector 9.
Compute the wrong answer deliberately by counting attempt rows: 6 jobs and 3 failures, which is what a naive GROUP BY on run rows returns.

Expected result: connector 7: 2 logical jobs, no terminal failures, 320 dedupe hits. connector 9: 1 logical job, 1 terminal failure with failure_class='schema_mismatch', 0 dedupe hits. Attempt-level counting would have reported 6 jobs and 3 failures.

Verify it yourself

The sum of terminal statuses across all connectors equals the number of distinct (connector_id, idempotency_key) pairs, here 3.
records_applied <= records_read holds on every row, so the dedupe total is non-negative.
Rerunning the aggregation over a shuffled copy of the input gives identical output, since the highest attempt_no wins regardless of arrival order.
Map size equals 3, strictly less than the 6 input rows, confirming collapse actually happened.

Follow-up

A job's highest attempt is 'cancelled' but an earlier attempt succeeded. What is the job's outcome, and what does that say about who writes the cancel?
Produce this incrementally as runs land instead of as a daily batch: what state do you keep per key, and what happens when an attempt arrives out of order?
records_applied is written by your runtime, not by the client. What would you reconcile it against before anyone trusts the dedupe-hit number?

Generate invoice lines once under concurrent month-end runs

Hard · isolation levels · unique constraints · idempotency

Month-end generation reads approved work_record rows for one engagement and period, sums minutes, inserts one invoice_line whose generation_key is UNIQUE over (engagement_id, period_start, period_end, line_type, generator_version), then sets those work records to invoiced with locked_at and invoice_line_id. Two runs execute concurrently for the same engagement while late approvals are still committing. Working in PostgreSQL, name the anomaly at READ COMMITTED and at REPEATABLE READ, and specify the controls that make a retry a no-op rather than a second charge.

Approach

At READ COMMITTED each statement takes a fresh snapshot, so the SELECT that computed the sum and the later UPDATE see different data: an approval committed between them is a phantom the sum missed but the UPDATE can still mark invoiced. Worse, an UPDATE that blocks on a row another transaction is changing re-evaluates its WHERE against the new row version once the lock releases, so WHERE status = 'approved' silently skips rows the other run already moved — fewer rows than you counted, and no error anywhere.
At REPEATABLE READ, which in PostgreSQL is snapshot isolation, the sum and the update agree because the whole transaction shares one snapshot, but a concurrent update to the same row aborts you with serialization_failure, SQLSTATE 40001, which the application must catch and retry. Snapshot isolation still permits write skew across different rows; only SERIALIZABLE with SSI excludes it, at the cost of more 40001s and predicate-lock memory bounded by max_pred_locks_per_transaction.
Make the second run collide instead of race: take a transaction-scoped advisory lock on a hash of (engagement_id, period_start, line_type) at the top of the run, so one generates while the other waits and then finds finished work. Keep the UNIQUE generation_key as the backstop, because the advisory lock is per-database-connection state that a failover or a second database node does not carry.
Treat the unique violation as success. Catch SQLSTATE 23505, re-select the existing invoice_line by generation_key, and return it. INSERT ... ON CONFLICT DO NOTHING ... RETURNING returns zero rows on conflict, so a handler that trusts RETURNING writes a NULL invoice_line_id and reports a failure for work that in fact completed; ON CONFLICT DO UPDATE is worse, because it overwrites a line that has already been issued.
Bound the read deterministically rather than by timing: select the work records FOR UPDATE ordered by work_record_id so both runs acquire row locks in the same order and cannot deadlock, and filter on approved_at < the run's start timestamp so a late approval is out of scope by definition and lands in the next period.
Enforce immutability in the database, not in the service. A BEFORE UPDATE trigger raising when locked_at IS NOT NULL and a billed column changes, plus CHECK (status <> 'invoiced' OR invoice_line_id IS NOT NULL), means the constant and legitimate business pressure to edit an invoiced entry resolves into an appended credit_note row instead of a quiet update.

Worked solution (40 min)

Open two psql sessions and interleave them by hand: A begins and inserts the invoice_line without committing; B begins and attempts the same insert.
Observe B blocking on the unique index, commit A, and record the exact error B receives.
Implement the handler path: catch 23505, re-select by generation_key, return that row, and confirm both sessions report the same invoice_line_id.
Repeat at REPEATABLE READ with both sessions updating the same work_record to force 40001.
Approve a work record after A's start timestamp and follow where it ends up.

Expected result: One invoice_line per (engagement, period, line_type, generator_version); the losing run returns the winner's row as success rather than an error; the late approval remains status approved, unlocked, and is picked up by the next period's run; the 40001 path is exercised by a retry rather than surfaced to the caller.

Verify it yourself

Both sessions report the same invoice_line_id, and the table holds exactly one row for that generation_key.
The late-approved work record still has status approved and locked_at NULL after both runs finish.
An UPDATE against a work record with locked_at set is rejected by the trigger, not merely by the service layer that happened not to issue it.
SELECT generation_key, count() FROM invoice_line GROUP BY 1 HAVING count() > 1 returns nothing — trivially, because the constraint and not the code is what guarantees it.

Follow-up

A line has been issued and then an approval behind it is reversed. What rows do you write, and what does the original line look like afterwards?
How many times does the run retry before giving up, and what state does it leave behind for the operator who picks it up?
After the fact, how do you prove no engagement-period was billed twice, without trusting the application code that wrote it?

Return the latest attempt per connector idempotency key

Easy · distinct-on · indexing · idempotency

connector_run stores one row per attempt: run_id, connector_id, idempotency_key, attempt_no SMALLINT, status (queued, running, succeeded, failed, ambiguous, cancelled, abandoned), failure_class, watermark_from, watermark_to, started_at, finished_at, unique on (connector_id, idempotency_key, attempt_no). For one connector_id, write the query returning the highest-numbered attempt per idempotency_key, keeping only those keys whose latest attempt is not succeeded, ordered by watermark_from. Give the index that serves it, and say what the plan does without that index.

Approach

Use DISTINCT ON (idempotency_key) with ORDER BY idempotency_key, attempt_no DESC. PostgreSQL requires the ORDER BY to lead with exactly the DISTINCT ON expressions, and that requirement is what makes the row picked deterministic rather than whichever the scan happened to reach first.
Apply the not-succeeded filter outside, in a wrapping SELECT. Putting it in the inner WHERE changes the question from 'keys whose latest attempt failed' to 'keys that have any non-succeeded attempt', which reports a key that failed twice and then succeeded, and is the single most common wrong answer here.
Know the portable alternative: row_number() OVER (PARTITION BY idempotency_key ORDER BY attempt_no DESC) in a subquery, filtered = 1 outside. A window function cannot be filtered in WHERE because WHERE is evaluated before the window, and PostgreSQL has no QUALIFY clause, so the subquery is mandatory rather than stylistic.
Index (connector_id, idempotency_key, attempt_no DESC). With connector_id fixed by equality, the index supplies the exact order DISTINCT ON needs and the Sort node disappears. It does not reduce rows read: through PostgreSQL 17 there is no loose index scan, so the executor still walks every attempt in range rather than skipping to each next distinct key.
Give the complexity honestly: without the index the plan is a scan plus an O(n log n) sort over the connector's attempts; with it, O(n) ordered reads and constant working memory, where n is attempts in range, not distinct keys. If the distinct keys are a tiny fraction of attempts, a recursive skip-scan is the next step.

Follow-up

attempt_no is not monotonic in time because a retry was queued out of order. Does your query still pick the right row, and which column should it actually order by?
How would you return the latest attempt per key across all connectors in one environment without the query degrading to a full sort?
An ambiguous latest attempt means the target may or may not have been written. What does this query feed, and should ambiguous be treated as failed here?

Design a run API where the outcome is genuinely unknown

Hard · idempotency · state-machines · ambiguity · at-least-once

connector_run holds run_id, connector_id, environment_id, idempotency_key, attempt_no, status, failure_class, watermark_from, watermark_to, records_read, records_applied, source_schema_version, started_at and finished_at; idempotency_key is derived from the connector and the watermark range, and (connector_id, idempotency_key, attempt_no) is unique. Workers claim a range, write to a client-owned target that offers no idempotency support of its own, then report back. A worker can die at any point, and a write can time out leaving the target's state unknown. Design the claim, report and settle endpoints and the status transitions a scheduler and an operator drive.

Approach

Derive the run's identity from the work rather than from the caller. POST /connectors/{id}/runs carrying a watermark range computes idempotency_key deterministically, so a scheduler retrying a claim receives the same logical run instead of a second one covering the same rows. Mint a new attempt_no only on an explicit retry of an attempt that has already settled as failed, which keeps the attempt history readable and stops a crashed worker from quietly opening a parallel attempt over the same range.
Give the claim a lease with an expiry rather than a lock. The worker heartbeats; an expired lease makes the run reclaimable. Without an expiry, a dead worker wedges the binding until someone notices. With a lease and no fencing, two workers can both believe they hold the range, so carry a monotonically increasing lease token and reject any settle whose token is stale — that is what stops a zombie's late report overwriting the live attempt's.
Make ambiguous a first-class status with its own transitions, separate from failed. A timeout, or a reset after the request bytes went out, leaves the target's state unknown: settling as failed and retrying duplicates, settling as succeeded and advancing the watermark loses data silently. The settle endpoint therefore accepts succeeded, failed with a class, or ambiguous with evidence, and only succeeded advances the watermark.
Keep the ledger write and the watermark advance in one local transaction, and have settle carry the dedupe keys actually applied. The target write and your commit cannot be made atomic without a distributed transaction no client will grant, so ambiguity is resolved by a reconciliation read: re-read the target by dedupe key over the claimed range and settle from what you find. Store the key's input fields and a derivation version alongside the hash, because the client will eventually rename or retype one of those fields and every previously computed key still has to be reproducible.
Define each caller's behaviour precisely. The scheduler re-claims expired leases and retries only definite failures whose class is retryable; it never retries an ambiguous run. The operator has an explicit resolve endpoint used after reconciliation, recording who decided and on what evidence. An ambiguous run that auto-retries is the design bug — it converts an unknown into a duplicate inside a system you do not own and cannot correct.
Close the loop with the counters. records_applied differs from records_read by the dedupe hit count, so a retry over a partially applied range should report a high hit count and little net change. That difference is the observable proving the dedupe key works: if a retry reports applied equal to read, the key is not matching and you are duplicating on the client's side right now.

Worked solution (40 min)

Write the state machine — queued, running, succeeded, failed, ambiguous, cancelled, abandoned — with every legal transition and who triggers it.
Write the claim endpoint's request and response, showing how idempotency_key is derived and what a repeated claim returns.
Inject a crash between each adjacent pair of claim, fetch credential, validate, write, ledger, advance, and say what the next claim of that range does in each case.
Write the settle payload for the ambiguous case, listing the evidence a later reconciliation will need.
Trace a zombie worker settling ninety seconds after its lease expired, and show the token check rejecting it.
Write the operator's resolve endpoint and the audit fields it records.

Expected result: A claim keyed on (connector_id, watermark range) that is safe to repeat, a leased and heartbeat-renewed claim with a fencing token checked at settle, a settle endpoint accepting succeeded, failed-with-class or ambiguous-with-evidence where only succeeded advances the watermark, a reconciliation path that resolves ambiguity by reading the target back by dedupe key, and an operator resolve step that never happens automatically.

Verify it yourself

Retrying a claim for the same range never produces two runs with different idempotency_keys.
A run left ambiguous stays ambiguous until something reads the target; nothing in the scheduler moves it.
Replaying a fully applied range reports records_applied of zero with a dedupe hit count equal to records_read.

Follow-up

The client's endpoint offers no read-back by your dedupe key. How do you settle an ambiguous run then?
A worker's lease expires while its write is still in flight and a second worker claims the range. What exactly prevents the double apply?
How long do you keep the dedupe ledger, and what happens when a record arrives after its ledger row was pruned?

Make timesheet submission safe under a client's automatic retries

Medium · idempotency · retries · unique-constraints · http-semantics

work_record holds work_record_id, engagement_id, principal_id, work_date, entered_at, minutes, is_billable, status, approved_at, locked_at and invoice_line_id, and entered_at is assigned by the server at insert. A delivery-team app submits entries over mobile networks and retries any request that has not answered within ten seconds. Today POST /engagements/{id}/work-records inserts a row and returns 201, and month-end shows duplicated minutes. Design the write contract so a retried submission cannot double-book: start from whether those columns give you a natural key, then specify the key you actually use, its scope and lifetime, the response on replay, the behaviour when the same key arrives with a different body, and what the app does on each outcome.

Approach

Name the actual fault: POST is not idempotent, and a ten-second timeout leaves the outcome unknown, because the row may have committed and only the response was lost. Retrying is correct client behaviour; making the retry safe is the server's job. Two mechanisms exist and they are not interchangeable — a natural key derived from the row's own grain, or an opaque client-supplied key — so the first question is which one this grain can support.
Test the natural key against the columns you were given, and reject it. The candidate is (engagement_id, principal_id, work_date), and nothing left over separates two legitimate entries: minutes and is_billable repeat honestly, because one person can book two distinct ninety-minute billable blocks on one day, and entered_at is server-assigned so it differs on every retry — it changes at exactly the moment a dedupe key must stay fixed. A unique index on that triple therefore rejects a real second entry, trading a visible bug for an invisible one: duplicated minutes surface at month-end, while silently refused minutes surface as an under-billed engagement nobody reconciles. Prefer a natural key wherever the grain genuinely has one; this grain does not, so the key has to come from the client.
Require an Idempotency-Key header and say so in the contract. With no natural key available, a request that arrives without one cannot be made retry-safe by anything the server knows, so refuse it with its own stable code rather than accepting it and hoping. The IETF header field is still a draft, so document your own semantics rather than pointing at a spec. Scope the key to (authenticated principal, endpoint, key) so one tenant cannot collide with another's, store a fingerprint of the request body beside it, and retain it at least as long as the client's maximum retry horizon; a key pruned before the last retry re-opens the duplicate window it existed to close.
Pin the client's half of the contract, because that is where this design usually fails in practice: the key is generated once per logical entry and reused byte-for-byte on every retry of it. An app that mints a fresh UUID per attempt satisfies the header and still double-books, and no server-side check can tell that apart from two genuine entries. Specify a deterministic construction the app can rebuild after a restart rather than one it holds in memory, or a crash between attempts reintroduces the duplicate.
Same key with a different body is a distinct 4xx with its own code, never a silent replay of the first response. These are two different bugs: a genuine retry must replay, and a key reused for new content is a client-side key-generation defect that stays invisible for months if you answer it with the original body.
Handle the concurrent in-flight duplicate, which is the case a single-threaded test never reaches: two copies arrive forty milliseconds apart, both miss the key lookup, both insert. Resolve it in the database rather than by check-then-act. Put a unique index on (principal_id, endpoint, idempotency_key), insert that row in the same transaction as the work record, and let the loser take the violation. Under READ COMMITTED in PostgreSQL the second insert blocks on the winner's uncommitted key row and then raises 23505 when the winner commits; that error aborts the transaction, so take a SAVEPOINT before the insert if you intend to read the winner back on the same connection. Note also that INSERT ... ON CONFLICT DO NOTHING returns no row on conflict, so the handler needs an explicit SELECT afterwards, or ON CONFLICT DO UPDATE ... RETURNING, to obtain the winning row.
Answer the loser that arrives before the winner has stored its response with a 409 carrying its own code and a Retry-After, not with a guessed body. Store each response with its status code so a later replay is byte-identical, and write the app's branch table: 201 and 200 both mean submitted; the different-body 4xx and the missing-key 4xx stop and surface to the user; a 409 against locked_at means re-read; the in-progress 409 retries after Retry-After; timeouts and 5xx retry with the same key and the same body. No branch may retry a terminal 4xx and no branch may drop a timeout.

Follow-up

The period has been billed and locked_at is set when a retry finally lands. What status does that retry get, and what does the app show the user?
What would have to be true of work_record for a natural key to be the better answer here, and what would you still keep the header for?
How long do you keep idempotency keys, and what exactly breaks on the day you prune one too early?

Reports intermittently return another client's rows under load

Hard · tenant isolation · row-level security · connection pooling · concurrency

A shared-pool deployment scopes every query with row-level security: the application runs SET app.client_id at the start of each request, and policies compare client_id against current_setting('app.client_id'). Twice this week a client's report contained rows belonging to a different client. Staging cannot reproduce it, single-request replays are clean, and the SQL text is identical in the good and bad cases. Connections come from a pool shared by all requests. Give the ordered diagnosis, the reproduction, and the fix.

Approach

Order the hypotheses by what the evidence already excludes. Identical SQL text and clean single-request replays rule out a missing predicate in that endpoint, so the variable is not the query, it is the value the policy reads when the query runs. Check, in this order: is RLS enabled and forced on the table; is the serving role actually subject to it; and only then, what does current_setting return at execution time.
Confirm the policy is in force before blaming it. Policies do not apply to the table owner unless FORCE ROW LEVEL SECURITY is set, and never apply to a role with BYPASSRLS or to a superuser. If traffic is served by the owning role, the policy has been decorative all along and the leak is not intermittent at all, only intermittently noticed.
Instrument the value, not the code. As the first statement inside every transaction, read current_setting('app.client_id', true) and compare it with the tenant on the request, emitting a counter on mismatch. A defect invisible to replays shows up here within minutes of real concurrent traffic.
Explain why pooling makes it intermittent. A plain SET is session-scoped and survives COMMIT, so the backend returns to the pool carrying the previous request's value. This needs no transaction-pooling proxy; an ordinary application connection pool is sufficient. The next checkout that sets the value late, or skips it on some path, inherits the previous tenant, and row-level security then filters flawlessly for the wrong client.
Reproduce deterministically rather than by load-testing and hoping. Cap the pool at one connection, issue two requests for different tenants back to back, and have the second issue its first query before its SET. Zero rows would be the fail-closed outcome; the other tenant's rows is the leak, and the difference between those two results is exactly whether the previous value was left behind.
Fix in layers. Use set_config('app.client_id', $1, true) or SET LOCAL inside the transaction so the value dies at COMMIT; assert it matches the request immediately before the first query rather than trusting an upstream middleware; set FORCE ROW LEVEL SECURITY; and serve traffic with a role that neither owns the tables nor holds BYPASSRLS, reserving the owning role for migrations.

Follow-up

You owe a breach notification within contractual time. What query bounds which client pairs and which rows were exposed, and what in the schema makes that answerable at all?
A background job has no inbound request and therefore no tenant. Where does its tenant come from, and what stops it from being where this happens next?
How does the same failure mode look on the dedicated and client-managed tiers, and what does that tell you about where isolation should live?

Suggested practice checklist

For a candidate senior enough that the loop turns on design and judgement rather than on whether the coding round gets finished. Five days build one system properly and then stress it; coding gets a single maintenance day, on the assumption that the risk at this level is an unexamined tradeoff rather than a missed algorithm.

Day 1 — Numbers before diagrams

Build your own reference card of the figures you will re-derive all week: bytes for a realistic record, requests per second implied by a given daily active count, and the storage that a year at a given write rate produces. Derive each one rather than copying it, because the derivation is what survives a follow-up.
Turn one product statement into capacity requirements. From ten million daily users at four writes and forty reads each, state the peak-to-average factor you are assuming and why, then produce peak write QPS, peak read QPS and a year of storage.
Write the two numbers whose order of magnitude changes the design, the read-to-write ratio and the working-set size against memory per node, and state the threshold at which each one flips your answer.

Deliverable: A one-page numbers card and one worked capacity estimate with every assumption written down.

Day 2 — One system, from requirements to schema

Spend the first ten minutes producing only functional requirements, non-functional targets with numbers attached, a p99 latency, a durability expectation, a consistency requirement, and an explicit out-of-scope list.
Define the interface before the boxes: the three or four endpoints, their parameters, what each returns, and which of them are idempotent.
Write the data model, then write the single access pattern that justifies it, and state what the schema would have to become if the dominant access pattern were the other one.

Deliverable: One design carried to endpoint-and-schema depth, with non-functional targets expressed as numbers and a written out-of-scope list.

Day 3 — The consistency you are actually buying

Write out what a client sees under asynchronous replication when its write commits on the leader and its next read is served by a lagging follower, then write the two fixes, pinning that session's reads to the leader for a bounded window or carrying a version token the replica must reach, and the cost of each.
Work the quorum arithmetic on paper for N of three with W and R of two, and separate what R + W > N does guarantee, that any read set intersects any write set, from what it does not: on its own it is not linearizability, and a sloppy quorum that accepts writes on nodes outside the preference list breaks even the intersection.
Take two storage choices with different defaults, a single-leader relational store committing synchronously and a quorum-replicated store that converges eventually, and write the specific product behaviour that would be wrong under each, rather than a general statement about which is stronger.

Deliverable: A page separating what quorum overlap guarantees from what it does not, with one concrete product misbehaviour attached to each gap.

Day 4 — Failure is the design

For one write path, work through the case where the client times out after the server has already committed, then design the idempotency key: who generates it, how long it is retained, and what the duplicate request returns.
Express the retry policy as parameters rather than as a word: maximum attempts, base delay, backoff factor, jitter, and which error classes are retried at all. Then state why retrying a non-idempotent write without a key is a correctness bug and not merely waste.
Compute the fan-out effect on tail latency. If a request waits on ten backends and each independently exceeds its p99 one percent of the time, the chance at least one is slow is 1 - 0.99^10, about ten percent. Then write why independence is the optimistic assumption and what correlates them in practice.
Name the backpressure mechanism for one queue or one dependency in the design, a bounded queue with shedding or a concurrency limit, and write what the caller is told when it engages.

Deliverable: One write path with an idempotency design, a parameterised retry policy, and a written tail-latency calculation with its assumption named.

Day 5 — Scaling the hot path

Choose cache-aside or write-through for one read path and write the staleness window each produces, then name the invalidation event and what the system does when that event is lost.
Design against the stampede: either coalesce requests so only one recomputes a missing key, or refresh early with jittered expiry, and write why identical TTLs on keys populated in the same moment produce a synchronised expiry and a thundering herd.
Shard one table by a key you choose, then answer the two questions that break the choice: which queries now require a scatter-gather, and what happens to the distribution when one tenant is a hundred times larger than the median.
Write the cost of adding a node under plain modulo placement, where nearly every key moves, against consistent hashing, where roughly one key in n+1 moves, and state what virtual nodes are for.

Deliverable: A caching and sharding decision for one path, each with its failure mode and its rebalancing cost written beside it.

Day 6 — Keep the coding hand in, at the bar that applies to you

Solve one medium problem in thirty minutes, then spend twenty more making it production-shaped: named invariants, validation at the boundary, and errors that distinguish a caller mistake from an internal fault.
Write the tests you would require of a colleague's version of that function: one for empty input, one for the boundary, and one for the case the implementation is most likely to get wrong.
Read a piece of your own code from six months ago and write the change you would ask for, phrased as you would actually phrase it in review.

Deliverable: One problem hardened to review standard, with its test list and one written review comment.

Day 7 — Defend it while being interrupted

Run a forty-five-minute design mock with an interviewer briefed to change a requirement halfway, a tenfold traffic increase or a new strict consistency requirement, and to push on one number you estimated.
Rehearse the two sentences a senior loop is listening for: naming the tradeoff you are choosing against and why, and saying what you would measure to learn that the choice was wrong.
Prepare the design you regret: a real decision, the constraint that produced it, what it cost, and what you changed afterwards.

Deliverable: Mock notes recording how the design changed under the new requirement, plus a written account of one regretted decision.

Review and mentoring: what you let through

Counting review comments or mentees proves nothing. The useful version is a specific change you approved with a reservation you stated, or one you blocked and the delay that cost. Say which standard you were holding and why it was worth the friction. A mentoring story needs the thing the other person can now do without you.

Describe a time you had to learn a new technology stack on the fly.

Medium · behavioural and engineering judgement

Approach

Pick a story where you made the decision, not one where you watched it.
State the situation in two sentences and spend the rest on the reasoning.
Close with what you would do differently, concretely.

Follow-up

What would you do differently if you ran that again?
How did you know your change caused the improvement?

Walk me through your most significant projects—what was your specific contribution?

Medium · behavioural and engineering judgement

Approach

Give the blast radius: what could have broken, and what you measured.
Name the disagreement and how you resolved it with evidence.
Pick a story where you made the decision, not one where you watched it.

Follow-up

What would you do differently if you ran that again?
How did you know your change caused the improvement?

Estimate a first integration against an unfamiliar system

Easy · estimation · unknowns · client dependencies

You are asked for a delivery date on a first integration against a client-owned system nobody on the team has touched: unknown production schema version, unknown real rate limit, sandbox access only for now. Describe how you have estimated work of this shape before. State how you separated the unknowns you could reduce by investigation from the ones you could not, the range you gave, the assumptions you attached to it, and what you did when one of those assumptions turned out to be wrong.

Approach

Split the unknowns into reducible and irreducible. The schema version, the payload shape and the real rate limit are answerable in a day of investigation; the client's change-window availability and their turnaround on an access request are not, and they usually dominate the calendar.
Buy information before committing to a date: a time-boxed spike that asserts the observed schema version at the boundary and measures the throttle empirically rather than reading it from a document. Say what you would do if the spike itself were blocked on access.
Give a range with the assumptions written beside it, each with an owner and a date it will be confirmed by, so that a broken assumption updates the estimate instead of silently consuming slack.
Separate engineering days from elapsed days out loud. In this domain the gap between them is the estimate, because waiting on a client's approval is the schedule.
Say how you communicated the first breach: early, naming the assumption, rather than absorbing it and reporting late.

Follow-up

The sandbox has no rate limiting and production does. What changes in the plan, not just in the number?
You are pushed for a single date rather than a range. What do you give, and what do you attach to it?