High-level design
Part 1 of 3 · URL ShortenerURL Shortener - Spec, Flask API, SQLite & Concepts
Machine-coding URL shortener hub: Flask+SQLite spec, steps, HTTP contracts, concepts, create/redirect/delete/stats.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Question ladder
L1
What does create accept?
Answer
url, owner_token, optional code, optional ttl_seconds. 201 with the row, 400 on validation, 409 when the code is taken.
L2
Why base62 from a counter?
Answer
Short codes, no hash collision, and an easy story for one database. UNIQUE is the backstop.
L3
Why 302 in the coding API?
Answer
Caches and browsers will not freeze the redirect, so hit counts and TTL changes stay visible.
L4
How do two threads avoid the same auto code?
Answer
Bump the counter and insert under one lock or transaction. UNIQUE still returns 409 if they race.
L5
What does redirect do to hits?
Answer
One conditional UPDATE increments hits only when the row is not deleted and not expired, and only when rowcount is 1.
L6
Soft delete or hard delete?
Answer
Soft delete keeps an audit trail and avoids reclaim races. Say so if the product wants the vanity code reused.
L7
What do you leave for the scale-up page?
Answer
ID generation choices, Redis cache-aside, sharding by code, 301 versus 302 at a CDN, and async analytics.
Failure modes
Code taken
Two creates use the same vanity code. UNIQUE fails and the API returns 409 CODE_TAKEN.
Dead or expired redirect
The conditional UPDATE matches zero rows. The handler returns 404 instead of a stale Location.
Unpinned memory database
A new connection to :memory: has no schema. The coding solution pins one connection.
Misconceptions
301 is always better because it is cacheable.
301 hides hit counts and TTL changes behind intermediaries. Use it only when that cache is intentional.
A hash of the URL is a free unique code.
Prefixes collide and are predictable. The coding round wants a counter or an explicit collision policy.
Check-then-set in Python is enough for hits.
Two requests can read the same count. The increment belongs in a conditional UPDATE.
Interviewer traps
Design the multi-region cache before the HTTP contract.
Finish method, path, status, and JSON. Then name the scale-up sibling.
Return 404 for a wrong owner without saying why.
403 reveals that the code exists. 404 hides it. State the threat model.
Design scenario
Same prompt for every reader.
Requirements
Custom and auto codes, TTL, ownership on delete, atomic hit counts, and strict status codes.
Traffic / scale
One process. Create is rare. Redirect is the hot path you will scale on the next page.
Latency
A SQLite transaction on the same machine. No network hop in the coding round.
Consistency
A code is unique. A hit increment is not lost under threads.
Availability
If the row is missing, deleted, or expired, redirect is 404.
Failure assumptions
- Two clients can request the same vanity code.
- Eight threads can hit one code at once.
Constraints
- Do not invent a key-generation service in the coding round.
- Do not put analytics writes on the redirect path.
Prompt
Ship a single-process URL shortener an interviewer can run: create, redirect, metadata, delete, and stats.
API
Which status codes distinguish validation, conflict, forbidden, and missing?
Data
What columns does urls need, and where does auto_seq live?
Architecture
What stays in this process, and what do you point at on the scale-up page?
What to build in the coding round
Prefer
Counter plus base62 on one SQLite file
Short codes, no hash collision, and UNIQUE as the last line of defense.
- Custom codes are a regex plus IntegrityError mapped to 409.
- Hits use a conditional UPDATE, not a Python read-modify-write.
- 302 keeps counts and TTL observable.
Alternative
Snowflake, a key service, or a hash of the URL
Those are scale-up answers. They are the wrong first design for one process.
- A URL hash collides and is predictable.
- A distributed id needs a clock or another service.
- Start there only after the HTTP contract works.
Overview
Build a URL shortener as a machine-coding problem: a Flask HTTP API over SQLite with create (custom or auto code), redirect, metadata, ownership-checked delete, TTL expiry, hit counting, aggregate stats, strict status codes and JSON contracts, and concurrency-safe read-modify-write. This hub covers the spec, design steps, HTTP contracts, concepts used, and a compact walkthrough. The solution sibling has the full runnable app and tests. The scale-up sibling covers ID generation choices, caching, sharding and analytics for a production HLD interview.
Spec summary
| Capability | Behavior |
|---|---|
| Create | Body: url, owner_token, optional code, optional ttl_seconds. Auto code from base62 sequence if omitted. Validate URL, code charset length, TTL bounds. 201 with row. 400 validation. 409 code taken. |
| Redirect | GET /r/<code> -> 302 Location long URL if alive; 404 if missing, deleted or expired. Increment hits atomically. |
| Metadata | GET /api/v1/urls/<code> -> code, url, created_at, expires_at, hits, expired flag. 404 if gone. |
| Delete | DELETE with owner_token in JSON or X-Owner-Token. Soft-delete. 204 OK, 403 wrong owner, 404 missing. |
| Stats | GET /api/v1/stats -> total_urls, active_urls, total_hits (non-deleted). |
| Concurrency | Hit path and auto-code allocation safe under threads (lock + conditional UPDATE). |
Step-by-step design
- Write the HTTP contract table (method, path, status codes, JSON shapes).
- Choose SQLite schema:
urls(code UNIQUE, long_url, owner_token, created_at, expires_at, hits, deleted)plusmeta(auto_seq). - Decide ID strategy for the coding round: monotonic counter encoded as base62; custom codes validated with a regex.
- Implement create with uniqueness via UNIQUE + IntegrityError -> 409.
- Implement resolve as a single transaction: read row, check deleted/expiry,
UPDATE hits = hits + 1 WHERE ...and require rowcount 1. - Soft-delete with ownership check; never leak existence to wrong owners beyond 403 vs 404 policy you state.
- Expose stats with simple aggregates.
- Add tests for validation, conflict, TTL, ownership, concurrent hits, and HTTP status codes.
- Only then discuss scale-up (cache, shard, analytics) on the sibling page.
Create, then redirect
Diagram 1. Custom and auto codes share one uniqueness check. A later redirect increments hits only while the row is alive.
- 1
POST /api/v1/urls
The client sends url, owner_token, and optional code and ttl_seconds. - 2
Validate url, owner, code, and ttl
Reject a bad URL, owner, code charset, or TTL bound before any insert. - 3
Custom code or auto code
Insert the vanity code as given, or bump auto_seq and base62-encode it. - 4
UNIQUE constraint
A taken code is 409 CODE_TAKEN. A new row is 201 with the JSON body. - 5
Later GET /r/code
Load the row and run a conditional UPDATE that increments hits. - 6
404 or 302
rowcount 1 means the link is alive, so respond 302. Otherwise 404.
Decisions
- 1
1. Client POST /api/v1/urls
- next2. Validate url owner code ttl
- 2
2. Validate url owner code ttl
- next3. Custom code?
- ?
3. Custom code?
- Yes4. INSERT custom code
- No5. Bump auto_seq and base62 encode
- 4
4. INSERT custom code
- next7. UNIQUE ok?
- 5
5. Bump auto_seq and base62 encode
- next6. INSERT auto code
- 6
6. INSERT auto code
- next7. UNIQUE ok?
- ?
7. UNIQUE ok?
- No8. Failure: 409 CODE_TAKEN
- Yes9. Return 201 JSON
- 8
8. Failure: 409 CODE_TAKEN
- 9
9. Return 201 JSON
- next10. Later GET /r/code
- 10
10. Later GET /r/code
- next11. Atomic hit UPDATE if alive
- 11
11. Atomic hit UPDATE if alive
- next12. rowcount 1?
- ?
12. rowcount 1?
- No13. Failure: 404
- Yes14. 302 redirect
- 13
13. Failure: 404
- 14
14. 302 redirect
Lesson map
URL Shortener - Spec, Flask API, SQLite & Concepts
Machine-coding URL shortener hub: Flask+SQLite spec, steps, HTTP contracts, concepts, create/redirect/delete/stats.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1. Client POST /api/v1/urls"] b["2. Validate url owner code ttl"] c["3. Custom code?"] d["4. INSERT custom code"] e["5. Bump auto_seq and base62 encode"] f["6. INSERT auto code"] g["7. UNIQUE ok?"] h["8. Failure: 409 CODE_TAKEN"] i["9. Return 201 JSON"] j["10. Later GET /r/code"] k["11. Atomic hit UPDATE if alive"] l["12. rowcount 1?"] m["13. Failure: 404"] n["14. 302 redirect"] a -->|continues| b b -->|continues| c c -->|Yes| d c -->|No| e e -->|continues| f d -->|continues| g f -->|continues| g g -->|No| h g -->|Yes| i i -->|continues| j j -->|continues| k k -->|continues| l l -->|No| m l -->|Yes| n
Decisions
- 1
Need custom vanity code?
- YesValidate charset then UNIQUE
- NoAuto base62 from counter
- 2
Validate charset then UNIQUE
- nextCollision?
- 3
Auto base62 from counter
- next201 created
- ?
Collision?
- Yes409 conflict
- No201 created
- 5
409 conflict
- 6
201 created
Concepts used, learn more
Read the underlying idea on its own study page. This lesson applies it. It does not replace those pages.
- API Design — Naming, Paths, Routing & Contracts
- API Idempotency Keys
- Rate Limiting: Token Bucket, Leaky Bucket & Sliding Window
- Mutexes, Condition Variables, Deadlocks & Happens-Before
- Mutex vs RWLock
- Atomics vs Locks
- Redis Cache-Aside, Invalidation & Stampede Prevention
- Consistent Hashing: Rings, Virtual Nodes & Replica Placement
- Database Sharding & Partitioning — Keys, Hotspots & Rebalancing
- Partition Strategies — Range, Hash, List & Composite
- CDNs, Cache Hierarchy & Origin Shielding
- Load Balancing — L4 vs L7, Algorithms & Health Checks
- Negative Caching
- Rendezvous Hashing (HRW): Highest Random Weight
- Hashing, Frequency Maps & Counting — Two Sum Family & Anagrams
| Concept | Role | Existing Study page |
|---|---|---|
| API contracts and status codes | Strict JSON and HTTP semantics | API design |
| Idempotency / ownership | Delete and create conflict handling | Idempotency keys |
| Rate limiting | Abuse on create (scale-up) | Rate limiting |
| Mutex / concurrency | Process lock around RMW | Mutexes |
| Cache-aside | Hot redirect path (scale-up) | Redis cache-aside |
Comparative: ID choices in the coding round vs scale-up
| Approach | Pros | Cons | When |
|---|---|---|---|
| Counter + base62 (this solution) | Short codes, no hash collision, easy | Needs coordinated counter | Single SQLite / coding interview |
| Random base62 | No counter hotspot | Retry on collision; longer for uniqueness | Multi-writer without KGS |
| Hash(url) prefix | Deterministic | Collisions and enumeration | Rarely ideal alone |
| Snowflake / KSUID | Time-sortable, distributed | Longer codes unless re-encoded | Multi-region writers |
| Pre-generated KGS | No runtime coordination | Key DB ops, leak risk | Very high create QPS |
Interview Q&A
Why 302 instead of 301 for the coding API?
Answer
302 keeps redirects out of aggressive intermediate caches so hit counts and TTL changes stay observable. 301 is fine for permanent marketing links once analytics are async. See the scale-up sibling.
How do you prevent two threads from double-assigning the same auto code?
Answer
Serialize counter bump and insert under one lock/transaction; UNIQUE on code is the last line of defense.
Soft delete vs hard delete?
Answer
Soft delete preserves audit and avoids reclaim races; hard delete frees the vanity code for reuse if product wants that (state the policy).
What does stats return?
Answer
total_urls, active_urls, and total_hits for rows that are not deleted. Active means not expired.
Why reject unvalidated custom codes?
Answer
A code is a path segment. The regex keeps it to 4-32 characters of letters, digits, underscore, and hyphen so ../ and spaces never become a route.
Where do rate limits and cache-aside live?
Answer
Not in this coding contract. Abuse control is rate limiting. The hot redirect cache is Redis cache-aside. The scale-up sibling places both.
Pitfalls
- Using plain
:memory:SQLite with a new connection per request (schema vanishes). Pin a connection or use a file. - Check-then-set hits without conditional UPDATE.
- Returning 404 for wrong owner (hides the resource) vs 403 (reveals it) without stating the threat model.
- Allowing unvalidated custom codes (
../, spaces).
Series map
- This hub - spec, steps, concepts.
- Flask + SQLite full solution, concurrency and tests.
- Scale-up HLD - ID generation, caching, sharding, analytics.
Go Deeper
Related
The series pager also walks these pages.