Design a Test Result Storage System

TL;DR

Use a transactional database for run and result metadata, object storage for logs and media, and a queue for asynchronous enrichment. Require idempotent writes, index the main debugging paths, and apply tiered retention with tenant-aware authorization.

Key Takeaways

• Separate searchable result metadata from large immutable artifacts.
• Make ingestion idempotent with stable run and result identifiers.
• Model runs, test cases, attempts, results, and artifacts independently.
• Optimize indexes around real debugging and trend-analysis queries.
• Use asynchronous enrichment for analytics without slowing test runners.
• Define retention, security, and failure recovery before discussing scale.
• Present tradeoffs and an evolution path instead of naming tools without reasons.

A strong design test result storage system interview answer starts with the workload, not a database brand. You need to accept bursty parallel writes from CI, preserve every attempt, return recent failures quickly, store large artifacts cheaply, and support historical analytics without corrupting source data.

This tutorial builds a runnable reference system and explains how to evolve it. For the broader framework, estimation method, and communication rubric, read the Senior SDET system design interview complete guide.

You will use PostgreSQL for searchable metadata, an idempotent HTTP ingestion service, and an artifact abstraction that can later point to S3-compatible object storage.

What You Will Build

By the end, you will have:

• A data model for runs, cases, attempts, results, and artifacts.
• A TypeScript ingestion API with conflict-safe retries.
• Queries for triage, summaries, and duration trends.
• An outbox for downstream analytics.
• A defensible retention and security plan.

Prerequisites

Use Node.js 22 or newer, npm 10 or newer, Docker with Compose v2, and PostgreSQL 17 in the container. The code uses Express 5, pg, and Zod 4 compatible APIs. Create an empty practice directory and install dependencies:

npm init -y
npm install express pg zod
npm install -D typescript tsx @types/express @types/node @types/pg

Add \"type\": \"module\" to package.json. Start PostgreSQL:

# compose.yaml
services:
  db:
    image: postgres:17
    environment:
      POSTGRES_USER: test_results
      POSTGRES_PASSWORD: test_results
      POSTGRES_DB: test_results
    ports:
      - \"5432:5432\"
    healthcheck:
      test: [\"CMD-SHELL\", \"pg_isready -U test_results\"]
      interval: 2s
      timeout: 2s
      retries: 15

docker compose up -d
export DATABASE_URL=postgresql://test_results:test_results@localhost:5432/test_results

Verify with docker compose ps. The database service should report healthy. In an interview, state that exact versions are implementation choices, while the boundaries and guarantees are the durable part of the design.

Step 1: Frame the Design Test Result Storage System Interview

Start by clarifying scope. Ask which frameworks send results, peak concurrent workers, average tests per run, artifact sizes, query freshness, retention, tenant isolation, and whether reruns must remain visible. Do not assume that a test has only one outcome. Retries make attempt history essential.

Write down this initial contract:

Estimate symbolically before inventing numbers. If R is peak runs per second, T is tests per run, and A is attempts per test, peak result writes equal R * T * A. Daily metadata is writes per day multiplied by average row bytes. Artifact volume is separate because a video can outweigh thousands of result rows.

Your service-level objectives might say: accepted results are durable before 202 Accepted; recent-run queries meet an agreed percentile latency; no acknowledged result disappears; analytics may lag by several minutes. Label every number as a requirement to confirm, not an industry fact.

Verification: You are ready when you can name the primary write path, three high-value read paths, the consistency promise, and the largest unknown. That framing should take under five minutes on a whiteboard.

Step 2: Create the Test Execution Data Model

Keep identity separate from execution. A test case describes a logical test. A run describes one CI invocation. A result joins them for a specific attempt. This prevents retries from overwriting evidence and lets renamed tests retain history through a stable source-controlled test key.

Create schema.sql:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE test_runs (
  id uuid PRIMARY KEY,
  tenant_id text NOT NULL,
  project_id text NOT NULL,
  commit_sha text NOT NULL,
  branch text NOT NULL,
  environment text NOT NULL,
  status text NOT NULL CHECK (status IN ('running','passed','failed','canceled')),
  started_at timestamptz NOT NULL,
  finished_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE test_cases (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id text NOT NULL,
  project_id text NOT NULL,
  test_key text NOT NULL,
  display_name text NOT NULL,
  suite_path text NOT NULL,
  UNIQUE (tenant_id, project_id, test_key)
);

CREATE TABLE test_results (
  id uuid PRIMARY KEY,
  tenant_id text NOT NULL,
  run_id uuid NOT NULL REFERENCES test_runs(id),
  test_case_id uuid NOT NULL REFERENCES test_cases(id),
  attempt smallint NOT NULL CHECK (attempt > 0),
  status text NOT NULL CHECK (status IN ('passed','failed','skipped','timed_out')),
  duration_ms integer NOT NULL CHECK (duration_ms >= 0),
  error_class text,
  error_message text,
  worker_id text,
  started_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (run_id, test_case_id, attempt)
);

CREATE TABLE artifacts (
  id uuid PRIMARY KEY,
  tenant_id text NOT NULL,
  result_id uuid NOT NULL REFERENCES test_results(id),
  kind text NOT NULL CHECK (kind IN ('log','screenshot','video','trace','other')),
  object_key text NOT NULL UNIQUE,
  content_type text NOT NULL,
  size_bytes bigint NOT NULL CHECK (size_bytes >= 0),
  sha256 text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Apply it with psql \"$DATABASE_URL\" -f schema.sql. In production, use migrations and keep changes backward compatible while old runners send the prior payload.

Verification: Run psql \"$DATABASE_URL\" -c '\\dt'. You should see four tables. Try inserting a negative duration and confirm PostgreSQL rejects it. The database now protects core invariants.

Step 3: Build an Idempotent Ingestion API

Runners retry after timeouts, so a repeated request must not create a second result. Let the client generate a UUID result ID and use ON CONFLICT. Also verify that the tenant on the authenticated request owns the run. The example uses a header only to keep the tutorial runnable. Replace it with verified identity claims in production.

Create server.ts:

import express from 'express';
import { Pool } from 'pg';
import { z } from 'zod';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();
app.use(express.json({ limit: '256kb' }));

const ResultInput = z.object({
  id: z.uuid(),
  runId: z.uuid(),
  projectId: z.string().min(1),
  testKey: z.string().min(1),
  displayName: z.string().min(1),
  suitePath: z.string().min(1),
  attempt: z.number().int().positive(),
  status: z.enum(['passed', 'failed', 'skipped', 'timed_out']),
  durationMs: z.number().int().nonnegative(),
  errorClass: z.string().nullish(),
  errorMessage: z.string().nullish(),
  workerId: z.string().nullish(),
  startedAt: z.iso.datetime()
});

app.post('/v1/results', async (req, res) => {
  const tenantId = req.header('x-tenant-id');
  const parsed = ResultInput.safeParse(req.body);
  if (!tenantId || !parsed.success) {
    return res.status(400).json({ error: 'invalid request' });
  }

  const input = parsed.data;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const run = await client.query(
      'SELECT 1 FROM test_runs WHERE id = $1 AND tenant_id = $2',
      [input.runId, tenantId]
    );
    if (run.rowCount !== 1) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'run not found' });
    }

    const testCase = await client.query(
      `INSERT INTO test_cases (tenant_id, project_id, test_key, display_name, suite_path)
       VALUES ($1, $2, $3, $4, $5)
       ON CONFLICT (tenant_id, project_id, test_key) DO UPDATE
       SET display_name = EXCLUDED.display_name, suite_path = EXCLUDED.suite_path
       RETURNING id`,
      [tenantId, input.projectId, input.testKey, input.displayName, input.suitePath]
    );

    await client.query(
      `INSERT INTO test_results
       (id, tenant_id, run_id, test_case_id, attempt, status, duration_ms,
        error_class, error_message, worker_id, started_at)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
       ON CONFLICT (id) DO NOTHING`,
      [input.id, tenantId, input.runId, testCase.rows[0].id, input.attempt,
       input.status, input.durationMs, input.errorClass, input.errorMessage,
       input.workerId, input.startedAt]
    );
    await client.query('COMMIT');
    return res.status(202).json({ id: input.id });
  } catch (error) {
    await client.query('ROLLBACK');
    return res.status(500).json({ error: 'ingestion failed' });
  } finally {
    client.release();
  }
});

app.listen(3000, () => console.log('result API listening on 3000'));

Create the run before starting the server, then post the same payload twice. Both calls should return 202, while the table keeps one row. In a real service, compare a payload hash on conflict and return 409 if the same ID arrives with different content. Silent acceptance is safe only when IDs are immutable and payload equivalence is enforced.

Verification: Run npx tsx server.ts, send two identical requests, then execute SELECT count(*) FROM test_results WHERE id = '<your-id>';. The result must be 1. Stop here in an interview to emphasize that retry safety is a correctness feature, not an optimization.

Step 4: Separate Artifacts from Searchable Metadata

Never stream multi-megabyte videos through the metadata transaction. Have the API issue a short-lived presigned upload URL. The runner uploads directly to object storage, then registers the object key, checksum, type, and size. A worker verifies the object exists before marking it available.

A provider-neutral registration endpoint can use this input:

const ArtifactInput = z.object({
  id: z.uuid(),
  resultId: z.uuid(),
  kind: z.enum(['log', 'screenshot', 'video', 'trace', 'other']),
  objectKey: z.string().regex(/^[a-zA-Z0-9/_=.-]+$/),
  contentType: z.string().min(1),
  sizeBytes: z.number().int().nonnegative(),
  sha256: z.string().regex(/^[a-f0-9]{64}$/)
});

Use keys such as tenant/<tenant-id>/runs/<run-id>/results/<result-id>/<artifact-id>. Do not accept a caller-supplied bucket or unrestricted path. Bind upload authorization to tenant, key, content length, and a short expiration. Encrypt storage, block public access, and serve downloads through authorized short-lived URLs.

Direct upload first is simple but may leave orphaned objects. Reservation first creates a pending artifact, then finalizes it after upload and provides better auditability. Delete abandoned uploads with a lifecycle rule.

Verification: Register a known checksum and confirm metadata contains no blob bytes. Attempt an object key containing ../ and confirm schema validation rejects it. Then explain how a reconciler finds pending records and orphaned objects without blocking ingestion.

Step 5: Add Indexes and Debugging Queries

Design indexes from access patterns. The essential reads are a run summary, failures in a run, recent history for a test, and project trends over time. Avoid indexing every column because each index amplifies writes and consumes storage.

CREATE INDEX results_run_status_idx
  ON test_results (run_id, status);
CREATE INDEX results_test_history_idx
  ON test_results (test_case_id, started_at DESC);
CREATE INDEX runs_project_recent_idx
  ON test_runs (tenant_id, project_id, started_at DESC);

SELECT status, count(*) AS total, round(avg(duration_ms)) AS avg_duration_ms
FROM test_results
WHERE run_id = $1
GROUP BY status;

SELECT tc.suite_path, tc.display_name, tr.attempt, tr.error_class,
       tr.error_message, tr.duration_ms
FROM test_results tr
JOIN test_cases tc ON tc.id = tr.test_case_id
WHERE tr.run_id = $1 AND tr.status IN ('failed', 'timed_out')
ORDER BY tc.suite_path, tc.display_name, tr.attempt;

SELECT started_at, status, duration_ms
FROM test_results
WHERE test_case_id = $1
ORDER BY started_at DESC
LIMIT 50;

For cursor pagination, return (started_at, id) and request the next page with a tuple comparison. Offset pagination becomes slow and unstable as new rows arrive. For dashboards, maintain summary tables asynchronously rather than scanning raw results on every refresh. Keep raw facts immutable so you can recompute a broken aggregate.

At larger scale, partition results by time. Keep tenant in every row and index prefix where it matches authorization and queries. Partitioning adds migration and operational complexity.

Verification: Run EXPLAIN (ANALYZE, BUFFERS) on the three queries after loading representative sample data. Confirm the chosen indexes appear for selective queries. With tiny tables PostgreSQL may correctly choose a sequential scan, so verify again at realistic cardinality.

Step 6: Decouple Analytics with a Transactional Outbox

Publishing to a queue after committing the result creates a dual-write gap: the row may commit while event publication fails. Insert an outbox event in the same transaction, then let a relay publish and mark it delivered. Consumers remain idempotent because queues can redeliver.

CREATE TABLE result_outbox (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id text NOT NULL,
  aggregate_id uuid NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);

CREATE INDEX outbox_unpublished_idx
  ON result_outbox (created_at) WHERE published_at IS NULL;

-- Add this inside the result transaction.
INSERT INTO result_outbox (tenant_id, aggregate_id, event_type, payload)
VALUES ($1, $2, 'test_result.recorded', jsonb_build_object('resultId', $2));

The relay claims batches with FOR UPDATE SKIP LOCKED, publishes them, and updates published_at. A crash after publish but before update causes duplication, so downstream workers upsert using the outbox ID or result ID. Track outbox age, retry count, dead-letter volume, and consumer lag.

Flaky-test scoring belongs in a consumer because it uses history and can evolve independently. The flaky test detection system design tutorial shows how to turn stored attempts into classifications without slowing the write path. Preserve algorithm version and calculation time beside each derived score.

Verification: Stop the relay, ingest a result, and confirm an unpublished outbox row exists. Restart it and confirm the event is delivered. Replay the same event and verify the consumer's aggregate does not double count.

Step 7: Define Retention, Security, and Reliability

Classify data before deleting it. Recent metadata may stay in the primary database, older metadata may move to cheaper query storage, and videos may expire earlier than compact failure summaries. Legal holds and enterprise contracts can override normal lifecycle rules. Store retention policy by tenant and data class, then record deletion jobs for audit.

Use defense in depth. Authenticate workloads with short-lived credentials, derive tenant identity from trusted claims, authorize every run and artifact access, encrypt in transit and at rest, redact secrets from logs, and restrict operators. Error messages and traces often contain tokens or personal data. Run scanning and redaction before broad access, but retain the original only when policy permits.

For reliability, deploy multiple stateless API instances, use bounded connection pools, apply per-tenant quotas, and shed nonessential reads before rejecting writes. Back up PostgreSQL and practice point-in-time recovery. Enable object versioning only if its recovery value justifies cost. Define a reconciliation job that compares result metadata with artifact objects.

Discuss degraded behavior explicitly. If analytics is down, ingestion continues and the outbox grows. If object storage is down, metadata results still land while artifact uploads retry. If PostgreSQL is unavailable, do not claim durability. Runners spool encrypted results locally with bounded disk usage and exponential backoff, or fail the pipeline according to policy.

Verification: Run a tabletop exercise for database loss, queue lag, and object-store outage. For each one, state what callers observe, what data can be lost, the alert that fires, and the recovery action. A design is not complete until failure behavior is predictable.

Step 8: Present the Scaling Evolution

Walk the interviewer through thresholds, not fashionable infrastructure. Start with one regional PostgreSQL primary, read replicas for eligible queries, object storage, and an outbox worker. Add time partitions when maintenance or index size demands it. Add precomputed aggregates when raw scans miss dashboard objectives. Add regional ingestion only when latency or resilience requirements justify cross-region conflict handling.

If write volume exceeds one database, shard by tenant or project so most queries remain single-shard. Large tenants may need dedicated shards. A routing catalog maps tenants to shards and must itself be highly available. Cross-tenant reporting moves to an analytics store fed by events or change data capture. Never make the analytical copy the source of truth for an individual result.

Test execution and storage scale together. A cross-platform test execution service design explains how workers generate stable run IDs and apply backpressure. A device farm scheduler design adds device reservations, leases, and session artifacts that this storage service must correlate.

Verification: Draw the architecture twice: the initial deployable version and the high-scale evolution. For every new component, name the measured trigger that introduces it, the failure it adds, and the operational owner. If you cannot justify a component, remove it.

Troubleshooting

Duplicate results after runner retries -> Require a client-generated result ID, enforce unique attempt identity, and compare payload hashes on conflict. Do not rely on a check followed by insert because concurrent requests race.

Ingestion latency spikes at suite completion -> Batch with a strict payload limit, add jitter, keep aggregation asynchronous, and inspect database connection saturation. Backpressure callers with 429 and Retry-After rather than allowing unbounded queues in API memory.

Run totals disagree with visible results -> Define whether totals include every attempt or only the final attempt. Store both raw attempts and a documented derived final outcome. Rebuild aggregates from immutable facts after fixing consumer logic.

Artifact links return access denied -> Confirm tenant authorization, object-key prefix, clock synchronization, upload completion, and presigned URL expiration. Never solve this by making the bucket public.

Queries slow down as history grows -> Capture query plans, add access-pattern indexes, paginate by cursor, archive old partitions, and move broad analytical scans away from the transactional primary.

Outbox backlog keeps increasing -> Alert on oldest unpublished event, scale consumers, isolate poison messages, and keep retries bounded. Preserve failed events in a dead-letter path with enough context for replay.

Where To Go Next

Return to the complete Senior SDET system design interview guide and practice the same sequence: requirements, estimates, API, data model, critical flows, failures, and tradeoffs. Then extend this system through these related designs:

• Design a cross-platform test execution service to connect scheduling, workers, leases, and result submission.
• Design a flaky test detection system to compute explainable classifications from attempt history.
• Design a device farm scheduler to correlate scarce devices, sessions, and artifacts.
• Review API testing interview questions for experienced engineers to sharpen reliability and contract-testing discussions.

Practice with a 45-minute timer. Spend about five minutes clarifying, five estimating, ten on the high-level design, fifteen on schema and critical flows, and the remainder on scaling and failures. Treat those times as a rehearsal aid, not a universal interview format.

Interview Questions and Answers

Q: Why not store everything in object storage?

Object storage is excellent for immutable blobs but inefficient for low-latency relational filters such as failures by run and history by test. Keep searchable metadata in a transactional store and blobs in object storage. Export columnar snapshots for broad analytics when needed.

Q: How do you make result ingestion idempotent?

Use a stable client-generated result ID and database uniqueness constraints. An identical retry returns the prior outcome, while the same ID with different content returns a conflict. Consumers also deduplicate because queue delivery is commonly at least once.

Q: How do you represent retries?

Store each attempt as an immutable row with its attempt number. Derive the final test outcome using an explicit policy and retain all attempts for debugging and flaky-test analysis. Never overwrite the first failure with the later pass.

Q: Would you choose SQL or NoSQL?

Begin from access patterns and guarantees. SQL fits relational identity, constraints, transactions, and common run queries. A key-value or wide-column store becomes attractive at extreme write scale with known denormalized queries, but it shifts uniqueness and aggregation complexity into the application.

Q: How do you prevent a noisy tenant from harming others?

Apply authenticated tenant identity, quotas, bounded batches, fair queues, connection limits, and rate limiting. Partition or shard by tenant at higher scale. Monitor latency and resource use by tenant so enforcement is evidence based.

Q: What should happen if analytics is unavailable?

The durable result write should continue. An outbox retains events until workers recover, and dashboards expose freshness or lag. Alert before backlog threatens retention or storage capacity.

Q: How do you delete old data safely?

Apply policy by tenant and data class, use object lifecycle rules, and drop or archive eligible time partitions. Respect legal holds, audit deletion, and reconcile metadata with objects. Test restore and deletion workflows rather than trusting configuration alone.

Q: How would you support multiple regions?

Prefer a home region per tenant to avoid write conflicts. Route writes there and replicate read models or artifacts as required. Only adopt active-active writes when explicit availability goals justify conflict resolution, global uniqueness, and higher operational cost.

Q: How do you calculate flaky-test rates?

Use completed attempts over a defined window and separate product failures, infrastructure failures, and retries. Store raw facts plus versioned derived metrics. Exclude canceled or invalid runs according to a documented policy.

Q: What metrics prove the system is healthy?

Track accepted and rejected result rates, ingest latency, database saturation, duplicate and conflict counts, outbox age, consumer lag, artifact verification failures, query latency, and retention backlog. Segment key signals by tenant and region.

Best Practices

• Keep raw attempts immutable and derived summaries rebuildable.
• Validate at the API and enforce invariants again in the database.
• Put tenant identity in every record and derive it from trusted authentication.
• Store artifacts outside the relational database with checksum and lifecycle metadata.
• Make producers and consumers idempotent.
• Design indexes from named queries and confirm them with query plans.
• Measure queue age and data freshness, not only queue length.
• State recovery point and recovery time objectives, then test restoration.
• Introduce partitions, shards, and regions only behind measured triggers.
• Explain what happens when every dependency fails.

Avoid three interview traps: presenting a diagram without requirements, claiming exactly-once delivery solves duplicates, and treating retention as a cost-only issue. Retention also affects privacy, compliance, debugging, and model quality for flaky-test analytics.

Conclusion

To succeed in a design test result storage system interview, separate metadata from artifacts, preserve immutable attempt history, make ingestion idempotent, and move enrichment behind a durable outbox. Anchor every scale decision to workload and every reliability claim to a failure scenario.

Build the runnable version, test duplicate delivery and dependency outages, then rehearse the evolution from one database to partitioned and tenant-sharded storage. That progression demonstrates senior judgment more convincingly than beginning with a complex distributed architecture.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Design a Flaky Test Detection System Interview
• Design a Cross-Platform Test Execution Service
• Design Device Farm Scheduler Interview: An SDET System Design Guide
• How to Design a test data strategy (2026)
• Senior SDET System Design Interview Complete Guide (2026)
• A/B test validation: A Complete Guide for QA (2026)