Service-layer aggregation
90.26 ms → 2.11 ms
42.8× in this local run · 1 warm-up, then the median of 5 timed runs
ServiceFlow
I used this FastAPI order and ticket API to investigate slow reports, payment failures and inconsistent order totals. I traced each problem through API responses, request logs and stored data, then checked the changes with regression tests.
Public repository · Generated local dataset · No hosted demo
PythonFastAPISQLitepytestApplication Support
Verified locally
Generated SQLite dataset
500 customers / 2,000 orders
5,966 order items
Regression tests
28-test pytest suite
The same suite passes in both healthy and fault modes
Issues investigated
These investigations use deliberately introduced faults and generated data. Each linked note includes the reproduction steps, relevant code and tests.
INC-001 Slow order reports
- What I found
- The report returned HTTP 200 and 500 rows, but an N+1 query loop and missing customer index made it slow.
- What I changed
- Added the customer index and replaced the loop with one JOIN … GROUP BY query.
INC-002 Payment failures
- What I found
- The payment client had no timeout, and failed charges left orders marked paid.
- What I changed
- Added a 3-second client timeout. Orders are now marked paid only after a successful charge; failures return a generic 502 without retries.
INC-003 Invalid order data
- What I found
- The API accepted negative quantities or missing prices and stored incorrect order totals.
- What I changed
- Required qty ≥ 1 and a non-negative price before inserting an order. Invalid items are rejected without saving a row.
Why the order report was slow
- I first checked that the slow report still returned HTTP 200 and the expected 500 rows. The issue was query time, not missing results.
- The service fetched the customer list once, then ran a separate SUM and COUNT for each customer: 1 + 2N database queries. The faulty configuration also lacked an index on orders.customer_id.
- The fix combined an index on orders.customer_id with a single grouped aggregation. Regression tests check the totals and confirm whether the index exists in each mode.
SCAN orders
SEARCH orders USING INDEX ix_orders_customer_id
This EXPLAIN QUERY PLAN comparison checks a separate per-customer SUM query. It shows how the index changes that lookup. The repository also contains the plan for the full JOIN … GROUP BY query.
Median service-function time fell from 90.26 ms to 2.11 ms over five timed runs after one warm-up. The function ran within the same process, so this measurement excludes HTTP overhead. The benchmark file records the individual samples; timings vary between machines.
Scope and limitations
- The application runs locally with generated data and simulated faults. These results do not represent production incidents, SLA performance or on-call experience.
- The measurements and tests use SQLite. PostgreSQL can be configured but has not been verified. The API has no authentication or role-based access control.
- Paid checkout requires the local mock payment API. Tests check the httpx timeout setting and a raised TimeoutException; an end-to-end timeout duration was not measured. Retrying a payment would need an idempotency key.