<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vishwajeet Gupta on Medium]]></title>
        <description><![CDATA[Stories by Vishwajeet Gupta on Medium]]></description>
        <link>https://medium.com/@vjgupta78?source=rss-b6c765222c1b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*n7PGRh3wMhWM2WG-7xgKwg.jpeg</url>
            <title>Stories by Vishwajeet Gupta on Medium</title>
            <link>https://medium.com/@vjgupta78?source=rss-b6c765222c1b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 18:12:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@vjgupta78/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Designing a Dropbox-Like File Storage and Synchronisation System]]></title>
            <link>https://medium.com/@vjgupta78/designing-a-dropbox-like-file-storage-and-synchronisation-system-cbd3542a7f4b?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/cbd3542a7f4b</guid>
            <category><![CDATA[system-design-interview]]></category>
            <category><![CDATA[system-design-concepts]]></category>
            <category><![CDATA[backend-development]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Mon, 16 Nov 2026 18:06:01 GMT</pubDate>
            <atom:updated>2026-11-16T18:06:01.893Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A system-design deep dive inspired by Dropbox, Google Drive and OneDrive</em></p><p>Dropbox looks like a very simple product. You save a file in a folder, and it appears on your phone, your laptop and your other devices. You share it with a friend, and your friend can open it too.</p><p>But the real fun starts when things go wrong:</p><ul><li>Someone uploads a 50 GB file over a weak internet connection.</li><li>The app crashes when 90% of the file is already uploaded.</li><li>Two people edit the same document at the same time.</li><li>A user logs in from a new laptop that has 100,000 files in the account.</li><li>A shared file is downloaded by thousands of users.</li><li>One faulty client opens thousands of parallel upload connections.</li></ul><p>So the challenge is not just <em>storing</em> files. The challenge is building a <strong>reliable file storage and synchronisation system</strong>.</p><p>Let us start.</p><h3>1. Requirements</h3><h3>Functional requirements</h3><ol><li>Users can upload files from different devices.</li><li>Users can download files from different devices.</li><li>Users can share files with other users, and view files shared with them.</li><li>Changes made on one device should sync automatically to the other devices.</li><li><strong>Delta sync:</strong> when a large file is modified, we should reuse already-stored content and transfer only the chunks that are missing.</li><li><strong>Conflict handling:</strong> concurrent edits to the same file follow a <em>Last Write Wins</em> (LWW) policy. The latest successfully committed update becomes the current version.</li></ol><h3>Non-functional requirements</h3><ol><li><strong>High availability:</strong> files remain accessible even if some application instances fail.</li><li><strong>Scalability:</strong> millions of users and a very large number of files.</li><li><strong>Reliability:</strong> committed data and metadata must be durable, and file integrity must be verified.</li><li><strong>Performance:</strong> metadata operations and sync notifications should be fast. Large transfers should be resumable.</li><li><strong>Security:</strong> only authorised users can access files. Data is encrypted in transit and at rest.</li><li><strong>Recoverability:</strong> the system recovers from crashes and network failures.</li><li><strong>Large file support:</strong> files up to 50 GB.</li></ol><h3>Out of scope</h3><p>To keep the design manageable, we will not cover:</p><ul><li>Real-time collaborative editing (like Google Docs).</li><li>File previews and in-app document editing.</li><li>Per-user storage plans and billing.</li><li>File format conversion.</li><li>Users can restore old versions and deleted files within the retention period.</li></ul><h3>A note on consistency</h3><p>We should not simply say that Dropbox chooses availability over consistency.</p><p>Different operations need different guarantees.</p><p>For example, another device receiving a file update a few seconds later is acceptable. But allowing a user whose access has been revoked to obtain new download permissions because of stale authorization data is not.</p><p>We’ll use strong consistency for critical metadata and permissions, while allowing asynchronous propagation for device synchronization.</p><h3>2. Capacity Estimation</h3><p>Let’s put some numbers down before deciding on the architecture.</p><p><strong>System assumptions</strong></p><ul><li>Daily active users: 1 million</li><li>New uploads per user per day: 0.1</li><li>Average file size: 20 MB</li><li>Download volume: 4x uploaded data</li><li>Peak traffic multiplier: 5x</li><li>Average CDC chunk size: 8 MiB</li></ul><p>All data-transfer calculations below use decimal TB and MB. Chunk sizes use binary MiB.</p><h3>2.1 Upload Volume</h3><p>First, calculate the number of uploads per day.</p><pre>Uploads per day = 1,000,000 x 0.1 = 100,000 uploads/day</pre><p>Now calculate daily logical upload volume.</p><pre>Daily upload data = 100,000 x 20 MB = 2,000,000 MB = 2 TB/day</pre><p>This represents the logical file data processed by clients before chunk reuse.</p><p>Because we use content-addressed chunks, the actual bytes transferred to S3 can be lower when chunks already exist in the owner’s storage scope.</p><p>Average ingress throughput:</p><pre>2 TB / 86,400 seconds = approximately 23 MB/second</pre><p>Assuming peak traffic is five times the average:</p><pre>Peak ingress = 23 x 5 = approximately 115 MB/second</pre><h3>2.2 Download Volume</h3><p>We assume downloads are four times the uploaded data volume.</p><pre>Daily download data = 2 TB x 4 = 8 TB/day</pre><p>Average aggregate download throughput:</p><pre>8 TB / 86,400 seconds = approximately 93 MB/second</pre><p>If we apply the same five-times peak assumption to downloads:</p><pre>Peak download throughput = 93 x 5 = approximately 463 MB/second</pre><h3>2.3 Chunk Processing and Upload Requests</h3><p>Our architecture uses content-defined chunking.</p><p>Assuming an illustrative average chunk size of 8 MiB:</p><pre>Chunks per day = 2 TB / 8 MiB = approximately 238,000 chunks/day</pre><p>Average chunk processing rate:</p><pre>238,000 / 86,400 = approximately 2.8 chunks/second</pre><p>This is an estimate before deduplication.</p><p>The client and backend still need to process the manifest entries, but only missing chunks are uploaded to S3.</p><h3>2.4 Storage Growth</h3><p>Assuming all uploaded data is retained:</p><pre>Daily logical upload volume = 2 TB<br><br>30-day logical upload volume = 2 x 30 = 60 TB/month</pre><p>Actual physical storage growth depends on:</p><ul><li>Chunk deduplication.</li><li>Compression</li><li>Retained file versions.</li><li>User deletions and retention policies.</li><li>Replication and backup requirements.</li></ul><h3>2.5 What Does This Tell Us?</h3><p>These estimates help identify which components need attention as the system grows.</p><ul><li><strong>S3:</strong> Stores independently uploaded content chunks.</li><li><strong>CDN:</strong> Handles large download volumes and reduces origin traffic.</li><li><strong>PostgreSQL:</strong> Stores metadata, versions, manifests and per-user change feeds.</li><li><strong>Message Queue:</strong> Handles asynchronous event processing and notifications.</li><li><strong>Redis:</strong> Can reduce frequently repeated metadata lookups.</li><li><strong>Client sync agent:</strong> Handles chunk processing, persistent upload/download queues and bounded parallelism.</li></ul><h3>3. Core Entities</h3><p>These are the main building blocks of our system.</p><ul><li><strong>User:</strong> a person using the system.</li><li><strong>Folder:</strong> a directory that contains files and other folders. It refers to its parent folder, so we get a hierarchy.</li><li><strong>File:</strong> a <em>logical</em> file. It stores name, owner, parent folder and the current version.</li><li><strong>FileVersion:</strong> an immutable snapshot of the file’s content. It references the chunks that make up that version.</li><li><strong>Chunk:</strong> an independently stored piece of file content, identified by its content hash.</li><li><strong>FileVersionChunk:</strong> maps chunks to a file version and keeps their order using a chunk index.</li><li><strong>UploadSession:</strong> tracks one ongoing upload.</li><li><strong>UploadSessionChunk:</strong> temporary references to chunks needed by an upload, so that garbage collection does not delete them too early.</li><li><strong>Share:</strong> maps a file to a user who has permission to access it.</li><li><strong>Device:</strong> a laptop, phone or desktop installation. It remembers the last sync cursor it applied.</li><li><strong>Change:</strong> a durable change-log entry (update, rename, delete, permission change). Devices use a cursor to fetch changes they have not yet applied.</li></ul><h3>Understanding File vs FileVersion</h3><p>This is an important point. A <strong>File</strong> is a logical resource. A <strong>FileVersion</strong> is one immutable snapshot of its content.</p><p>When a user edits project-report.pdf, we never overwrite the old version. We create a <em>new</em> version and move the file&#39;s current_version_id pointer. The old versions stay available for recovery. This one decision gives us version history, restore, and a clean way to handle concurrent edits.</p><h3>Entity Relationships</h3><p>The following diagram shows how the main entities relate to each other.</p><pre>                         User<br>                          |<br>             +------------+------------+<br>             |            |            |<br>             v            v            v<br>          Folder        File         Device<br>             |            |            |<br>             |            |            +---- Change Feed<br>             |            |<br>             |            +---- current_version_id<br>             |            |              |<br>             |            |              v<br>             |            +--------&gt; FileVersion<br>             |                           |<br>             |                           v<br>             |                    FileVersionChunk<br>             |                           |<br>             |                           v<br>             |                         Chunk<br>             |                           |<br>             |                           v<br>             |                        S3 Object<br>             |<br>             +---- parent_folder_id<br><br>----------------------------------------------------------------------------<br><br>            File<br>             |<br>             +---- UploadSession<br>             |         |<br>             |         v<br>             |    UploadSessionChunk<br>             |         |<br>             |         v<br>             |       Chunk<br>             |<br>             +---- Share ---- User (recipient)</pre><h3>4. APIs</h3><p>We’ll expose REST APIs for the initial design.</p><p><strong>File APIs</strong></p><pre>POST   /files<br>GET    /files/{fileId}<br>PATCH  /files/{fileId}<br>GET    /files/{fileId}/download<br>DELETE /files/{fileId}</pre><p><strong>Upload APIs</strong></p><pre>POST   /files/{fileId}/upload-sessions<br>GET    /upload-sessions/{uploadSessionId}<br>PUT    &lt;presigned-url&gt;<br>POST   /upload-sessions/{uploadSessionId}/commit<br>DELETE /upload-sessions/{uploadSessionId}</pre><p><strong>Version APIs</strong></p><pre>GET  /files/{fileId}/versions<br>GET  /files/{fileId}/versions/{versionId}/manifest</pre><p><strong>Sharing APIs</strong></p><pre>POST   /files/{fileId}/shares<br>DELETE /files/{fileId}/shares/{userId}<br>GET    /users/me/shared-files</pre><p><strong>Synchronization APIs</strong></p><pre>GET  /sync/initial<br>GET  /sync/changes?cursor={cursor}<br>POST /sync/acknowledge</pre><p>These APIs coordinates uploads and downloads, verifies metadata, checks permissions, and generates short-lived pre-signed URLs.</p><p>Clients transfer the actual chunks directly to S3 or through the CDN.</p><h3>4.1 Two important API contracts</h3><p><strong>1. Create an upload session.</strong> The client first splits the file into content-addressed chunks, then sends the ordered manifest.</p><pre>POST /files/{fileId}/upload-sessions<br>{<br>  &quot;chunkerVersion&quot;: 1,<br>  &quot;contentSha256&quot;: &quot;file-content-hash&quot;,<br>  &quot;manifest&quot;: [<br>    {&quot;hash&quot;: &quot;h1&quot;, &quot;size&quot;: 4194304},<br>    {&quot;hash&quot;: &quot;h2&quot;, &quot;size&quot;: 4194304},<br>    {&quot;hash&quot;: &quot;h3&quot;, &quot;size&quot;: 4194304},<br>    {&quot;hash&quot;: &quot;h4&quot;, &quot;size&quot;: 4194304}<br>  ]<br>}</pre><p>The backend checks which chunks already exist in the owner’s storage and gives pre-signed PUT URLs <em>only for the missing ones</em>.</p><pre>{<br>  &quot;uploadSessionId&quot;: &quot;upload-123&quot;,<br>  &quot;missingChunks&quot;: [ { &quot;hash&quot;: &quot;h3&quot;, &quot;putUrl&quot;: &quot;https://...&quot; } ]<br>}</pre><p><strong>2. Retrieve a version manifest.</strong> The client needs the ordered manifest to rebuild the file.</p><pre>GET /files/{fileId}/versions/{version}/manifest?pageToken=...<br>{<br>  &quot;chunks&quot;: [<br>    { &quot;index&quot;: 0, &quot;hash&quot;: &quot;h1&quot;, &quot;size&quot;: 4194304, &quot;downloadUrl&quot;: &quot;https://...&quot; },<br>    { &quot;index&quot;: 1, &quot;hash&quot;: &quot;h2&quot;, &quot;size&quot;: 4194304, &quot;downloadUrl&quot;: &quot;https://...&quot; }<br>  ],<br>  &quot;nextPageToken&quot;: &quot;...&quot;<br>}</pre><p>The backend checks permission first and returns short-lived download URLs. For files with thousands of chunks, the manifest is paginated.</p><h3>4.2 Idempotency</h3><p>We also use <strong>idempotency keys</strong> for file creation, upload-session creation and version commit. A retried request must not create duplicate files or versions.</p><h3>5. Data Model</h3><p>We use <strong>PostgreSQL</strong> for metadata and <strong>Amazon S3</strong> for actual file content. Our data is full of relationships (users, files, folders, versions, chunks, permissions), so PostgreSQL is a good starting point. DynamoDB could also work.</p><blockquote>The key idea: <em>PostgreSQL stores metadata and relationships. S3 stores content-addressed chunks.</em></blockquote><p><strong>Users, Folders and Files</strong></p><pre>users   (user_id BIGINT PK, email TEXT UNIQUE, created_at TIMESTAMPTZ)<br><br>folders (folder_id UUID PK, owner_id BIGINT, parent_folder_id UUID NULL,<br>         folder_name TEXT, created_at TIMESTAMPTZ, deleted_at TIMESTAMPTZ)<br><br>files   (file_id UUID PK, owner_id BIGINT, parent_folder_id UUID,<br>         file_name TEXT, current_version_id UUID,<br>         created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ, deleted_at TIMESTAMPTZ)<br><br>CREATE INDEX idx_folders_owner_parent ON folders(owner_id, parent_folder_id);<br>CREATE INDEX idx_files_owner_folder   ON files(owner_id, parent_folder_id);</pre><p>A NULL parent folder means a root-level folder. A files row is only a logical resource. current_version_id points to the latest <em>successfully committed</em> version.</p><p><strong>File versions</strong></p><pre>file_versions (<br>  version_id UUID PK, file_id UUID, version_number BIGINT,<br>  file_size BIGINT, content_sha256 TEXT, manifest_hash TEXT,<br>  chunker_version INT, created_by BIGINT, created_at TIMESTAMPTZ,<br>  UNIQUE(file_id, version_number)<br>)</pre><p>Notice there is no object_key here. A version does not point to one S3 object. It points to an <em>ordered list of chunks</em>. Every committed version is immutable.</p><p><strong>Chunks and the version-to-chunk mapping</strong></p><pre>chunks (<br>  chunk_id UUID PK, owner_id BIGINT, chunk_hash TEXT, chunk_size BIGINT,<br>  object_key TEXT UNIQUE, storage_checksum TEXT, status TEXT,<br>  created_at TIMESTAMPTZ, UNIQUE(owner_id, chunk_hash)<br>)<br><br>file_version_chunks (<br>  version_id UUID, chunk_index INT, chunk_id UUID,<br>  PRIMARY KEY(version_id, chunk_index)<br>)<br><br>CREATE INDEX idx_file_version_chunks_chunk ON file_version_chunks(chunk_id);</pre><p>The chunk_hash is the SHA-256 of the original chunk. It tells us if the chunk already exists. We scope deduplication <strong>per owner, not globally</strong>, so that nobody can find out whether another user&#39;s files contain some particular content.</p><p><strong>Upload sessions</strong></p><pre>upload_sessions (<br>  upload_session_id UUID PK, file_id UUID, owner_id BIGINT,<br>  expected_size BIGINT, content_sha256 TEXT, manifest_hash TEXT,<br>  chunker_version INT, status TEXT,<br>  committed_version_id UUID NULL, expires_at TIMESTAMPTZ, created_at TIMESTAMPTZ<br>)<br><br>upload_session_chunks (<br>  upload_session_id UUID, chunk_index INT, chunk_hash TEXT, chunk_size BIGINT,<br>  status TEXT, lease_expires_at TIMESTAMPTZ,<br>  PRIMARY KEY(upload_session_id, chunk_index)<br>)</pre><p>If the app crashes or the network drops, the client can resume with the same session (if not expired). committed_version_id lets us return the <em>same</em> version if the client retries a commit. The upload_session_chunks table protects chunks from garbage collection while an upload is in progress.</p><p><strong>Shares</strong></p><pre>file_shares (<br>  share_id UUID PK, file_id UUID, recipient_id BIGINT,<br>  permission TEXT, created_at TIMESTAMPTZ, revoked_at TIMESTAMPTZ<br>)<br><br>CREATE INDEX idx_shares_recipient ON file_shares(recipient_id, file_id)<br>  WHERE revoked_at IS NULL;<br>CREATE UNIQUE INDEX idx_unique_active_share ON file_shares(file_id, recipient_id)<br>  WHERE revoked_at IS NULL;</pre><p>We keep revoked shares as history, and still allow a fresh share later.</p><p><strong>Devices, change log and outbox</strong></p><pre>devices (device_id UUID PK, user_id BIGINT, last_ack_cursor BIGINT, last_seen_at TIMESTAMPTZ)<br><br>user_changes (<br>  user_id BIGINT, sequence_id BIGINT, resource_type TEXT, resource_id UUID,<br>  event_type TEXT, version_id UUID, created_at TIMESTAMPTZ,<br>  PRIMARY KEY(user_id, sequence_id)<br>)<br><br>user_change_counters (user_id BIGINT PK, last_sequence BIGINT)<br>outbox (<br>  event_id UUID PK, aggregate_type TEXT, aggregate_id UUID,<br>  event_type TEXT, payload JSONB, created_at TIMESTAMPTZ, published_at TIMESTAMPTZ<br>)</pre><ul><li>Each device keeps its own cursor. A laptop and a phone can be at different points in the feed.</li><li>Whenever we create a change, we increment the user’s counter and insert the change row <strong>in the same transaction</strong>. This gives every user a monotonically increasing sequence.</li><li>The <strong>outbox</strong> table helps us publish events reliably. When a version is committed, we update the pointer, insert change-log rows and insert an outbox event in one transaction. A background publisher later sends outbox rows to the message broker.</li></ul><p>The change log is the <em>source of truth</em> for sync. The message broker only delivers notifications.</p><h3>6. High-Level Design</h3><p>Let’s start with the basic architecture.</p><pre>                         +-------------------------+<br>                         |         Clients         |<br>                         |   Web / Desktop / App   |<br>                         +------------+------------+<br>                                      |<br>                                      v<br>                         +-------------------------+<br>                         |       DNS / CDN         |<br>                         |          WAF            |<br>                         +------------+------------+<br>                                      |<br>                                      v<br>                         +-------------------------+<br>                         |       External LB       |<br>                         +------------+------------+<br>                                      |<br>                    +-----------------+-----------------+<br>                    |                                   |<br>                    | HTTPS API                         | WebSocket / SSE<br>                    v                                   v<br>           +--------------------+             +----------------------+<br>           |    API Gateway     |             |   Realtime Gateway   |<br>           | Auth / Rate Limit  |             | Persistent Client    |<br>           +---------+----------+             | Connections          |<br>                     |                        +----------+-----------+<br>          +----------+----------+                        ^<br>          |          |          |                        |<br>          v          v          v                        |<br>    +----------+ +----------+ +-----------+              |<br>    |   File   | |   Sync   | |   Share   |              |<br>    | Service  | | Service  | |  Service  |              |<br>    +----+-----+ +----+-----+ +-----+-----+              |<br>         |            |             |                    |<br>         +------------+-------------+                    |<br>                      |                                  |<br>                      v                                  |<br>           +-------------------------+                   |<br>           |       PostgreSQL        |                   |<br>           |                         |                   |<br>           | File / Folder Metadata  |                   |<br>           | Shares / Permissions    |                   |<br>           | File Versions           |                   |<br>           | Ordered Chunk Manifests |                   |<br>           | Chunk Metadata          |                   |<br>           | Upload Sessions         |                   |<br>           | Durable Change Feed     |                   |<br>           | Transactional Outbox    |                   |<br>           +------------+------------+                   |<br>                        ^                                |<br>                        | Poll / Read Outbox             |<br>               +--------+---------+                      |<br>               |  Outbox Publisher|                      |<br>               +--------+---------+                      |<br>                        |                                |<br>                        v                                |<br>               +-------------------+                     |<br>               |   Message Queue   |                     |<br>               |    SQS / Kafka    |                     |<br>               +--------+----------+                     |<br>                        |                                |<br>                        v                                |<br>               +-------------------+                     |<br>               | Notification      |---------------------+<br>               | Service           |<br>               +-------------------+<br><br><br>DIRECT FILE DATA TRANSFER<br>-------------------------<br>    UPLOAD<br>    Client ----------------------------------------&gt; S3<br>           Pre-signed PUT URL<br>           Individual content-addressed chunks<br>    DOWNLOAD<br>    Client ---------------------&gt; CDN ----------------&gt; S3<br>           Request chunk          Cache miss / origin<br>    Client &lt;--------------------- CDN<br>           Chunk data</pre><p>There are three important parts:</p><ol><li><strong>Application services</strong> handle authentication, authorisation, metadata, upload coordination, version commits and sync. They <em>do not</em> carry the actual file bytes.</li><li><strong>PostgreSQL</strong> stores metadata, versions, permissions, upload sessions, chunk manifests and the change feed.</li><li><strong>Amazon S3</strong> stores the actual content-addressed chunks.</li></ol><p>Redis can be added to cache metadata and permissions, but PostgreSQL stays the source of truth.</p><h3>7. High-Level Design: Requirement by Requirement</h3><h3>7.1 Upload a File</h3><p>Let’s start with a simple approach and improve it.</p><p><strong>Bad: Upload the file through our application server</strong></p><p>The client sends the entire file to the File Service, and the File Service uploads it to S3.</p><pre>Client<br>  |<br>  | 50 GB file<br>  v<br>File Service<br>  |<br>  | 50 GB file<br>  v<br> S3</pre><p>This means the application server handles the entire file transfer.</p><p>For large files, this makes our application servers a data-transfer bottleneck. They consume network bandwidth, connection capacity and compute resources. If the server buffers file contents, memory pressure also becomes a concern.</p><p>We should avoid routing large file contents through our application servers.</p><p><strong>Better: Upload directly to S3 using pre-signed URLs</strong></p><p>A pre-signed URL is a temporary URL that grants permission to perform a specific operation on an S3 object.</p><p>The backend generates the URL using its AWS credentials. Generating the signature is a local cryptographic operation; it does not require an S3 API request.</p><p>The basic flow looks like this:</p><pre>Client          File Service       PostgreSQL          S3<br>  |                  |                  |               |<br>  |-- Initiate -----&gt;|                  |               |<br>  |                  |-- Save metadata-&gt;|               |<br>  |                  |                  |               |<br>  |&lt;-- Pre-signed URL|                  |               |<br>  |                  |                  |               |<br>  |-- Upload directly to S3 ---------------------------&gt;|<br>  |                  |                  |               |<br>  |&lt;-- Upload response ---------------------------------|</pre><p>The backend coordinates the upload, but the file bytes travel directly from the client to S3.</p><p>However, a single pre-signed URL for an entire file is not sufficient for our design. We also need delta sync, chunk reuse and immutable file versions.</p><p><strong>Best: Upload Content-Addressed Chunks</strong></p><p>Instead of storing each complete file as one S3 object, we split the file into smaller chunks and store those chunks independently.</p><p>We use Content-Defined Chunking (CDC) to determine chunk boundaries. A rolling fingerprint algorithm identifies content-dependent boundaries, while SHA-256 identifies the resulting chunk content.</p><p>The client creates an ordered manifest containing each chunk’s index, hash and size. It also calculates the complete file’s SHA-256 hash and includes the chunker version.</p><p>The upload process then follows these steps:</p><ol><li>The client sends the chunk manifest to the File Service through the upload-session API.</li><li>The backend checks which chunks already exist in the owner’s storage scope.</li><li>It reuses existing chunks and generates pre-signed PUT URLs only for missing chunks.</li><li>The client uploads missing chunks directly to S3.</li><li>Once the required chunks are available, the client calls the commit API to publish a new file version.</li></ol><p>The backend does not receive or proxy the chunk bytes.</p><p>The key idea is that the application backend manages the upload lifecycle, while clients transfer the actual chunks directly to S3.</p><h3>7.2 Download a File</h3><p>The same principle applies to downloads.</p><p><strong>Bad: Proxy the file through our application server</strong></p><p>S3 sends the file to our backend, and our backend forwards it to the client.</p><pre>Client             File Service                S3<br>  |                      |                      |<br>  |-- Download request -&gt;|                      |<br>  |                      |-- GET file ---------&gt;|<br>  |                      |&lt;-- File bytes -------|<br>  |&lt;-- File bytes -------|                      |</pre><p>This makes our application servers responsible for transferring the entire file.</p><p><strong>Better: Download directly through a CDN/S3</strong></p><p>The backend checks permissions and provides the client with authorized download URLs.</p><p>The client downloads the actual file data directly from the CDN/S3.</p><pre>Client          File Service          CDN             S3<br>  |                  |                 |               |<br>  |-- Download -----&gt;|                 |               |<br>  |                  |-- Check access  |               |<br>  |                  |-- Fetch metadata                |<br>  |                  |                 |               |<br>  |&lt;-- Signed URLs --|                 |               |<br>  |                  |                 |               |<br>  |-- GET chunks ---------------------&gt;|               |<br>  |                  |                 |-- Cache miss-&gt;|<br>  |                  |                 |&lt;-- Chunk -----|<br>  |&lt;-------------------- Chunk data ---|               |</pre><p><strong>Best: Downloading Using Chunk Manifests</strong></p><p>Our architecture does not store a complete file as one S3 object for every version. Instead, each immutable FileVersion contains an ordered mapping to the chunks required to reconstruct that version.</p><p>The download process follows these steps:</p><ol><li>The client requests a file download using GET /files/{fileId}/download.</li><li>The File Service checks whether the authenticated user owns the file or has an active share permission.</li><li>The backend resolves the requested version and provides access to its chunk manifest.</li><li>The client checks which required chunks are already available in its local cache.</li><li>It downloads missing chunks through the CDN/S3.</li><li>The client verifies the chunks and reconstructs the original file using the manifest order.</li></ol><p>For large files, the client can download multiple missing chunks in parallel, using bounded concurrency to avoid exhausting network and device resources.</p><h3>7.3 Why Do We Need a CDN?</h3><p>Imagine Alice shares a file with 10,000 users across different countries.</p><p>Without a CDN, downloads repeatedly reach the S3 origin. A CDN caches frequently requested chunks at edge locations closer to users, reducing latency and S3 traffic while handling popular downloads more efficiently.</p><p>Our content-addressed, immutable chunks are ideal for long-lived caching because their content never changes.</p><p>One caveat: revoking access prevents new authorizations but does not automatically invalidate already-issued credentials.</p><h3>7.4 Share a File</h3><p>Suppose Alice wants to share a file with Bob.</p><p>We need to answer two questions:</p><ol><li>Who has access to this file?</li><li>Which files have been shared with a particular user?</li></ol><p><strong>Bad Approach: Keep a List of Shared Users Inside the File Record</strong></p><pre>Files<br>-----<br>file_id<br>owner_id<br>file_name<br>shared_users = [Bob, Charlie, David]</pre><p>Now consider the query:</p><p>“Show me all files shared with Bob.”</p><p>We may need to scan a large number of file records.</p><p><strong>Better Approach: Maintain a Separate Mapping</strong></p><pre>File Shares<br>-------------------------------------<br>recipient_id       file_id<br>-------------------------------------<br>Bob                File-101<br>Bob                File-102<br>Charlie            File-101<br>David              File-103</pre><p>Now we can query using recipient_id.</p><pre>SELECT file_id<br>FROM file_shares<br>WHERE recipient_id = :user_id<br>  AND revoked_at IS NULL;</pre><p>We can also check whether a user has permission to access a particular file.</p><pre>Download Request<br>       |<br>       v<br> Check Ownership<br>       |<br>       +---- Owner? ---- Yes ----&gt; Allow<br>       |<br>       No<br>       |<br>       v<br> Check file_shares<br>       |<br>       +---- Valid VIEW/EDIT permission? ---- Yes ----&gt; Allow<br>       |<br>       No<br>       |<br>       v<br> Return 403 Forbidden</pre><p>We are using the file_shares table as our Access Control List (ACL) mapping.</p><p>For our initial design, we will focus on direct file sharing. We will not support folder sharing for now.</p><h3>7.5 Sync Files Across Devices</h3><p>This is one of the most interesting parts of Dropbox.</p><p>Suppose a user has two devices:</p><ul><li>Laptop A</li><li>Laptop B</li></ul><p>The user edits a document on Laptop A. How does Laptop B learn about the changes and download the updated file?</p><p>We need to handle two directions:</p><ul><li>Local device to cloud.</li><li>Cloud to other devices.</li></ul><p><strong>A. Local Device to Cloud</strong></p><p>We need a background sync agent running in the desktop application.</p><p>The agent monitors the local file system for changes. Operating systems provide different mechanisms:</p><ul><li>Windows: ReadDirectoryChangesW.</li><li>macOS: FSEvents.</li><li>Linux: inotify.</li></ul><p>When a file changes, the operating system sends an event to our agent.</p><p>If a file changes multiple times within a short window, we wait for the changes to settle and enqueue one upload job.</p><pre>            Local Folder<br>                  |<br>                  v<br>          +------------------+<br>          | File System      |<br>          | Watcher          |<br>          +--------+---------+<br>                   |<br>                   v<br>          +------------------+<br>          | Debounce /       |<br>          | Coalesce         |<br>          +--------+---------+<br>                   |<br>                   v<br>          +------------------+<br>          | Stable File      |<br>          | Check            |<br>          +--------+---------+<br>                   |<br>                   v<br>          +------------------+<br>          | SQLite Database  |<br>          | Persistent Jobs  |<br>          +--------+---------+<br>                   |<br>                   v<br>          +------------------+<br>          | Upload Workers   |<br>          +--------+---------+<br>                   |<br>            +------+------+<br>            |             |<br>            v             v<br>       File Service      S3<br>       Create Session  Direct PUT<br>       Get URLs        Missing Chunks<br>            |             ^<br>            |             |<br>            +------+------+<br>                   |<br>                   v<br>               Commit API</pre><p>SQLite provides the sync agent with <strong>persistent local storage</strong> for upload jobs, chunk-level progress, file synchronization state, and the device’s last applied cloud change-feed cursor.</p><p>A local file fingerprint identifies the file snapshot and detects changes, while CDC’s rolling fingerprint determines chunk boundaries.</p><p><strong>Why SQLite?</strong></p><p>The upload queue must survive application restarts.</p><p>If we keep pending jobs only in memory, restarting the laptop may cause us to lose track of pending uploads.</p><p>SQLite provides a persistent local database for the sync agent.</p><p><strong>Handling Missed Filesystem Events</strong></p><p>Filesystem watchers are useful, but they cannot guarantee that every change will be detected.</p><p>Events can be duplicated, coalesced or lost due to buffer overflow or application downtime.</p><p>So we need periodic reconciliation.</p><pre>File System Watcher<br>         |<br>         v<br>    SQLite Queue<br>         |<br>         v<br>    Upload Worker<br>         |<br>         v<br>       Cloud<br><br>-----------------------------------------------<br><br>Periodic Reconciliation<br>         |<br>         v<br>   Scan Local Folder<br>         |<br>         v<br> Compare with Local DB<br>         |<br>         v<br> Find Missed Changes<br>         |<br>         v<br>   Add Missing Jobs</pre><p>The watcher provides fast change detection. Periodic reconciliation protects us against missed events.</p><p>The agent should also distinguish changes made by the user from changes applied by the sync agent itself. Otherwise, downloading a cloud update could trigger another upload and create a synchronization loop.</p><p><strong>B. Cloud to Local Device</strong></p><p>Now let’s understand how Laptop B learns about changes made on Laptop A.</p><p>We have two common approaches.</p><p><strong>Polling</strong></p><p>The client periodically asks the backend:</p><pre>GET /sync/changes?cursor=12345</pre><p>This is easy to implement, but polling too frequently creates unnecessary requests. Polling too slowly delays synchronization.</p><p><strong>WebSockets or Server-Sent Events</strong></p><p>The server maintains an open connection and sends notifications when something changes.</p><p>This provides faster updates, but connections can drop and notifications can be missed.</p><p><strong>Recommended Approach: Hybrid</strong></p><p>Use WebSockets or SSE as the fast path, and cursor-based polling as the recovery path.</p><pre>                Laptop A<br>                    |<br>                    | Upload file<br>                    v<br>                File Service<br>                    |<br>                    v<br>               PostgreSQL<br>                    |<br>              +-----+-----+<br>              |           |<br>              v           v<br>          Change Feed  Outbox Event<br>                           |<br>                           v<br>                    Outbox Publisher<br>                           |<br>                           v<br>                     Message Queue<br>                           |<br>                           v<br>                 Notification Service<br>                           |<br>                           v<br>                    WebSocket / SSE<br>                           |<br>                           v<br>                       Laptop B<br>                           |<br>                           | Fetch changes<br>                           v<br>                       Sync API</pre><p>When Laptop A commits a new file version, the backend writes the change-feed record and transactional outbox event in the same PostgreSQL transaction.</p><p>The Outbox Publisher publishes the event to the message queue.</p><p>The Notification Service consumes it and notifies connected devices through WebSocket or SSE.</p><p>The notification contains lightweight information such as the file ID, version ID and change sequence. It does not contain the actual file contents.</p><p>An important principle:</p><p><strong>A push notification is only a signal. The durable change feed is the source of truth.</strong></p><p>Even if Laptop B misses a notification, it can retrieve all pending changes from the durable feed.</p><p>Each device maintains its own cursor because different devices can have different synchronization progress.</p><p>When Laptop B receives a notification, it fetches changes after its last successfully applied cursor. It applies those changes locally and acknowledges its progress only after successfully applying them.</p><p><strong>How Does Laptop B Download the Updated File?</strong></p><p>When Laptop B receives a FILE_UPDATED event:</p><ol><li>It fetches the manifest for the specified immutable version.</li><li>It checks which chunks are already available locally.</li><li>It downloads missing chunks through the CDN/S3.</li><li>It verifies the chunk hashes.</li><li>It reconstructs the file in the correct order.</li><li>It atomically replaces the local file.</li></ol><p>The sync agent must also ensure that changes applied from the cloud do not trigger new upload jobs. If the local file has unsynchronized user edits, the agent must preserve that pending snapshot rather than blindly overwrite it with the incoming cloud version.</p><h3>7.6 How Do Shared File Changes Reach Other Users?</h3><p>There is an additional problem.</p><p>Alice owns a file and shares it with Bob and Charlie.</p><p>Alice modifies the file.</p><p>How do Bob and Charlie receive the change?</p><p>A file update must reach the change feeds of the owner and all users who currently have access to that file.</p><p>We have two approaches.</p><p><strong>Fan-out on Write</strong></p><p>When Alice updates the file, the system creates change-feed entries for the owner and all active recipients.</p><pre>            Alice updates File-101<br>                       |<br>                       v<br>                  Change Event<br>                       |<br>              +--------+--------+<br>              |        |        |<br>              v        v        v<br>            Alice     Bob    Charlie<br>            Feed     Feed      Feed</pre><p>Each user’s change feed contains a separate entry referencing the same file and version.</p><p>For example:</p><pre>user_changes<br>-----------------------------------------<br>user_id   sequence   event_type<br>-----------------------------------------<br>Alice       501      FILE_UPDATED<br>Bob         421      FILE_UPDATED<br>Charlie     351      FILE_UPDATED</pre><p>Each user has an independent, monotonically increasing sequence because different users can have different synchronization progress.</p><p>This makes recipient synchronization queries simple because each user can read changes from their own feed.</p><p>The outbox publisher publishes notifications through the message queue.</p><p>However, a file shared with thousands of users creates many database writes. This is known as write amplification.</p><p>For example, a file shared with 100,000 users could require up to 100,000 change-feed writes for a single update.</p><p><strong>Fan-out on Read</strong></p><p>Instead of writing a change to every recipient’s feed, store the resource-level change once.</p><p>When a user synchronizes, resolve changes from the resources they currently have access to.</p><pre>            Alice updates File-101<br>                       |<br>                       v<br>                Resource Change<br>                     Feed<br>                       |<br>              +--------+--------+<br>              |                 |<br>              v                 v<br>             Bob             Charlie<br>            Query              Query<br>              |                 |<br>              v                 v<br>        Resolve Access    Resolve Access<br>              |                 |<br>              v                 v<br>         Return Changes   Return Changes</pre><p>This reduces write amplification because the system stores the resource change only once.</p><p>However, sync queries become more complex because the backend must identify all relevant shared resources, check permissions and combine changes into a consistent response.</p><p>Cursor management also becomes more complicated because changes can come from multiple resource-level feeds rather than a single per-user feed.</p><p><strong>Recommended Approach: Hybrid</strong></p><p>For our initial design, use fan-out-on-write for direct file sharing.</p><ul><li>When a file is updated, create change-feed entries for its owner and all active recipients.</li><li>When a file is shared, add a FILE_SHARED event to the recipient&#39;s change feed so their devices discover the new file.</li><li>When access is revoked, add a FILE_ACCESS_REVOKED event to the affected recipient&#39;s feed so their devices can remove the file from their synchronized view.</li></ul><h3>8. Supporting 50 GB Files</h3><p>Supporting files up to 50 GB introduces additional challenges.</p><p>Two things are particularly important:</p><ul><li>The user should see meaningful upload progress.</li><li>The upload should resume after network or application failure.</li></ul><p>Our architecture already uses Content-Defined Chunking (CDC) and independently stored, content-addressed chunks.</p><p>This approach allows us to process and upload smaller chunks independently without sending the entire file through our application servers.</p><p>For illustration, assume an average chunk size of 8 MiB.</p><p>A 50 GiB file would produce approximately 6,400 chunks.</p><h3>8.1 How Does Uploading a 50 GB File Work?</h3><p>Let’s walk through the upload process for a large file.</p><p><strong>Step 1: Split the file into chunks</strong></p><p>The desktop sync agent reads the file incrementally and applies CDC.</p><p>It calculates the SHA-256 hash and size of every chunk. The result is an ordered manifest.</p><p>For example:</p><pre>File: movie.mp4 (50 GiB)<br><br>+----------+----------+----------+----------+<br>| Chunk  1 | Chunk  2 | Chunk  3 | Chunk  4 |<br>|    h1    |    h2    |    h3    |    h4    |<br>+----------+----------+----------+----------+<br>                    ...<br>+----------+----------+----------+----------+<br>|Chunk 6397|Chunk 6398|Chunk 6399|Chunk 6400|<br>+----------+----------+----------+----------+</pre><p><strong>Step 2: Create an upload session</strong></p><p>The client sends the manifest to the File Service:</p><pre>POST /files/{fileId}/upload-sessions</pre><p>The backend creates an UploadSession in PostgreSQL and checks which chunks already exist in the owner’s storage scope.</p><p>The backend reuses those chunks and generates pre-signed PUT URLs for the remaining missing chunks.</p><p>For a large manifest, upload instructions and pre-signed URLs can be returned in batches rather than one enormous response.</p><p><strong>Step 3: Upload missing chunks</strong></p><p>The client uploads missing chunks directly to S3.</p><pre>Client                         S3<br>  |                             |<br>  |-- PUT chunk h1001 ---------&gt;|<br>  |                             |<br>  |-- PUT chunk h1002 ---------&gt;|<br>  |                             |<br>  |-- PUT chunk h1003 ---------&gt;|<br>  |                             |<br>  |          ...                |<br>  |                             |<br>  |-- PUT chunk h6400 ---------&gt;|<br>  |                             |<br>  |&lt;-- Upload responses --------|</pre><p>Each request uploads one independently stored content-addressed chunk.</p><p><strong>Step 4: Commit the upload session</strong></p><p>Once all required chunks are available, the client calls:</p><pre>POST /upload-sessions/{uploadSessionId}/commit</pre><p>The backend verifies that every chunk referenced by the manifest is available and passes the required integrity checks.</p><p>It then commits the new immutable file version.</p><h3>8.2 How Do We Resume an Interrupted Upload?</h3><p>Suppose the client uploads 60% of the chunks and then the laptop crashes.</p><p>We should not restart the entire 50 GB upload.</p><p>The desktop sync agent maintains upload progress in local SQLite.</p><p>For example, we can maintain the following tables:</p><p><strong>sync_jobs</strong></p><pre>--------------------------------<br>job_id<br>file_id<br>local_path<br>file_fingerprint<br>upload_session_id<br>manifest_hash<br>upload_status<br>retry_count</pre><p><strong>sync_job_chunks</strong></p><pre>--------------------------------<br>job_id<br>chunk_index<br>chunk_hash<br>chunk_size<br>upload_status</pre><p>Notice that we track chunk-level progress rather than S3 part numbers or ETags.</p><p>When the application restarts:</p><ol><li>It identifies unfinished upload jobs from SQLite.</li><li>It checks that the original file is still available.</li><li>It verifies that the file has not changed since the upload session was created.</li><li>It calls the upload-session status API.</li><li>The backend returns the status of the expected chunks.</li><li>The client uploads only the missing chunks.</li><li>Once all chunks are available, the client commits the session.</li></ol><p>The status API is:</p><pre>GET /upload-sessions/{uploadSessionId}</pre><p>The client should not blindly trust its local SQLite state.</p><p>The backend reconciles the session against the actual S3 objects and their expected checksums.</p><p>If the original file has changed, the agent must not continue uploading an old snapshot as though it represented the new file.</p><p>Instead, it should calculate a new manifest and initiate a new upload session.</p><p>Previously uploaded chunks can still be reused if they appear in the new manifest.</p><p>The file_fingerprint helps the agent identify whether the original local file has changed. This is different from the Rabin rolling fingerprint used by CDC to determine chunk boundaries.</p><p>The important point is that we resume at the chunk level, not from the beginning of the entire file.</p><h3>8.3 How Do We Track Upload Progress?</h3><p>Since the file is divided into chunks, tracking progress becomes easier.</p><p>The client can maintain:</p><ul><li>Total file size.</li><li>Total number of chunks.</li><li>Number of chunks already available in cloud storage.</li><li>Number of chunks successfully uploaded.</li><li>Number of chunks still pending.</li><li>Bytes uploaded and bytes remaining.</li></ul><p>For example:</p><pre>File: movie.mp4<br><br>Total Size:                50 GiB<br>Chunks Identified:          6,400<br>Chunks Already Available:   1,000<br>Chunks Uploaded:            3,000<br>Chunks Remaining:           2,400<br><br>Upload Progress:<br>[#################-------] 60%</pre><h3>8.4 How Do We Verify the Uploaded Chunks?</h3><p>We cannot blindly trust the client to tell us that a chunk was uploaded successfully.</p><p>Our design uses two important values:</p><p><strong>1. chunk_hash</strong></p><p>This is the SHA-256 hash of the original chunk content.</p><p>It identifies the content and enables deduplication.</p><p><strong>2. storage_checksum</strong></p><p>This verifies the actual stored payload.</p><p>If we store the chunk without transforming it, the storage checksum can correspond to the original chunk content.</p><p>If compression or encryption is applied before storage, the checksum of the stored payload is different from the original content hash.</p><p>In that case, we maintain both values separately.</p><p>S3 supports checksum validation during object uploads. The backend can use the expected object size and stored checksum information to verify the uploaded chunks.</p><p><strong>Why Can’t We Use ETag as Our Content Hash?</strong></p><p>An ETag is not universally equivalent to SHA-256.</p><p>Its meaning depends on how the object was uploaded and stored.</p><p>We should use our own SHA-256 chunk_hash as the content identity and explicit checksums for storage integrity.</p><p>During download, the client verifies the original chunk hash after any required decryption or decompression.</p><p>It can also verify the complete file hash after reconstructing the file.</p><p>The backend commits the new file version only after all required chunks have been verified.</p><h3>9. Upload Completion and Data Integrity</h3><p>Uploading all chunks does not mean a file is ready for users.</p><p>Before publishing a file version, the backend must verify that every required chunk exists in S3 and its content is intact.</p><p>Our completion flow has three stages:</p><pre>Client uploads chunks<br>        |<br>        v<br>  Verify integrity<br>        |<br>        v<br>  Commit file version<br>        |<br>        v<br>  File available</pre><h3>9.1 Verify Uploaded Chunks</h3><p>Once the client finishes uploading, it calls:</p><pre>POST /upload-sessions/{uploadSessionId}/commit</pre><p>The File Service retrieves the expected chunk manifest and verifies every unique chunk.</p><p>For each chunk, it checks:</p><ul><li>Does the S3 object exist?</li><li>Does its size match the expected size?</li><li>Does its checksum match the expected checksum?</li></ul><p>We can use S3 HeadObject with checksum retrieval enabled.</p><p>There are two types of checksums to consider:</p><ul><li>chunk_hash: SHA-256 hash of the original chunk content.</li><li>storage_checksum: Checksum of the actual stored payload.</li></ul><h3>9.2 Handle Integrity Failures</h3><p>Consider a file containing 6,400 chunks, where one chunk is missing or has an invalid checksum.</p><p>We must not commit the file version.</p><p>Instead, the backend returns the affected chunks and moves the upload session back to UPLOADING.</p><pre>       VERIFY CHUNKS<br>             |<br>       +-----+------+<br>       |            |<br>       v            v<br>   All valid    Missing/Invalid<br>       |            |<br>       v            v<br>    COMMIT      Return chunk IDs<br>                    |<br>                    v<br>               Re-upload<br>                    |<br>                    v<br>              Verify again</pre><p>The client only needs to upload the affected chunks. It does not need to restart the entire upload.</p><h3>9.3 Commit the File Version</h3><p>Once all chunks pass verification, the backend starts a PostgreSQL transaction.</p><p>It performs the following operations:</p><ol><li>Creates an immutable FileVersion.</li><li>Inserts the ordered FileVersionChunk mappings.</li><li>Updates files.current_version_id.</li><li>Creates change-feed and transactional outbox records.</li><li>Marks the upload session as COMMITTED.</li></ol><pre>                 Verified Chunks<br>                       |<br>                       v<br>             PostgreSQL Transaction<br>                       |<br>          +------------+--------------+<br>          |            |              |<br>          v            v              v<br>       Create     Insert Chunk   Update Current<br>       File       Mappings       Version Pointer<br>       Version        |               |<br>          |           |               |<br>          +-----------+---------------+<br>                       |<br>                 +-----+------+<br>                 |            |<br>                 v            v<br>             Change Feed   Transactional<br>                              Outbox<br>                 |            |<br>                 +-----+------+<br>                       |<br>                       v<br>              Transaction Commit<br>                       |<br>                       v<br>              New Version Available</pre><h3>9.4 Handle Retries and Failed Commits</h3><p>What happens if S3 uploads succeed but the PostgreSQL transaction fails?</p><p>The chunks remain in S3, but the file version is not published. The upload session remains retryable, so the backend can verify the existing chunks and try the commit again.</p><p>There is another important case: PostgreSQL commits successfully, but the client never receives the response.</p><p>To prevent duplicate versions, we store committed_version_id in the upload session.</p><pre>      Commit Request<br>             |<br>             v<br>     PostgreSQL Commit<br>             |<br>             v<br>      Response Lost<br>             |<br>             v<br>       Client Retries<br>             |<br>             v<br>   Session Already Committed?<br>             |<br>             v<br>      Return Existing Version</pre><p>The session status and committed_version_id are updated in the same transaction that creates the new version.</p><h3>9.5 Clean Up Orphaned Chunks</h3><p>An abandoned upload can leave chunks in S3 without a committed file version.</p><p>We cannot delete them immediately because another active upload session might still need them.</p><p>Before deleting a chunk, garbage collection checks whether it has:</p><ul><li>A reference from a committed FileVersionChunk.</li><li>An active UploadSessionChunk reference or lease.</li></ul><pre>                S3 Chunk<br>                    |<br>                    v<br>             Check References<br>                    |<br>             +------+------+<br>             |             |<br>             v             v<br>       Committed       Active Upload<br>       Version Ref?    Session Ref?<br>             |             |<br>            Yes           Yes<br>             |             |<br>             v             v<br>          Keep Chunk    Keep Chunk<br><br>--------------------------------------------------  <br>        No references from either<br>                    |<br>                    v<br>          Retention Period Passed?<br>                    |<br>                    v<br>             Mark DELETING<br>                    |<br>                    v<br>               Delete S3<br>                    |<br>                    v<br>             Remove DB Record</pre><p>Reference checks and deletion must be coordinated using PostgreSQL transactions and row-level locking. This prevents garbage collection from racing with an active upload or commit.</p><p><strong>The key idea:</strong> S3 stores the chunks, but PostgreSQL decides when a complete, verified and immutable file version becomes available to users.</p><h3>Deep Dives</h3><h3>1. Downloading Large Files</h3><h3>1.1 Manifest and Parallel Downloads</h3><p>For a large file, the client fetches a paginated manifest using the same immutable versionId throughout the download:</p><pre>GET /files/{fileId}/versions/{versionId}/manifest?pageToken=...</pre><p>The manifest contains ordered chunk references, hashes, sizes and the complete-file checksum. The client reuses verified cached chunks and downloads missing chunks through a bounded worker pool.</p><pre>File Manifest<br>                 |<br>        Identify Missing Chunks<br>                 |<br>       +---------+---------+<br>       |         |         |<br>    Worker 1  Worker 2  Worker 3<br>       |         |         |<br>    Chunk 1   Chunk 2   Chunk 3<br>       |         |         |<br>       +---------+---------+<br>                 |<br>         Local Chunk Cache</pre><p>This avoids sequential downloads and excessive concurrent requests.</p><h3>1.2 Resuming Interrupted Downloads</h3><p>Track progress in local SQLite:</p><ul><li>download_jobs: File, version, manifest hash, destination and overall status.</li><li>download_job_chunks: Chunk index, hash and download status.</li></ul><p>After a restart, retrieve the same version’s manifest, reuse verified chunks, download missing chunks and reconstruct the file. Incomplete chunks can be downloaded again as CDC bounds their size.</p><h3>1.3 Reconstruction and Verification</h3><pre>      Local Chunk Cache<br>               |<br>     Read in Manifest Order<br>               |<br>    Verify storage_checksum<br>               |<br>      Decrypt / Decompress<br>               |<br>       Verify chunk_hash<br>               |<br>    Stream into Temporary File<br>               |<br>     Verify content_sha256<br>               |<br>         Atomic Rename</pre><p>Verify the stored representation, original chunks and complete file. Re-download invalid chunks. Stream into a temporary file to limit memory usage, and move it to the destination only after complete verification.</p><h3>1.4 Logical Byte Ranges</h3><p>An S3 Range request applies to one chunk object, not the complete logical file. Use the manifest and cumulative chunk sizes to identify overlapping chunks, decode them and extract the requested bytes.</p><pre>Chunk 1     Chunk 2     Chunk 3     Chunk 4<br>0–8 MB      8–16 MB     16–24 MB    24–32 MB<br>               |           |<br>               +-----+-----+<br>                     |<br>           Requested: 10–20 MB<br>                     |<br>             Fetch Chunks 2, 3<br>                     |<br>               Extract Range</pre><p>Download complete chunks initially. Add logical-range and partial-chunk retrieval later if streaming or random access requires them.</p><h3>1.5 URL Expiry and Progress</h3><p>CloudFront checks signed URL expiry when each request starts. An ongoing request can finish after expiry, but subsequent requests and retries fail. Generate URLs in batches and refresh them for pending chunks.</p><p>Calculate progress using verified bytes, including cached chunks, rather than chunk counts because CDC produces variable-sized chunks.</p><h3>2. Reliable Synchronisation</h3><p>The protocol must handle new devices, long offline periods, crashes and duplicate events without losing changes.</p><h3>2.1 Initial Sync: New Device</h3><p>Return metadata in pages:</p><pre>GET /sync/initial?limit=1000<br>{<br>  &quot;snapshotId&quot;: &quot;snapshot-101&quot;,<br>  &quot;files&quot;: [<br>    {<br>      &quot;fileId&quot;: &quot;file-101&quot;,<br>      &quot;name&quot;: &quot;document.pdf&quot;,<br>      &quot;versionId&quot;: &quot;version-105&quot;,<br>      &quot;parentFolderId&quot;: &quot;folder-10&quot;<br>    }<br>  ],<br>  &quot;nextPageToken&quot;: &quot;token-2&quot;,<br>  &quot;snapshotCursor&quot;: 50000<br>}</pre><p>Fetch subsequent pages from the same snapshot:</p><pre>GET /sync/initial?snapshotId=snapshot-101&amp;pageToken=token-2</pre><p>All pages must represent one point in time. Paginating live records can miss or duplicate files changed between requests.</p><pre>          Consistent PostgreSQL Snapshot<br>                       |<br>             +---------+---------+<br>             |                   |<br>       Capture Feed       Capture Visible<br>          Cursor          Metadata + Permissions<br>             |                   |<br>             +---------+---------+<br>                       |<br>          Materialise Short-lived Snapshot<br>                       |<br>               Paginated Results<br>                       |<br>          Fetch Changes after Snapshot Cursor</pre><p>Capture visible metadata, permissions and the feed cursor within one consistent database snapshot. Materialise the result so pagination does not keep a transaction open.</p><p>The snapshot and cursor must form a gap-free boundary: earlier changes appear in the snapshot; later changes are available after its cursor. Write metadata changes and corresponding feed records in the same PostgreSQL transaction.</p><p>After loading the snapshot, fetch changes made during pagination:</p><pre>GET /sync/changes?cursor=50000</pre><p>Retain the required feed history while the snapshot is active. Restart initial sync if the snapshot expires or its required history is unavailable.</p><p>Initial sync downloads metadata, not all content. Show files as Online Only and fetch chunks when opened, marked for offline access or prefetched.</p><h3>2.2 Incremental Sync: Existing Device</h3><p>The client stores its last applied cursor in SQLite and requests subsequent changes:</p><pre>GET /sync/changes?cursor=59828<br>{<br>  &quot;changes&quot;: [<br>    { &quot;sequence&quot;: 59829, &quot;eventType&quot;: &quot;FILE_UPDATED&quot;, &quot;fileId&quot;: &quot;file-101&quot;, &quot;versionId&quot;: &quot;version-106&quot; },<br>    { &quot;sequence&quot;: 59830, &quot;eventType&quot;: &quot;FILE_CREATED&quot;, &quot;fileId&quot;: &quot;file-102&quot; },<br>    { &quot;sequence&quot;: 59831, &quot;eventType&quot;: &quot;FILE_DELETED&quot;, &quot;fileId&quot;: &quot;file-103&quot; }<br>  ],<br>  &quot;nextCursor&quot;: 59831,<br>  &quot;hasMore&quot;: false<br>}<br><br>Client                                  Backend<br>  |                                        |<br>  |--- GET changes?cursor=59828 ----------&gt;|<br>  |&lt;-- Changes 59829–59831 ----------------|<br>  |                                        |<br>  | Apply changes and save cursor          |<br>  | atomically in SQLite                   |<br>  |                                        |<br>  |--- ACK cursor=59831 ------------------&gt;|<br>  |&lt;-- ACK successful ---------------------|</pre><p>Advance the cursor only after applying the corresponding changes. Commit metadata updates and cursor advancement in one SQLite transaction. Make processing idempotent, and use repeatable filesystem operations, temporary files and atomic renames.</p><p>Each device progresses independently. Update devices.last_ack_cursor only after acknowledgement. Acknowledge only the highest contiguous applied position; parallel processing must not skip unfinished events. If processing stops after 59830, the saved cursor must not advance to 59831.</p><h3>2.3 Expired Cursors and Full Re-sync</h3><p>Reject cursors older than the retained history rather than silently skipping missing events:</p><pre>HTTP/1.1 410 Gone<br>{<br>  &quot;error&quot;: &quot;CURSOR_EXPIRED&quot;,<br>  &quot;message&quot;: &quot;Change history is no longer available.&quot;,<br>  &quot;action&quot;: &quot;FULL_SYNC_REQUIRED&quot;<br>}</pre><pre>        Cursor within Retention?<br>                  |<br>            +-----+-----+<br>            |           |<br>           Yes          No<br>            |           |<br>      Return Deltas   410 CURSOR_EXPIRED<br>                        |<br>                   Initial Sync<br>                        |<br>              Reconcile Local State</pre><p>Reconcile the new snapshot with local state: add missing files, update changed versions and remove deleted or inaccessible files. Preserve unsynchronised edits as recovery copies for an explicit recovery or conflict workflow. Never silently overwrite them or automatically recreate cloud-deleted files.</p><p>Track the earliest supported cursor per user feed. Retention cleanup must preserve events required by unexpired snapshots.</p><h3>2.4 Deletions and Tombstones</h3><p>Record logical deletion in files.deleted_at and write a FILE_DELETED event to affected users’ feeds in the same transaction.</p><pre>          Delete File<br>                |<br>       +--------+---------+<br>       |                  |<br>Set files.deleted_at   Write FILE_DELETED<br>       |                  |<br>       +--------+---------+<br>          Same Transaction<br>                |<br>         Durable Change Feed<br>                |<br>        Other Devices Remove File</pre><p>The tombstone records deletion; the event informs devices. Together, they prevent stale offline copies from being uploaded as new files. For shared files, notify users who had access. Use a separate event, such as FILE_ACCESS_REVOKED, when only access is removed.</p><p>Coordinate tombstone cleanup with feed retention and device acknowledgements. After history is purged, older cursors require full reconciliation. Logical deletion does not immediately remove S3 chunks; reference-based garbage collection handles them separately (Section 4.5).</p><h3>3. Concurrent File Edits</h3><h3>3.1 Last Write Wins</h3><p>Use Last Write Wins (LWW): the latest successful server-side commit becomes current. Do not rely on client timestamps.</p><p>Clients upload chunks independently without locking the file. Serialise only the final commit using a PostgreSQL row-level lock:</p><pre>SELECT *<br>FROM files<br>WHERE file_id = :file_id<br>FOR UPDATE;</pre><p>Within the transaction:</p><ol><li>Lock the file and assign the next version number.</li><li>Create an immutable FileVersion and ordered FileVersionChunk mappings.</li><li>Update files.current_version_id.</li><li>Write change-feed records and the transactional outbox event.</li><li>Mark the upload session as committed.</li></ol><p>Other commits for the same file wait until the transaction finishes.</p><h3>3.2 Concurrent and Stale Updates</h3><p>Suppose Alice and Bob both edit Version 10:</p><pre>                Version 10<br>                /        \<br>          Alice edits   Bob edits<br>               |            |<br>        Upload Session A  Upload Session B<br>               |            |<br>               +------+-----+<br>                      |<br>              Serialised Commits<br>                      |<br>            Version 11: Alice<br>                      |<br>            Version 12: Bob (Current)</pre><p>If Alice commits first, she creates Version 11. Bob’s later commit creates Version 12 containing his snapshot, not a merge of both edits. Alice’s version remains recoverable under the version-retention policy.</p><p>Do not reject stale updates through row_version, expectedVersion or HTTP 409 checks. Both uploads can succeed if validation passes. Version numbers reflect commit order, not when editing started.</p><h3>3.3 Restoring Earlier Versions</h3><p>Restore by creating a new immutable version that reuses historical chunks. Do not modify history or move the current pointer backwards.</p><pre>Version 10<br>    |<br>Version 11: Alice’s changes<br>    |<br>Version 12: Bob’s changes<br>    |<br>Version 13: Restore Version 11 (Current)</pre><h3>3.4 Unsynchronised Local Changes</h3><p>Preserve pending local content before applying an incoming remote version. If the client later commits its local snapshot, LWW applies again. This preserves unsynchronised work but does not merge concurrent edits.</p><h3>4. Content-Defined Chunking</h3><h3>4.1 Why CDC?</h3><p>Fixed-size boundaries shift after an insertion, changing hashes for otherwise unchanged content. Content-Defined Chunking (CDC) uses content-based boundaries that can resynchronise after an edit, reducing re-uploads.</p><pre>Original:                 [ A  ][ B  ][ C  ][ D  ]<br>Fixed-size after insert:  [ A&#39; ][ B&#39; ][ C&#39; ][ D&#39; ]<br>CDC after local edit:     [ A  ][ B&#39; ][ C  ][ D  ]</pre><p>The CDC example is illustrative; boundary preservation is not guaranteed for every edit.</p><h3>4.2 Boundaries and Chunk Identity</h3><p>Use a rolling hash, such as a Rabin fingerprint, to find boundaries. Configure minimum, target and maximum sizes to avoid very small or large chunks. An illustrative target is 8 MiB; tune the limits to the workload.</p><p>After chunking, calculate SHA-256 for each chunk. The rolling hash finds boundaries; SHA-256 identifies content.</p><pre>          Original File<br>                 |<br>      CDC: Rabin Fingerprint<br>                 |<br>       +---------+---------+<br>       |         |         |<br>    Chunk A   Chunk B   Chunk C<br>       |         |         |<br>    SHA-256   SHA-256   SHA-256<br>       |         |         |<br>       h1        h2        h3<br>       |         |         |<br>       +---------+---------+<br>                 |<br>          Ordered Manifest</pre><p>The manifest records chunk index, hash, size and chunker version identifying the algorithm and parameters. At upload-session creation, the File Service checks existing chunks and requests only missing ones.</p><p>Scope deduplication to the owner or tenant, including reuse across files and versions. Do not expose whether another user stores matching content.</p><h3>4.3 Independent Chunk Storage</h3><p>Store each chunk as an S3 object, for example {owner_id}/{chunk_hash}. PostgreSQL maintains version metadata and ordered FileVersionChunk mappings.</p><pre>            File Version<br>                  |<br>          Ordered Manifest<br>                  |<br>        +---------+---------+<br>        |         |         |<br>     Chunk 1   Chunk 2   Chunk 3<br>        |         |         |<br>     S3 Object S3 Object S3 Object<br>        |         |         |<br>        +---------+---------+<br>                  |<br>         Client Reconstruction</pre><p>Versions reuse unchanged chunks:</p><pre>Version 1: [h1, h2, h3, h4]<br>Version 2: [h1, h2, h5, h4]<br>Version 3: [h1, h2, h5, h6]</pre><h3>4.4 CDC vs S3 Multipart Upload</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/755/1*FGNTJEQ46bUXCjSKNiSj7g.png" /></figure><p>Use regular pre-signed S3 PUT requests for missing chunks, not CreateMultipartUpload, UploadPart and CompleteMultipartUpload as the primary mechanism.</p><h3>4.5 Reference-Based Garbage Collection</h3><p>Delete a chunk only when no retained file version or active upload session references it. Deleting one version must not remove chunks still required elsewhere.</p><h3>5. Merkle Trees: Future Optimisation</h3><p>A Merkle tree allows chunk-membership verification against a trusted root without downloading all other chunks.</p><pre>              Merkle Root<br>              /         \<br>          Hash AB      Hash CD<br>           /   \        /   \<br>         H(A) H(B)    H(C) H(D)<br>          |    |       |    |<br>        CH-A CH-B     CH-C CH-D</pre><p>Each parent hashes its children. To verify Chunk B, combine its hash with H(A) and Hash CD in the correct order, then compare the calculated root with the file version’s authenticated root.</p><p>The root must come from authenticated metadata. Retain tree nodes or sufficient hash information to generate proofs; the root alone is insufficient.</p><p>Merkle trees are not required initially. Chunk SHA-256 hashes, the complete-file checksum and manifest hash already meet current integrity needs. Add Merkle trees only for independent membership proofs, partial-manifest verification or untrusted-storage proofs that justify their computation, storage and proof-generation cost.</p><h3>6. Performance Optimisations</h3><h3>6.1 CDN Caching</h3><p>Cache immutable, content-addressed chunks for long periods. New versions reuse unchanged cache entries without invalidation. Use shorter caching for mutable metadata and current-version information.</p><h3>6.2 Selective Compression</h3><p>Compress chunks independently so downloads, decoding and retries remain chunk-local. Text, JSON and CSV usually benefit; already-compressed images, audio, video and archives often do not.</p><p>Retain encoding metadata. chunk_hash identifies original content; storage_checksum validates the stored representation. If application-level encryption is used, compress before encrypting. Skip compression when savings are negligible.</p><h3>6.3 Adaptive Parallelism</h3><p>Adjust bounded upload/download concurrency using throughput, latency, failures, retries and device resources. Excessive concurrency can overwhelm the connection or client without improving throughput.</p><p>Keep CDC parameters and chunker version consistent within an upload. Adapt worker count, not chunk boundaries.</p><h3>6.4 LAN and Peer-to-Peer Sync</h3><p>Nearby devices could exchange verified chunks directly, with cloud fallback:</p><pre>Laptop A ── Direct LAN Transfer ──&gt; Laptop B<br>                                      |<br>                                Cloud Fallback<br>                                      |<br>                                     CDN<br>                                      |<br>                                      S3</pre><p>This can reduce egress and improve speed but requires peer discovery, authentication, encryption, NAT/firewall handling and secure fallback. Verify received chunks against the authenticated manifest. Keep peer-to-peer sync out of the initial scope.</p><h3>7. Keeping Files Secure</h3><h3>7.1 Encryption in Transit</h3><p>Use HTTPS/TLS for client-to-API, direct S3 uploads, CDN downloads and internal service communication. Never transfer file content or credentials over unencrypted connections.</p><h3>7.2 Encryption at Rest</h3><ul><li>S3 encrypts new objects by default using SSE-S3. Use SSE-KMS for additional key control, permissions and auditing; KMS manages keys while S3 encrypts and decrypts objects.</li><li>Encrypt PostgreSQL storage, backups and snapshots containing metadata, permissions, versions, manifests and object references.</li></ul><p>Use server-side encryption initially.</p><p>References: <a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/serv-side-encryption.html">S3 server-side encryption</a>, <a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/UsingKMSEncryption.html">SSE-KMS</a>.</p><h3>7.3 Access Control and Signed URLs</h3><p>Keep S3 private. Never provide clients with AWS credentials or unrestricted bucket access. Authorise each operation before issuing temporary object-specific URLs:</p><ul><li>Upload: Issue a pre-signed S3 PUT URL restricted to the object and method. Bind the expected checksum where supported.</li><li>Download: Check ownership or active sharing permissions, then issue a signed CloudFront URL or appropriately scoped cookie. Validate authorisation before cache hits and origin fetches. Restrict S3 origin access using CloudFront OAC.</li></ul><p>Signed URLs are bearer credentials. Use HTTPS, short expiry and narrow scope. Do not expose full URLs in logs, analytics or errors.</p><p>Revoking database permissions does not invalidate issued URLs; they can remain usable until expiry or signing credentials/policies are invalidated. Near-immediate revocation requires current-permission checks at the edge or on each request, adding complexity and potential latency. Handle expiry during large downloads as described in Section 1.5.</p><p>References: <a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/ShareObjectPreSignedURL.html">S3 pre-signed URLs</a>, <a href="https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/private-content-signed-urls.html">CloudFront signed URLs</a>.</p><h3>7.4 Malware Scanning</h3><p>Malware scanning is outside the initial scope. If introduced, reconstruct the original content as a stream in manifest order, after required decryption or decompression. Scanning chunks separately can miss malicious patterns spanning boundaries.</p><pre>        Chunks Uploaded<br>                |<br>      Upload Manifest Verified<br>                |<br>        Malware Scanner Worker<br>                |<br>       Read Chunks in Order<br>                |<br>    Decode and Reconstruct Stream<br>                |<br>           Scan Content<br>                |<br>          +-----+-----+<br>          |           |<br>        Clean      Malicious<br>          |           |<br>    Allow Version  Quarantine<br>       Commit        Upload</pre><p>Large-file scanning can run asynchronously, but downloads and sharing must remain blocked until it passes. Add a scan-status gate to the upload lifecycle when this feature is introduced.</p><h3>Production Considerations</h3><p>A design that works on paper can face different challenges at production scale. Here are some areas to consider:</p><ul><li><strong>Storage Costs:</strong> S3 storage and egress can become expensive. Consider Cloudflare R2, which offers zero egress fees, along with CDN caching, deduplication and lifecycle policies.</li><li><strong>Database Scaling:</strong> Start with PostgreSQL, proper indexing and read replicas. Introduce partitioning or owner-based sharding when actual load requires it.</li><li><strong>Rate Limiting:</strong> Enforce per-account quotas, limit active upload sessions and use bounded transfer concurrency. Control direct S3 access through scoped, short-lived pre-signed URLs.</li><li><strong>High Availability:</strong> Use automated backups, database replication and a defined disaster-recovery plan with clear RPO and RTO targets.</li><li><strong>Observability:</strong> Monitor upload failures, download retries, synchronization lag, queue health and garbage-collection backlog. Use distributed tracing to investigate failures across services.</li></ul><p>The goal is to keep the initial architecture simple while having a clear path to address these challenges as the system grows.</p><h3>Final Architecture</h3><p>Let’s put everything together.</p><p>Actual file contents are transferred directly between clients and S3/CDN.</p><h3>Complete Architecture</h3><pre>                         +-------------------------+<br>                         |         Clients         |<br>                         |   Web / Desktop / App   |<br>                         +------------+------------+<br>                                      |<br>                                      v<br>                         +-------------------------+<br>                         |       DNS / WAF         |<br>                         +------------+------------+<br>                                      |<br>                                      v<br>                         +-------------------------+<br>                         |       External LB       |<br>                         +------------+------------+<br>                                      |<br>                    +-----------------+-----------------+<br>                    |                                   |<br>                    v                                   v<br>          +---------------------+           +---------------------+<br>          |     API Gateway     |           |   Realtime Gateway  |<br>          |  Auth / Rate Limit  |           |    WebSocket / SSE  |<br>          +----------+----------+           +----------^----------+<br>                     |                                 |<br>          +----------+----------+                      |<br>          |          |          |                      |<br>          v          v          v                      |<br>      +--------+ +--------+ +--------+                 |<br>      |  File  | |  Sync  | | Share  |                 |<br>      | Service| | Service| | Service|                 |<br>      +---+----+ +---+----+ +---+----+                 |<br>          |          |          |                      |<br>          +----------+----------+                      |<br>                     |                                 |<br>                     v                                 |<br>          +---------------------------+                |<br>          |        PostgreSQL         |                |<br>          |                           |                |<br>          | File / Folder Metadata    |                |<br>          | File Versions             |                |<br>          | Ordered Chunk Manifests   |                |<br>          | Chunk Metadata            |                |<br>          | Upload Sessions           |                |<br>          | Shares / Permissions      |                |<br>          | Durable Change Feed       |                |<br>          | Device Sync Cursors       |                |<br>          | Transactional Outbox      |                |<br>          +-------------+-------------+                |<br>                        |                              |<br>                        | Outbox Publisher polls       |<br>                        v                              |<br>          +---------------------------+                |<br>          |     Outbox Publisher      |                |<br>          +-------------+-------------+                |<br>                        |                              |<br>                        v                              |<br>          +---------------------------+                |<br>          |       Message Queue       |                |<br>          |        SQS / Kafka        |                |<br>          +-------------+-------------+                |<br>                        |                              |<br>                        v                              |<br>          +---------------------------+                |<br>          |   Notification Service    |----------------+<br>          +---------------------------+<br><br>---------------------------------------------------------------------<br>                    DIRECT FILE DATA TRANSFER<br>                    -------------------------<br>    UPLOAD<br>    Client -------------------------------------&gt; S3<br>           Pre-signed PUT URLs<br>           Individual content-addressed chunks<br>    DOWNLOAD<br>    Client &lt;---------------- CDN &lt;--------------&gt; S3<br>                   Immutable chunk delivery<br>                    CLIENT-SIDE COMPONENTS<br>                    ----------------------<br>                Local File System<br>                       |<br>                       v<br>                File System Watcher<br>                       |<br>                       v<br>                Debounce / Coalesce<br>                       |<br>                       v<br>                 Stable File Check<br>                       |<br>                       v<br>                  SQLite Database<br>              (Persistent Jobs / State)<br>                       |<br>                       v<br>                  Sync Agent<br>                       |<br>             +---------+---------+<br>             |                   |<br>             v                   v<br>        Upload Workers       Sync Workers<br>             |                   |<br>       +-----+-----+       +-----+------+<br>       |           |       |            |<br>       v           v       v            v<br>   File Service   S3   Sync Service    CDN<br>   Sessions /    Direct  Cursors /   Chunk<br>   Commit        PUT     Changes     Downloads<br>       Periodic Local Filesystem Reconciliation</pre><p>The architecture has three important paths:</p><ol><li><strong>Control plane:</strong> The backend handles authentication, authorization, metadata, upload coordination, version commits and synchronization.</li><li><strong>Data plane:</strong> Clients upload chunks directly to S3 and download them through the CDN.</li><li><strong>Synchronization path:</strong> PostgreSQL maintains durable changes, while the transactional outbox, message queue and notification service deliver real-time notifications.</li></ol><h3>19. Conclusion</h3><blockquote>Building a Dropbox-like system is more than integrating S3 with a file-upload API. The real challenge is maintaining data integrity, handling concurrent changes and keeping multiple devices synchronized despite failures.</blockquote><blockquote>The architecture starts with simple, reliable building blocks: PostgreSQL, S3, Content-Defined Chunking and a durable change feed. More advanced optimizations can be introduced as the system grows.</blockquote><blockquote><strong>The priority is to get correctness and reliability right before optimizing for scale.</strong></blockquote><p>If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, AI Agents, LLM, RAG, System Desing and backend engineering!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cbd3542a7f4b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Designing a URL Shortener Like Bitly]]></title>
            <link>https://medium.com/@vjgupta78/designing-a-url-shortener-like-bitly-c110e1bc929b?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/c110e1bc929b</guid>
            <category><![CDATA[design-systems]]></category>
            <category><![CDATA[microservices]]></category>
            <category><![CDATA[system-design-interview]]></category>
            <category><![CDATA[system-design-concepts]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Sat, 14 Nov 2026 20:57:42 GMT</pubDate>
            <atom:updated>2026-11-15T08:45:53.163Z</atom:updated>
            <content:encoded><![CDATA[<p><em>A system design breakdown covering ID generation, caching, database replication, failure handling and the trade-offs behind the architecture.</em></p><p>A URL shortener is usually one of the first system design problems people practise. The basic implementation is straightforward: take a long URL, generate a short code, store the mapping and redirect users when they open the shortened link.</p><p>But the interesting engineering problems start when we think about scale.</p><p>How do we generate unique short codes across multiple application instances? How do we handle millions of redirects without overwhelming the database? What happens when a popular link suddenly receives millions of requests? And what happens to our ID counter when the primary database fails?</p><p>In this article, I’ll walk through how I would design a URL shortener like Bitly, starting with the basic requirements and gradually introducing the components needed to make the system scalable and highly available.</p><h3>1. Understanding the Requirements</h3><p>Before drawing boxes, let’s establish what we are actually building.</p><p>A user provides a long URL and receives a shorter URL. Anyone opening that shortened URL should be redirected to the original destination.</p><h3>Functional Requirements</h3><p>We’ll focus on these features:</p><ol><li>A user can submit a long URL and receive a shortened URL.</li><li>A user can optionally specify a custom alias, such as short.ly/my-launch.</li><li>A user can optionally specify an expiration time.</li><li>Anyone accessing a valid short URL should be redirected to the original URL.</li><li>The system should support disabling or taking down malicious URLs.</li></ol><p>We’ll keep user account management and detailed click analytics outside the core requirements. However, we’ll discuss analytics and takedowns later because they affect the architecture.</p><h3>Non-Functional Requirements</h3><ol><li><strong>Uniqueness:</strong> Every short code must map to exactly one long URL.</li><li><strong>Low latency:</strong> Target a redirect latency below 100 ms for most users, measured end-to-end where possible.</li><li><strong>High availability:</strong> Target 99.99% availability for redirects.</li><li><strong>Scalability:</strong> Support 1 billion stored URLs and 100 million daily active users.</li><li><strong>Durability:</strong> Successfully created URLs should not disappear after a database failure.</li><li><strong>Read-heavy optimisation:</strong> Most traffic will come from redirects rather than URL creation.</li></ol><p>One important observation is the read-to-write ratio.</p><p>People click links far more often than they create them. Almost every architectural decision in this design follows from this observation.</p><h3>2. Back-of-the-Envelope Estimation</h3><p>Let’s put some numbers down before deciding on the architecture.</p><p>We’ll make the following assumptions:</p><ul><li>Daily active users (DAU): 100 million.</li><li>Redirects per user per day: 5 request.</li><li>New URLs created per day: 1 million.</li><li>Total URLs stored: 1 billion.</li><li>Average raw record size: 500 bytes.</li></ul><h3>Read Throughput</h3><p>100 million users, with five redirects per day:</p><pre>100M × 5 = 500M redirects/day<br>500M / 86,400 = 5,787 redirects/second</pre><p>This is average traffic. Real traffic is never flat.</p><p>Assuming a 10x peak multiplier:</p><pre>Peak reads ≈ 58,000 requests/second</pre><h3>Write Throughput</h3><p>Assuming 1 million new URLs per day:</p><pre>1M / 86,400 = 11.6 writes/second</pre><p>Even with a 100x spike:</p><pre>Peak writes ≈ 1,160 writes/second</pre><p>Our estimated read-to-write ratio is approximately 500:1.</p><h3>Storage Estimation</h3><p>Assuming an average record size of 500 bytes:</p><pre>1 billion × 500 bytes = 500 GB</pre><p>This is only the raw data estimate. Actual storage requirements will be higher after accounting for indexes, WAL, database overhead, backups and operational headroom.</p><p>Still, this is a reasonable starting point for a well-provisioned PostgreSQL deployment.</p><h3>Short Code Capacity</h3><p>We’ll use Base62 encoding.</p><p>Base62 consists of:</p><ul><li>Lowercase letters: a-z (26)</li><li>Uppercase letters: A-Z (26)</li><li>Numbers: 0–9 (10)</li></ul><p>Total: 62 characters.</p><p>The number of possible combinations depends on the length of the short code.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/832/1*yHxeFg8kW9e1CmLTBna2IA.png" /></figure><p>With seven characters:</p><pre>62^7 = 3,521,614,606,208</pre><p>For 1 billion URLs, we use approximately 0.028% of this namespace.</p><p>Seven characters provide sufficient room for our initial requirements.</p><h3>What Do These Numbers Tell Us?</h3><p>Write throughput is relatively low, and the dataset is manageable.</p><p>A single PostgreSQL primary should be a reasonable starting point. We can use caching and read replicas to handle the read-heavy workload.</p><p>There is no immediate justification for database sharding.</p><p>If someone proposes sharding on day one, ask them to explain which actual bottleneck requires it.</p><h3>3. Data Model and API Design</h3><h3>Core Entity</h3><p>We can represent the entire mapping in a single table.</p><p>PostgreSQL schema:</p><pre>CREATE TABLE short_urls (<br>    short_code       TEXT PRIMARY KEY,<br>    long_url         TEXT NOT NULL,<br>    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),<br>    expiration_time  TIMESTAMPTZ,<br>    is_custom        BOOLEAN NOT NULL DEFAULT FALSE,<br>    owner_id         BIGINT,<br>    status           VARCHAR(20) NOT NULL DEFAULT &#39;ACTIVE&#39;,<br>    CHECK (status IN (&#39;ACTIVE&#39;, &#39;DISABLED&#39;, &#39;TAKEN_DOWN&#39;))<br>);<br><br>CREATE INDEX idx_short_urls_expiry<br>    ON short_urls (expiration_time)<br>    WHERE expiration_time IS NOT NULL;</pre><p>The primary key gives us both uniqueness and an efficient lookup index.</p><p>The partial index on expiration_time will help our background cleanup job.</p><h3>API Design</h3><p>We need two primary APIs.</p><p><strong>1. Create a Short URL</strong></p><pre>POST /api/v1/urls</pre><p>Request:</p><pre>{<br>  &quot;long_url&quot;: &quot;https://www.example.com/products/some-very-long-path&quot;,<br>  &quot;custom_alias&quot;: &quot;my-launch&quot;,<br>  &quot;expiration_time&quot;: &quot;2027-03-31T23:59:59Z&quot;<br>}</pre><p>Both custom_alias and expiration_time are optional.</p><p>Response:</p><pre>{<br>  &quot;short_url&quot;: &quot;https://short.ly/aZ3kP9x&quot;,<br>  &quot;short_code&quot;: &quot;aZ3kP9x&quot;<br>}</pre><p><strong>2. Redirect to Original URL</strong></p><pre>GET /aZ3kP9x</pre><p>Response:</p><pre>HTTP/1.1 302 Found<br>Location: https://www.example.com/products/some-very-long-path</pre><h3>HTTP Status Codes</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/701/1*IvoX244_uYLjnTUBd2V-jw.png" /></figure><p>The 409 case is worth highlighting.</p><p>Suppose two users simultaneously try to create:</p><pre>short.ly/my-launch</pre><p>Checking whether the alias exists before inserting is not enough. Both requests could pass the check at the same time.</p><p>The database primary key must enforce uniqueness.</p><p>When the insert encounters a unique constraint violation, the Write Service translates it into HTTP 409 Conflict.</p><p>For generated codes, we retry with a fresh ID.</p><h3>Why 302 Instead of 301?</h3><p>A 301 represents a permanent redirect. Browsers can cache it, and subsequent clicks may never reach our service.</p><p>That creates problems if we want to expire, disable or change a URL.</p><p>A 302 represents a temporary redirect. It is not heuristically cacheable by default, but clients and CDNs can still cache it when explicit freshness headers are provided.</p><p>We control that behaviour through Cache-Control policies.</p><p>For our use case, 302 is a sensible default because we want to retain control over redirects.</p><p>See <a href="https://www.rfc-editor.org/rfc/rfc9110.html">RFC 9110, HTTP Semantics</a>.</p><h3>4. High-Level Architecture</h3><p>We have two very different workloads:</p><ul><li>URL creation: relatively low volume.</li><li>URL redirection: significantly higher volume.</li></ul><p>I would separate them into two services so that we can scale and operate them independently.</p><p>The main components are:</p><ol><li>API Gateway</li><li>Write Service</li><li>Read Service</li><li>PostgreSQL Primary</li><li>PostgreSQL Synchronous Standbys</li><li>Redis Cluster</li><li>CDN</li><li>In-process LRU Cache (Caffeine/Ehcache)</li></ol><p>One important distinction: public redirects should not require authentication. Someone clicking a link in a WhatsApp group should not need to log in.</p><p>Authentication belongs on the URL creation and management APIs. Public redirects need rate limiting and abuse protection, not user authentication.</p><h3>Overall Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pcekiaklkhELnL7bfXj_EQ.png" /></figure><p>Let’s go deeper into each path.</p><h3>5. Deep Dive: How Do We Generate Unique Short Codes?</h3><p>This is one of the most interesting parts of the design.</p><p>We need short codes that are:</p><ul><li>Unique.</li><li>Short enough to fit in a URL.</li><li>Cheap to generate.</li><li>Safe to generate across multiple application instances.</li></ul><p>Let’s look at some possible approaches.</p><h3>Approach 1: Using a Prefix of the Long URL</h3><p>This is obviously not going to work.</p><p>Consider:</p><pre>https://example.com/products/iphone<br>https://example.com/products/macbook</pre><p>Taking the first few characters would generate the same code.</p><p>We cannot guarantee uniqueness.</p><h3>Approach 2: Hashing the Long URL</h3><p>We could calculate SHA-256 of the URL, encode it using Base62 and take the first seven characters.</p><pre>Long URL<br>https://www.example.com/blog/system-design/url-shortener?ref=newsletter<br>    |<br>    v<br>SHA-256 (256-bit digest, shown as hex)<br>26475c6e9119c2e960ce019d42a69b63567beb6d5ae0aecf61333caab455d7b5<br>    |<br>    v<br>Base62 Encoding (treat the digest as one big integer, then convert to base 62)<br>94mGJY3P9vPsEcCrQcWvhlLBvEGcyOM9D5qMWiqWdxF   (43 characters)<br>    |<br>    v<br>First 7 characters<br>94mGJY3<br>    |<br>    v<br>Short Code<br>https://short.ly/94mGJY3</pre><p>This looks attractive because hashing is deterministic and stateless.</p><p>However, there are two problems.</p><p><strong>First, truncating the hash introduces collisions.</strong></p><p>With a large number of URLs and a finite seven-character namespace, collisions become inevitable over time.</p><p>We need a unique constraint and a retry mechanism, possibly with a nonce or salt.</p><p><strong>Second, the same long URL always produces the same hash.</strong></p><p>Imagine two users shortening the same URL but wanting different expiration times or independent analytics.</p><p>A deterministic hash would give them the same short code.</p><p>We could introduce a user ID or random nonce into the hash, but that makes the design more complicated.</p><h3>Approach 3: Using a Counter</h3><p>A counter is simpler.</p><p>Every time we create a URL, we allocate a unique numeric ID and convert it into Base62.</p><pre>1      -&gt; 0000001<br>100    -&gt; 000001c<br>1000   -&gt; 00000G8</pre><p>The exact characters depend on the Base62 implementation.</p><p>The advantage is that uniqueness is guaranteed by the ID allocator rather than by the probability of a hash not colliding.</p><p>The question now becomes: where should this counter live?</p><h3>Why Not Redis INCR?</h3><p>A common solution is to use Redis INCR.</p><p>Redis provides atomic increment operations, so multiple Write Service instances can safely request IDs.</p><p>It is a perfectly valid approach.</p><p>However, it introduces an additional stateful dependency whose primary responsibility is ID allocation.</p><p>We also need to think about counter durability, replication and failover. If the latest counter state is lost during failover, previously allocated IDs could potentially be reused.</p><p>Since PostgreSQL is already our durable source of truth, I’d prefer to use it.</p><h3>PostgreSQL Sequence with Range Allocation</h3><p>Instead of requesting one ID for every URL, we allocate a range of 1,000 IDs to each Write Service instance.</p><p>We create a PostgreSQL sequence:</p><pre>CREATE SEQUENCE short_id_seq<br>    START WITH 1<br>    INCREMENT BY 1000<br>    MINVALUE 1<br>    NO CYCLE<br>    CACHE 1;</pre><p>Every time a Write Service instance needs a new range, it calls:</p><pre>SELECT nextval(&#39;short_id_seq&#39;);</pre><p>Suppose PostgreSQL returns 4001.</p><p>The service now owns:</p><pre>4001 - 5000</pre><p>It can generate IDs locally without making another database call for each URL.</p><p>When the range is exhausted, it requests another one.</p><p>Here’s the flow:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/984/1*A0mLBkiV5jI9tFWmNrXSYg.png" /></figure><p>The nextval() operation is atomic, so concurrent application instances receive different sequence values.</p><p>If an instance crashes with 500 unused IDs, those IDs are simply discarded.</p><p>That’s fine. We need uniqueness, not gapless sequences.</p><p>PostgreSQL sequences are specifically designed for this kind of allocation. Their values are not rolled back when transactions abort, so gaps are expected. See the <a href="https://www.postgresql.org/docs/18/functions-sequence.html">PostgreSQL sequence documentation</a>.</p><p><strong>Why </strong><strong>CACHE 1?</strong></p><p>PostgreSQL can cache sequence values per database session. Since we are already managing ranges in the application, keeping the sequence cache at one makes the allocation behaviour easier to reason about. It can still produce gaps; that is acceptable.</p><h3>Converting IDs into Base62</h3><p>Once we get the numeric ID, we convert it into Base62.</p><p>For example:</p><pre>Numeric ID: 125000<br><br>Base62 Encoding:<br>125000 -&gt; w7E<br>Fixed-length code:<br>0000w7E</pre><p>I prefer keeping generated codes at exactly seven characters.</p><p>It gives us a predictable URL format and a fixed namespace.</p><h3>Preventing Enumeration</h3><p>There is one problem with a sequential counter.</p><p>Someone can guess the next short code.</p><pre>short.ly/0000001<br>short.ly/0000002<br>short.ly/0000003</pre><p>A script could enumerate codes and discover URLs.</p><p>To reduce casual enumeration, we can scramble the numeric ID before encoding it.</p><p>One option is a bijective affine permutation over the seven-character code space:</p><pre>code_space = 62^7<br><br>scrambled = (n * K + C) mod code_space<br>short_code = base62(scrambled).padStart(7, &#39;0&#39;)</pre><p>Here, K must be coprime with the code-space size.</p><p>This preserves uniqueness while making sequential IDs less obvious.</p><p>A keyed Feistel-based permutation is another option when stronger scrambling is required.</p><p>However, neither approach is encryption or an access-control mechanism.</p><p>If a shortened URL points to sensitive information, the destination must enforce its own authentication and authorization. For genuinely unguessable private links, use longer cryptographically random tokens.</p><h3>Handling Custom Aliases</h3><p>Custom aliases introduce another possible collision.</p><p>Suppose the generated code is:</p><pre>aZ3kP9x</pre><p>A user might choose the same alias.</p><p>We don’t need a complicated solution. The primary key handles it.</p><ul><li>If the custom alias was created first, a generated-code collision results in a retry with another ID.</li><li>If the generated code already exists, the custom alias request gets HTTP 409.</li></ul><p>We can also reserve a separate alias format, such as requiring a hyphen in custom aliases, while keeping generated codes strictly seven-character Base62 strings.</p><p>Either way, the database constraint remains the final safety net.</p><h3>6. Deep Dive: How Do We Make Redirects Fast?</h3><p>Our peak traffic estimate is approximately 58,000 redirects per second.</p><p>Sending every request to PostgreSQL is unnecessary.</p><p>A primary-key lookup is efficient, but the database should not have to serve every redirect while also handling writes and replication.</p><p>We need multiple caching layers.</p><blockquote>A CDN serves the redirect at the edge, so those clicks never reach our servers and are missed by our analytics. To limit this, we cache only viral URLs at the CDN and recover their counts from CDN logs.</blockquote><h3>The Read Path</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/467/1*bqF1XeLse6UYa-3a12jLwg.png" /></figure><p>Each layer protects the one below it.</p><h3>Layer 1: CDN</h3><p>The CDN is our first line of defence.</p><p>Viral URLs can be cached at geographically distributed points of presence.</p><p>Instead of reaching our application servers, a request can be answered directly from the nearest edge location.</p><p>We can cache redirect responses with explicit cache headers.</p><p>For example:</p><pre>HTTP/1.1 302 Found<br>Location: https://example.com/products<br>Cache-Control: public, max-age=60</pre><p>The actual TTL depends on whether the link can expire, be modified or be taken down.</p><p>A longer TTL improves performance but makes takedown and updates harder.</p><h3>Layer 2: In-Process LRU Cache</h3><p>If the CDN does not have the mapping, the request reaches our Read Service.</p><p>Before calling Redis, we check a local in-memory LRU cache.</p><p>LRU stands for Least Recently Used.</p><p>When the cache reaches its configured capacity, the least recently accessed entries are removed.</p><p>This is useful for frequently accessed URLs because we avoid a network call to Redis.</p><p>The limitation is that every application instance has its own cache.</p><p>If we have ten Read Service instances, we have ten independent LRU caches.</p><p>We need another shared layer.</p><h3>Layer 3: Redis Cluster</h3><p>Redis stores frequently accessed short-code mappings.</p><p>For example:</p><pre>Key: short_url:aZ3kP9x<br>Value:<br>{<br>  &quot;long_url&quot;: &quot;https://example.com/products&quot;,<br>  &quot;expiration_time&quot;: &quot;2027-03-31T23:59:59Z&quot;,<br>  &quot;status&quot;: &quot;ACTIVE&quot;<br>}</pre><p>We run Redis as a cluster with replicas rather than relying on a single Redis instance.</p><p>If a Redis node fails, we lose some cache capacity temporarily, but PostgreSQL remains our source of truth.</p><h3>Layer 4: PostgreSQL</h3><p>If all cache layers miss, we query PostgreSQL.</p><p>Normally, reads go to a read replica.</p><p>But there is an important edge case.</p><p>Imagine a user creates a URL and immediately tries to open it.</p><p>The primary has committed the record, but an asynchronous read replica has not received or applied the transaction yet.</p><p>The replica returns no result.</p><p>If we immediately return 404, we have a read-after-write consistency problem.</p><p>There are two mechanisms to address this:</p><p><strong>Cache on write</strong></p><p>After the database transaction commits, the Write Service populates Redis with the new mapping. It also removes any negative cache entry for that short code.</p><p><strong>Primary fallback</strong></p><p>If Redis and the read replica both miss, the Read Service checks the primary before returning 404.</p><p>This protects us against replication lag and makes the immediate post-creation experience reliable.</p><p>The primary fallback must be bounded and protected with rate limiting, connection pools and negative caching. Otherwise, random requests for invalid codes could overload the primary.</p><p>An alternative is to use synchronous_commit = remote_apply for transactions that must be visible on a synchronous standby before the commit returns. This increases write latency, so we should make the trade-off consciously.</p><h3>Cache Expiration</h3><p>The cache TTL should never exceed the remaining lifetime of a URL.</p><pre>cache_ttl = min(default_cache_ttl, expiration_time - now)</pre><p>That way, an expired URL does not remain in the cache indefinitely.</p><p>The Read Service should still check the expiration time before redirecting.</p><p>An expired link returns HTTP 410 Gone.</p><h3>7. Handling Hot Keys</h3><p>Traffic in a URL shortener is not uniformly distributed.</p><p>A single link shared by a celebrity or a popular news outlet can receive more traffic than millions of other links combined.</p><p>This is the hot-key problem.</p><p>Suppose one Redis shard receives a huge percentage of all requests because every request is asking for the same short code.</p><p>That shard becomes a bottleneck.</p><p>Our existing layers already help:</p><ol><li>CDN serves popular redirects.</li><li>Local LRU caches absorb repeated requests.</li><li>Redis handles the remaining shared traffic.</li></ol><p>If a particular key is still too hot, we can replicate that cache entry across multiple Redis keys.</p><p>For example:</p><pre>short_url:aZ3kP9x#0<br>short_url:aZ3kP9x#1<br>short_url:aZ3kP9x#2<br>short_url:aZ3kP9x#3</pre><p>The Read Service selects one of these keys, spreading traffic across different Redis shards.</p><p>We should only do this for keys identified as hot through monitoring.</p><p>Replicating every key adds unnecessary memory usage and invalidation complexity.</p><h3>8. Handling Cache Stampedes</h3><p>This is one of the most important failure scenarios in the design.</p><p>Imagine a popular URL receives 10,000 requests per second.</p><p>Its Redis entry expires.</p><p>Suddenly, thousands of concurrent requests experience a cache miss and query PostgreSQL for the same record.</p><p>The database becomes overloaded. Requests start timing out, clients retry, and the situation gets worse.</p><p>This is called a cache stampede.</p><p>We can combine a few techniques to handle it.</p><h3>8.1. Request Coalescing / Singleflight</h3><p>Within one application instance, requests for the same key can be grouped together.</p><p>Only one request performs the database lookup.</p><p>Other requests wait for that result.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BuFvkdthpQ8-uqwaqYTd3A.png" /></figure><p>This reduces 100 database requests to one within that instance.</p><p>However, we may still have multiple Read Service instances.</p><h3>8.2. Distributed Locking</h3><p>To coordinate cache refreshes across instances, we can use Redis locks.</p><pre>SET lock:short_url:aZ3kP9x &lt;unique-token&gt; NX PX 2000</pre><ul><li>NX: Acquire the lock only if it doesn&#39;t exist.</li><li>PX 2000: Lock expires after two seconds.</li><li>unique-token: Identifies the owner of the lock.</li></ul><p>The instance that acquires the lock queries PostgreSQL and refreshes Redis.</p><p>Other instances briefly wait and check the cache again, rather than immediately querying the database.</p><h3>8.3. Safe Unlock Using Lua</h3><p>We cannot simply execute:</p><pre>DEL lock:short_url:aZ3kP9x</pre><p>Imagine our lock expires after two seconds, another instance acquires it, and then the original instance tries to unlock.</p><p>It could accidentally delete the second instance’s lock.</p><p>We need an atomic check-and-delete operation.</p><p>Conceptually:</p><pre>if redis.call(&quot;GET&quot;, KEYS[1]) == ARGV[1] then<br>    return redis.call(&quot;DEL&quot;, KEYS[1])<br>else<br>    return 0<br>end</pre><p>This ensures that only the owner of the lock can release it.</p><p>For cache refresh, this lock is an optimisation to reduce duplicate work, not a correctness guarantee.</p><h3>8.4. TTL Jitter</h3><p>Suppose thousands of cache entries were populated at approximately the same time with identical TTLs.</p><p>They may expire together and create a sudden burst of database traffic.</p><p>We can introduce random variation:</p><pre>actual_ttl = base_ttl + random_jitter</pre><p>For example, 10–20% random jitter spreads expiration events over time.</p><h3>8.5. Early Refresh</h3><p>For heavily accessed keys, we can refresh the cache before it expires.</p><p>Instead of waiting for a cache miss, the Read Service detects that the entry is approaching expiry and refreshes it in the background.</p><p>This is different from normal cache-aside behaviour, where we populate the cache only after a miss.</p><h3>8.6. Negative Caching</h3><p>Not every request contains a valid short code.</p><p>Bots and scanners may repeatedly request non-existent codes.</p><p>Without negative caching, every request would hit PostgreSQL.</p><p>We can cache a not-found result for a short period, such as 30–60 seconds.</p><p>When a new URL is created with that code, the Write Service must invalidate the negative cache entry.</p><h3>8.7. Bulkhead and Connection Limits</h3><p>Finally, we protect PostgreSQL itself.</p><p>The Read Service should have:</p><ul><li>Bounded database connection pools.</li><li>Timeouts on database requests.</li><li>Limits on concurrent cache-miss lookups.</li><li>Circuit breakers and controlled retry policies.</li></ul><p>When the database is struggling, we should shed load rather than let every request wait indefinitely.</p><p>A slow database should degrade the redirect service gradually, not bring down the entire application.</p><h3>9. Deep Dive: Database Replication and High Availability</h3><p>Our PostgreSQL primary handles writes.</p><p>We have already reduced most read traffic using CDN, LRU and Redis.</p><p>Now the question is: what happens when the primary database fails?</p><p>A single database instance is a single point of failure.</p><p>We need replication and automatic failover.</p><h3>Database Topology</h3><p>I’d use one primary and two synchronous standbys in separate availability zones.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/494/1*PDP3-A6hPH3lEmo7HA8e0w.png" /></figure><p>The configuration concept is:</p><pre>synchronous_standby_names =<br>    &#39;ANY 1 (standby_a, standby_b)&#39;</pre><p>This means a transaction waits for acknowledgement from at least one of the configured synchronous standbys.</p><p>With synchronous_commit = on, PostgreSQL waits for the standby to flush the relevant WAL to durable storage.</p><p>It does not necessarily wait for that standby to replay the WAL and make the change visible to queries.</p><p>For that stronger visibility guarantee, PostgreSQL provides:</p><pre>synchronous_commit = remote_apply</pre><p>That waits until the synchronous standby has replayed the transaction.</p><p>We don’t necessarily need remote_apply for every transaction because our Read Service already has primary fallback.</p><p>The trade-off is straightforward:</p><ul><li>on: Better write latency, with durable WAL on a synchronous standby.</li><li>remote_apply: Stronger immediate read visibility on the synchronous standby, at the cost of additional write latency.</li></ul><p>Official reference: <a href="https://www.postgresql.org/docs/current/warm-standby.html">PostgreSQL synchronous replication</a>.</p><h3>What Happens When the Primary Fails?</h3><p>Synchronous replication protects committed data, but it doesn’t automatically promote a standby.</p><p>We need a cluster management mechanism.</p><p>I’d use:</p><ul><li><strong>Patroni:</strong> Manages PostgreSQL cluster health, leader election and failover.</li><li><strong>etcd:</strong> Provides distributed coordination and leader information.</li><li><strong>Database proxy or stable endpoint:</strong> Directs applications to the current primary.</li></ul><p>The important point is that quorum commit and leader election are two different problems.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/520/1*TdBYjLFsxvRUR8FqbVLJAg.png" /></figure><p>Patroni’s synchronous mode tracks which standby nodes are eligible for safe promotion.</p><p>If a node cannot be proven to contain all successfully acknowledged commits, we should not automatically promote it when data loss is unacceptable.</p><p>This introduces an important trade-off: in some failure scenarios, we may temporarily stop accepting writes rather than promote a replica that could lose acknowledged data.</p><p>With quorum-based synchronous replication, Patroni maintains additional state to reason about which standby contains the latest committed WAL.</p><p>It is important to configure Patroni’s synchronous/quorum mode properly instead of assuming that a PostgreSQL ANY 1 setting alone provides a complete failover solution.</p><p>Reference: <a href="https://patroni.readthedocs.io/en/latest/replication_modes.html">Patroni replication modes</a>.</p><h3>What Happens to Our Allocated ID Ranges During Failover?</h3><p>This is an interesting edge case.</p><p>Suppose Write Service instance 1 already owns:</p><pre>4001 - 5000</pre><p>It has used only 200 IDs when the PostgreSQL primary fails.</p><p>There is no problem with the application continuing to use the unused IDs, provided the allocated range has been safely persisted on a standby.</p><p>The important rule is that an application must not start using a range until the nextval() operation has successfully completed under the configured synchronous commit policy.</p><p>If the service crashes, its unused IDs are discarded.</p><p>If the primary fails, Patroni must promote a standby that contains the acknowledged sequence advancement.</p><p>If a failover cannot prove that the most recent acknowledged allocation is present, automatic promotion must not be treated as lossless.</p><p>This is why ID allocation, synchronous replication and failover cannot be designed independently.</p><h3>What About Redis?</h3><p>Redis is a cache, not our source of truth.</p><p>If Redis fails, the system should continue working by falling back to PostgreSQL.</p><p>However, a complete Redis failure can produce a massive increase in database traffic.</p><p>We should therefore use Redis Cluster/Sentinel with replicas and monitor failovers, cache hit ratios and per-shard load.</p><h3>Do We Need Multi-Region Deployment?</h3><p>Not initially.</p><p>If we eventually need writes from multiple regions, we can allocate disjoint numeric ID ranges to each region.</p><p>For example:</p><pre>Region A: 0 - 1 billion<br>Region B: 1 - 2 billion<br>Region C: 2 - 3 billion</pre><p>Each region generates IDs from its own range, avoiding coordination for every new URL.</p><p>Regional CDNs and read replicas handle redirects.</p><p>We also need to think about replication lag between regions, regional failover and recovery of the ID allocator. Multi-region writes introduce substantially more complexity, so I would not add them without an actual requirement.</p><h3>When Would I Introduce Database Sharding?</h3><p>Not at this scale.</p><p>We have:</p><ul><li>Approximately 12 average writes per second.</li><li>Around 1,160 writes per second during our assumed peak.</li><li>Approximately 500 GB of raw data.</li><li>Most reads handled by caching.</li></ul><p>I’d first scale vertically, add read capacity and optimise the database.</p><p>If the primary eventually becomes a write bottleneck or the dataset exceeds the practical capacity of one database node, we can introduce sharding using short_code as the shard key.</p><p>The important thing is to introduce sharding when the measurements justify it, not because the architecture diagram looks more distributed.</p><h3>10. Click Analytics Without Affecting Redirect Latency</h3><p>Click analytics are not part of the core requirements, but they are a natural extension.</p><p>We may want to track:</p><ul><li>Total clicks.</li><li>Country and city.</li><li>Device and browser.</li><li>Referrer.</li><li>Clicks over time.</li></ul><p>We should not update a PostgreSQL row synchronously on every redirect.</p><p>At 58,000 requests per second, that would put enormous write pressure on the database.</p><p>Instead, we publish click events asynchronously.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/250/1*2ZFLiUS1mYE5AaAt_3IZ3w.png" /></figure><p>An example event:</p><pre>{<br>  &quot;short_code&quot;: &quot;aZ3kP9x&quot;,<br>  &quot;timestamp&quot;: &quot;2026-11-14T18:30:00Z&quot;,<br>  &quot;country&quot;: &quot;IN&quot;,<br>  &quot;device&quot;: &quot;mobile&quot;,<br>  &quot;referrer&quot;: &quot;whatsapp&quot;<br>}</pre><p>The redirect should not wait for analytics processing.</p><p>If the analytics pipeline is down, links should continue working.</p><p>One important detail: if the CDN caches redirect responses, those clicks never reach our Read Service.</p><p>So if we need complete click analytics, we should also consume CDN access logs or edge-generated events.</p><p>We can aggregate the data in a separate analytics-oriented database rather than putting this workload on PostgreSQL.</p><h3>11. Handling URL Expiration and Takedowns</h3><p>We have two different cases:</p><ol><li>A URL expires naturally.</li><li>A URL needs to be disabled because of abuse, spam or a legal request.</li></ol><p>Both need to invalidate cached entries.</p><h3>Expiration Cleanup</h3><p>Every redirect request checks whether the URL has expired.</p><p>If it has expired, we return:</p><pre>HTTP/1.1 410 Gone</pre><p>We don’t have to delete the row immediately.</p><p>A periodic background job can remove expired mappings in small batches.</p><p>For example:</p><pre>WITH expired AS (<br>    SELECT short_code<br>    FROM short_urls<br>    WHERE expiration_time &lt; NOW()<br>    ORDER BY expiration_time<br>    LIMIT 10000<br>    FOR UPDATE SKIP LOCKED<br>)<br>DELETE FROM short_urls s<br>USING expired e<br>WHERE s.short_code = e.short_code;</pre><p>The FOR UPDATE SKIP LOCKED approach allows multiple cleanup workers to process different batches without waiting on rows already locked by another worker.</p><p>Small batches also reduce long-running transactions and replication pressure.</p><p>We can archive records before deletion if an audit trail is required.</p><h3>Takedown Flow</h3><p>Suppose a URL is reported for phishing.</p><p>Deleting the row from PostgreSQL is not sufficient.</p><p>The mapping may still exist in:</p><ul><li>CDN cache.</li><li>Redis.</li><li>Local LRU caches on multiple application instances.</li></ul><p>We need a proper invalidation flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/630/1*fVIhpRmDA9RgX5zU9IX29w.png" /></figure><p>The sequence is:</p><ol><li>Update the URL status in PostgreSQL.</li><li>Record an invalidation event in a transactional outbox.</li><li>Publish the event to a durable event bus.</li><li>Remove the mapping from Redis.</li><li>Notify Read Service instances to invalidate their local LRU entries.</li><li>Purge the corresponding CDN entry.</li></ol><p>Why use an outbox?</p><p>If the database update succeeds but the application crashes before publishing the event, a simple publish-after-commit approach can lose the invalidation message.</p><p>The transactional outbox ensures the database update and event record are committed together. A separate publisher delivers the event.</p><p>There is one limitation worth calling out.</p><p>CDN purge and pub/sub propagation are not instantaneous guarantees.</p><p>If a CDN has cached a 302 response, it may continue serving that response until its cache expires or the purge takes effect.</p><p>For stricter takedown requirements, we need short CDN TTLs, reliable invalidation and potentially an authoritative revocation check at the edge.</p><p>We should not claim that pub/sub alone provides immediate global takedowns.</p><h3>12. Abuse Prevention and Security</h3><p>A URL shortener can easily become a platform for phishing and malicious redirects.</p><p>We should validate URLs at creation time.</p><p>Some basic checks include:</p><ul><li>Allow only http and https schemes.</li><li>Reject malformed URLs and unsupported protocols.</li><li>Check known malware and phishing databases.</li><li>Apply per-user and per-IP creation limits.</li><li>Monitor suspicious redirect patterns.</li></ul><p>If we fetch submitted URLs for scanning or previews, that operation must run in an isolated environment.</p><p>Otherwise, a malicious destination could exploit our backend to access internal services or cloud metadata endpoints. The scanning service should block private, loopback and link-local addresses and protect against DNS rebinding.</p><p>Finally, short-code scrambling is not authorization.</p><p>For links containing sensitive information, the destination must still enforce access control.</p><h3>13. Observability: How Do We Know the System Is Healthy?</h3><p>A staff-level design should also cover how we operate the system after deployment.</p><p>Here are the metrics I’d start with:</p><p>ComponentImportant metricsCDNCache hit ratio, edge latency, purge latencyRead ServiceP95/P99 latency, redirect errors, request rateWrite ServiceCreation latency, 409 rate, failed requestsRedisHit ratio, per-shard QPS, memory, eviction ratePostgreSQLQuery latency, connection usage, replication lagID allocatorRange allocation failures, unused ranges, duplicate retriesKafkaConsumer lag, event processing throughputPatroniLeader changes, failover duration, standby health</p><p>I’d pay particular attention to three metrics:</p><p><strong>1. Cache hit ratio:</strong> A sudden drop can increase PostgreSQL traffic dramatically.</p><p><strong>2. Replication lag:</strong> This affects read consistency and the reliability of the primary fallback strategy.</p><p><strong>3. Database connection pool saturation:</strong> This is often an early sign that cache misses or a slow database are creating a larger incident.</p><p>We should also track availability and redirect latency by geography, not just from a single monitoring location.</p><h3>14. Final Architecture</h3><p>Let’s put everything together.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uBZtycgtnMTfuS2FeIWdtw.png" /></figure><h3>Write Path Summary</h3><ol><li>The client submits a URL through the authenticated API Gateway.</li><li>The Write Service validates the request.</li><li>It gets an ID from its locally held range, allocating a new range from PostgreSQL when required.</li><li>The ID is scrambled and converted to a seven-character Base62 code.</li><li>The mapping is inserted into the PostgreSQL primary.</li><li>The transaction is synchronously replicated according to our durability policy.</li><li>After commit, the Write Service populates Redis and clears any negative cache entry.</li><li>The short URL is returned.</li></ol><p>If the custom alias already exists, the insert fails with a unique constraint violation and we return 409.</p><h3>Read Path Summary</h3><ol><li>The browser requests the shortened URL.</li><li>The CDN serves a cached redirect if available.</li><li>Otherwise, the request reaches the Read Service through the public redirect route, with rate limiting but no user authentication.</li><li>The service checks its local LRU.</li><li>On a miss, it checks Redis.</li><li>On another miss, it queries a PostgreSQL read replica.</li><li>If the record is missing, it checks the primary before returning 404.</li><li>It validates the status and expiration time, then returns a 302 redirect.</li></ol><p>Request coalescing, distributed locks, TTL jitter, early refresh, negative caching and database bulkheads protect the cache-miss path.</p><h3>15. What Is Expected at Each Interview Level?</h3><p>I find it useful to think about the design in terms of what an interviewer expects from different engineering levels.</p><h3>Mid-Level Engineer</h3><p>You should be able to explain the complete flow:</p><ul><li>How a long URL becomes a short URL.</li><li>How the mapping is stored.</li><li>How redirects work.</li><li>Why Base62 is useful.</li><li>Why 302 is preferable to 301 for this use case.</li><li>Why we need a database index and cache.</li></ul><p>The interviewer may help you reach some of these decisions.</p><h3>Senior Engineer</h3><p>You should lead most of the discussion.</p><p>You should be able to:</p><ul><li>Compare hashing, random codes and counters.</li><li>Explain how multiple application instances generate unique IDs.</li><li>Justify PostgreSQL over Redis as the durable source of truth.</li><li>Separate read and write paths.</li><li>Design the caching layers.</li><li>Discuss consistency, expiry and cache invalidation.</li><li>Explain database replication and the trade-offs involved.</li></ul><h3>Staff Engineer and Above</h3><p>The discussion should go beyond the happy path.</p><p>You should start with capacity numbers and use them to justify architectural decisions.</p><p>More importantly, you should proactively discuss failure scenarios:</p><ul><li>What happens when a popular Redis key expires?</li><li>What happens when a Read Service instance crashes while holding an ID range?</li><li>Can a database failover cause an ID range to be allocated twice?</li><li>What happens when a synchronous standby is unavailable?</li><li>Can a CDN continue redirecting users after a takedown?</li><li>What happens when the analytics pipeline is down?</li><li>How do we know the system is degrading before users report an issue?</li></ul><p>You should also be able to explain why you are not introducing certain technologies.</p><p>For example, why not shard PostgreSQL when the workload is still manageable by one primary?</p><p>A good staff-level design is not the one with the most components. It is the one where every component has a reason to exist.</p><h3>Closing Thoughts</h3><p>The interesting part of designing a URL shortener isn’t generating a short string. That is relatively straightforward.</p><p>The real engineering challenges come from ensuring uniqueness across distributed application instances, making redirects fast, handling database failures and keeping cached data consistent.</p><p>I’d start with PostgreSQL as the primary data store, allocate IDs in batches, use Base62 encoding and separate the read and write paths.</p><p>For the read-heavy workload, I’d introduce CDN caching, in-process LRU and Redis before allowing requests to reach PostgreSQL.</p><p>I’d keep analytics asynchronous, build a reliable takedown mechanism and introduce sharding only when actual capacity requirements justify it.</p><p>One thing I would particularly emphasise in an interview is the relationship between ID allocation and database failover. It is easy to draw a sequence and assume that uniqueness is solved. But once application instances hold ranges in memory, we need to think about what happens when the primary changes.</p><p>The same applies to caching. Adding Redis is easy. Understanding what happens when a viral link expires from Redis is where the interesting design discussion begins.</p><p><strong>Start with the numbers, identify the bottlenecks, and design for the failure cases. Don’t introduce complexity just to make the architecture look scalable.</strong></p><p><em>If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, Python, and backend engineering!</em></p><h3>References</h3><ol><li><a href="https://youtu.be/AVztRY77xxA">https://youtu.be/AVztRY77xxA</a></li><li><a href="https://www.hellointerview.com/learn/system-design/problem-breakdowns/bitly">Hello Interview — Design a URL Shortener Like Bitly</a></li><li><a href="https://www.postgresql.org/docs/18/functions-sequence.html">PostgreSQL — Sequence Manipulation Functions</a></li><li><a href="https://www.postgresql.org/docs/current/warm-standby.html">PostgreSQL — Synchronous Replication</a></li><li><a href="https://patroni.readthedocs.io/en/latest/replication_modes.html">Patroni — Replication Modes and Failover</a></li><li><a href="https://www.rfc-editor.org/rfc/rfc9110.html">RFC 9110 — HTTP Semantics</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c110e1bc929b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Temporal Internals: How Durable Execution, Replay and Workflow Recovery Actually Work]]></title>
            <link>https://medium.com/@vjgupta78/temporal-internals-how-durable-execution-replay-and-workflow-recovery-actually-work-7c36b50c165d?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/7c36b50c165d</guid>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[system-design-interview]]></category>
            <category><![CDATA[system-design-concepts]]></category>
            <category><![CDATA[temporal]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Sat, 14 Nov 2026 16:23:06 GMT</pubDate>
            <atom:updated>2026-11-14T16:23:06.456Z</atom:updated>
            <content:encoded><![CDATA[<p>Imagine a customer placing an order on your application.</p><p>Once the order is placed, several things need to happen:</p><ol><li>Charge the customer’s card.</li><li>Reserve the inventory.</li><li>Request shipment.</li><li>Wait until the package is delivered.</li><li>Send a review email.</li></ol><p>At first glance, this looks like a fairly simple background process. We can write a function that calls these operations one after another.</p><pre>def process_order(order):<br>  charge_customer(order)<br>  reserve_inventory(order)<br>  request_shipment(order)<br>  <br>  while not is_delivered(order):<br>    sleep(5 days)<br>  <br>  send_review_email(order)</pre><p>The code is straightforward. But there is a problem.</p><p><strong>What happens if our application crashes after charging the customer but before reserving the inventory?</strong></p><p>What happens if the worker restarts after three days of waiting for the package? Will it start everything again? Will the customer get charged twice?</p><p>We can solve these problems by adding database tables, job queues, retry mechanisms and status checks. But as the number of steps increases, we slowly start building our own workflow engine.</p><p>This is the problem Temporal tries to solve.</p><blockquote>Temporal is a workflow orchestration system built around a concept called <strong>Durable Execution</strong>. It allows a process to survive application crashes, worker restarts, retries and long waiting periods without losing its progress.</blockquote><p>Let’s understand how it works, starting with a simple function and gradually moving into Temporal’s internal architecture.</p><h3>1. Understanding Durable Execution through Replay</h3><p>Most application code is designed around the assumption that the process executing it will remain alive until the work is completed.</p><p>That assumption is not always true.</p><p>A server can crash, a container can restart, a deployment can happen, or a worker can become unavailable. When that happens, the in-memory state of the application is lost.</p><p>Traditional background processing usually handles this by saving the current status in a database and allowing another worker to pick up the job later.</p><p>But this means we need to manage several things ourselves:</p><ul><li>Which steps have completed?</li><li>Which steps are still pending?</li><li>Should an operation be retried?</li><li>What happens if a worker crashes between two operations?</li><li>How do we resume a process that has been waiting for several days?</li></ul><p>Temporal approaches this differently.</p><p>Instead of expecting the original application process to remain alive, Temporal records the progress of a Workflow in a durable Event History.</p><p>When a worker crashes, another worker can reconstruct the execution using that history.</p><p>Let’s understand this with a smaller example.</p><p>Suppose we have a Workflow that fetches a stock price and calculates a value based on it.</p><pre>@activity.defn<br>async def fetch_stock_price(symbol: str) -&gt; float:<br>    return 2500.0<br><br>@workflow.defn<br>class StockWorkflow:<br>    @workflow.run<br>    async def run(self, symbol: str) -&gt; float:<br>        price = await workflow.execute_activity(<br>            fetch_stock_price,<br>            symbol,<br>            start_to_close_timeout=timedelta(seconds=10),<br>        )<br>        return price * 1.1</pre><p>There are two important things happening here.</p><p>First, fetch_stock_price() performs an external operation. Temporal records the result returned by this Activity.</p><p>Second, price * 1.1 is ordinary Workflow logic. It is calculated as the Workflow executes.</p><p>Now imagine the worker crashes after the Activity has completed.</p><p>A new worker picks up the Workflow and starts executing the code again.</p><p>Does Temporal call fetch_stock_price() again?</p><p>No. Since the completed Activity result is already recorded in the Event History, Temporal reuses that result during replay.</p><p>The calculation price * 1.1 runs again using the previously recorded price.</p><p>This is the basic idea behind <strong>Replay</strong>.</p><blockquote>Replay means running the Workflow code again and substituting previously recorded results whenever an operation has already completed. This allows Temporal to reconstruct the Workflow’s state without restoring the original worker’s memory.</blockquote><h3>What happens in our order example?</h3><p>Suppose the execution went like this:</p><ul><li>Customer payment was successful.</li><li>Inventory was reserved successfully.</li><li>Worker crashed before requesting shipment.</li></ul><p>Temporal has already recorded the results of the first two operations.</p><p>When a new worker picks up the Workflow, it replays the code. It encounters the payment operation and retrieves its recorded result. It then does the same for the inventory reservation.</p><p>Finally, it reaches the shipment operation, which has not completed yet, and executes it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x4PoSPhJPcWa5p1Xo5LB1A.png" /></figure><p>The new worker does not need to start the entire process from scratch.</p><p>This is how Temporal makes long-running processes recoverable.</p><h3>2. Workflow vs Activity: Two important building blocks</h3><p>Replay introduces an important design decision.</p><p>Not all code can be executed again safely.</p><p>For example, consider these operations:</p><ul><li>Charging a customer through Stripe.</li><li>Updating an inventory database.</li><li>Calling a shipping provider.</li><li>Sending an email.</li></ul><p>These operations interact with the outside world.</p><p>If Temporal executes them again during replay, we could charge the customer twice or send duplicate emails.</p><p>To avoid this problem, Temporal separates application logic into two categories.</p><p><strong>Workflow</strong></p><blockquote>A Workflow contains the orchestration logic. It decides what needs to happen and in what order.</blockquote><blockquote>The Workflow code must be deterministic because Temporal may execute it again during replay.</blockquote><p><strong>Activity</strong></p><blockquote>An Activity performs work that interacts with external systems.</blockquote><blockquote>Activities can call APIs, access databases, send emails and perform other operations outside the Workflow.</blockquote><p>Temporal records the results of completed Activities so that replay can reuse them.</p><p>Our order-processing flow can now be represented like this:</p><pre>@workflow.defn<br>class OrderWorkflow:<br>@workflow.run<br>    async def run(self, order):<br>        await workflow.execute_activity(<br>            charge_customer,<br>            order,<br>        )<br>        await workflow.execute_activity(<br>            reserve_inventory,<br>            order,<br>        )<br>        await workflow.execute_activity(<br>            request_shipment,<br>            order,<br>        )<br>        # Wait for delivery<br>        await workflow.execute_activity(<br>            send_review_email,<br>            order,<br>        )</pre><p>The actual implementation of payment, inventory and shipment operations lives inside separate Activities.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*IYaAeyRE__NfaWYdNXi09Q.png" /></figure><h3>Idempotency: What happens when an Activity is retried?</h3><p>There is still an important failure scenario we need to consider.</p><p>Suppose Temporal sends an Activity request to Stripe.</p><p>Stripe successfully charges the customer, but the worker crashes before Temporal receives confirmation of the Activity’s completion.</p><p>From Temporal’s perspective, the Activity might not have completed.</p><p>What happens next?</p><p>Temporal may retry the Activity.</p><p>This means the external payment request could be sent again.</p><p>This is why external operations need idempotency.</p><p>For example, instead of generating a new payment request every time, we can use a stable idempotency key:</p><pre>Payment Activity<br>    |<br>    v<br>Stripe API<br>    |<br>    v<br>Idempotency Key: paymentId_123</pre><p>Similarly, we can use separate keys for inventory reservation and shipment requests.</p><pre>Idempotency-Key (client, per checkout attempt): K<br><br>reserveInventory   -&gt; key = K + &quot;:reserve&quot;<br>createPayment      -&gt; key = K + &quot;:payment&quot;<br>createShipment     -&gt; key = K + &quot;:dispatch&quot;</pre><p>The idea is that retrying the same logical operation should not create a second side effect.</p><p>Temporal provides retries, but it cannot guarantee that an external API will never receive the same request more than once. The external system must also support safe handling of duplicate requests, or we need to implement that protection ourselves.</p><h3>What about undoing a completed operation?</h3><p>Now consider another situation.</p><p>The payment was successful and inventory was reserved, but the shipment request failed permanently.</p><p>We may not want to leave the customer’s money charged and the inventory locked.</p><p>We need to reverse the completed business operations.</p><p>For example:</p><pre>Normal execution:<br>Charge Customer<br>      |<br>      v<br>Reserve Inventory<br>      |<br>      v<br>Request Shipment<br><br>Compensation:<br>Release Inventory<br>      |<br>      v<br>Refund Payment</pre><p>These reverse operations are known as compensating operations.</p><blockquote>Temporal does not automatically roll back external side effects. It does not magically refund a payment or release inventory.</blockquote><blockquote>We need to implement those reverse operations as Activities and decide when they should run.</blockquote><p>For example, if shipment creation fails, our Workflow can:</p><ol><li>Release the inventory reservation.</li><li>Refund the payment.</li><li>Mark the order as failed.</li></ol><p>This is different from database rollback. Once an external system has completed an operation, we generally need another business operation to compensate for it.</p><h3>3. Why Workflow code must be deterministic</h3><p>Now that we understand Replay, there is another important requirement.</p><p>Replay only works when the Workflow follows the same execution path every time it is reconstructed from the same history.</p><p>In other words, Workflow code must be deterministic.</p><p>Consider this example:</p><pre>def process_order(order):<br>charge_customer(order)<br>    reserve_inventory(order)<br>    if current_time() &lt; &quot;5:00 PM&quot;:<br>        request_same_day_pickup(order)<br>    else:<br>        request_next_day_pickup(order)<br>    wait_until_delivered(order)<br>    send_review_email(order)</pre><p>Suppose a customer places an order at 4:59 PM.</p><p>The first execution checks the time and chooses same-day pickup.</p><p>Now imagine the worker crashes, and another worker replays this Workflow at 5:03 PM.</p><p>The same time check now returns a different result.</p><p>The original execution took the same-day pickup branch, but the replay takes the next-day pickup branch.</p><p>This breaks the deterministic execution model.</p><h3>How does Temporal solve this?</h3><p>Instead of reading the machine’s system clock directly, the Workflow should use Temporal’s Workflow Clock.</p><p>For example:</p><pre># Non-deterministic<br>start = datetime.now()<br><br># Deterministic<br>start = workflow.now()<br>await workflow.sleep(50)</pre><p>Temporal records the information needed to reconstruct the same logical time during replay.</p><p>So even when another worker executes the Workflow later, it gets the same logical time recorded for the original execution.</p><p>The general rule is simple:</p><blockquote><strong>Workflow code should make decisions using information that Temporal can consistently reproduce during replay.</strong></blockquote><p>Operations that depend on changing external state, such as database queries or external API calls, generally belong inside Activities.</p><h3>4. How does Temporal handle long waiting periods?</h3><p>Let’s return to our order example.</p><p>After requesting shipment, we need to wait for the package to be delivered.</p><p>One way to write this is:</p><pre>sleep(5 days)</pre><p>But we cannot keep a worker process running for five days just to wait.</p><p>Imagine having thousands or millions of Workflows waiting for different durations. Keeping a process or thread occupied for every waiting Workflow would be wasteful.</p><blockquote>Temporal solves this using <strong>Durable Timers</strong>.</blockquote><h3>Understanding Durable Timers</h3><p>When a Workflow reaches:</p><pre>await workflow.sleep(5 days)</pre><p>Temporal does not simply block a worker for five days.</p><p>Instead, it calculates the timer’s deadline and persists the timer information.</p><p>Conceptually, the history contains an event similar to:</p><pre>TimerStarted {<br>    fireAt: May 15, 10:00 AM<br>}</pre><p>A background timer processor monitors these durable timers.</p><p>It can process timers based on their deadlines without keeping a separate worker running for each waiting Workflow.</p><p>When the deadline arrives, Temporal records the timer firing:</p><pre>TimerFired</pre><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*-OmIYi1j7Ch89mhH7ZZf1A.png" /></figure><p>The Workflow is then scheduled to execute again.</p><p>During replay, Temporal encounters the sleep operation and checks the recorded history:</p><pre>sleep(5 days)<br><br>TimerStarted already exists<br>TimerFired exists<br>Continue execution</pre><p>If the timer has not fired yet, the Workflow simply waits again.</p><p>A Workflow can wait for five days, five months or even longer without consuming a worker process for the entire duration.</p><p>The History Service stores a Timer Task with its deadline. A background timer processor reads these tasks and processes them when they become due.</p><p>The processor serves many Workflows. Each sleeping Workflow does not need its own process.</p><p>If the History Service instance crashes, another instance can take ownership of the relevant History Shard and recover pending timers from durable storage.</p><blockquote>An outage may delay a timer from firing, but it does not erase the timer.</blockquote><h3>5. What if we don’t know how long we need to wait?</h3><p>A five-day timer works when we know exactly how long we need to wait.</p><p>But real-world processes are not always predictable.</p><p>In our order example, the package might arrive tomorrow, or it might take a week.</p><p>What we actually want is not to wait for five days. We want to wait until the shipping carrier tells us that the package has been delivered.</p><blockquote>This is where <strong>Temporal Signals</strong> come into the picture.</blockquote><h3>Understanding Signals</h3><p>Imagine the Workflow reaches:</p><pre>wait_until_delivered(order)</pre><p>At this point, the Workflow has nothing else to do. It can stop executing and wait for an external event.</p><p>Three days later, the shipping carrier sends a webhook informing us that the package has been delivered.</p><p>Our webhook handler receives this notification and sends a Signal to the corresponding Temporal Workflow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2uVYWE3Z25t9W5y8t19wTw.png" /></figure><p>Temporal records the Signal in the Workflow’s Event History and schedules the Workflow to run again.</p><p>The replay now encounters the delivery event:</p><pre>ChargeCustomerCompleted<br>ReserveInventoryCompleted<br>RequestShipmentCompleted<br>PackageDelivered</pre><p>The Workflow reaches the waiting condition:</p><pre>waitUntilDelivered(order)<br><br>PackageDelivered exists<br>Continue</pre><p>It then proceeds with the next Activity:</p><pre>sendReviewEmail(order)<br><br>Execute<br>Record result</pre><blockquote>A Signal allows another system to send information to a running Workflow, even when that Workflow has been idle for days or weeks.</blockquote><p>No worker needs to remain occupied while waiting for the shipping provider. When the external event arrives, Temporal uses the durable history to reconstruct the Workflow and continue execution.</p><h3>6. Understanding Temporal’s internal architecture</h3><p>So far, we have understood how Temporal handles Replay, Activities, deterministic execution, Timers and Signals.</p><p>But where does all this information live? How does Temporal know which worker should execute the next operation?</p><p>A Temporal deployment has a few important components:</p><ul><li>Temporal Client</li><li>Frontend Service</li><li>History Service</li><li>Matching Service</li><li>Worker Service</li><li>Persistence Layer</li><li>Application Workers</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*InsNNxhDuBOh0UfZoAnwUg.png" /></figure><p>Let’s understand their responsibilities.</p><h3>6.1 Temporal Client</h3><p>The Temporal Client is what our application uses to communicate with the Temporal Server.</p><p>It provides APIs to:</p><ol><li>Start a Workflow Execution.</li><li>Send a Signal to a Workflow Execution.</li><li>Query a Workflow Execution.</li><li>List Workflow Executions.</li><li>Get the result of a Workflow Execution.</li></ol><p>For example, when a customer places an order, our application can start an Order Workflow using the Temporal Client.</p><p>The client does not execute the Workflow itself. It communicates with the Temporal Server.</p><h3>6.2 Frontend Service</h3><p>The Frontend Service is the entry point into the Temporal cluster.</p><p>Both Clients and application Workers connect to the Temporal Server through the Frontend.</p><p>When a client starts a Workflow or sends a Signal, the request first reaches the Frontend Service.</p><p>The Frontend then routes the request to the appropriate backend service.</p><p>The Frontend is designed to be stateless, which makes it possible to scale multiple Frontend instances independently.</p><h3>6.3 History Service</h3><p>The History Service is responsible for maintaining the durable state of Workflow Executions.</p><p>Whenever something important happens during a Workflow, Temporal records it in an ordered Event History.</p><p>For example:</p><pre>WorkflowStarted<br>ActivityScheduled<br>ActivityStarted<br>ActivityCompleted<br>TimerStarted<br>TimerFired<br>SignalReceived<br>WorkflowCompleted</pre><p>The actual event sequence depends on what happens during execution.</p><p>This history allows Temporal to reconstruct the Workflow after a failure.</p><p>The History Service also processes Workflow state transitions and manages internal tasks such as timers, transfers, replication and visibility indexing.</p><p>We will look at how it stores and processes Workflow state in the next section.</p><h3>6.4 Matching Service</h3><p>The Matching Service manages Task Queues and coordinates the delivery of tasks to application Workers.</p><p>When a Workflow or Activity needs to execute, the History Service generates the necessary work.</p><p>The Matching Service makes that task available to a Worker polling the appropriate Task Queue.</p><p>It also handles Worker polling and Task Queue partitions.</p><h3>6.5 Worker Service</h3><p>There is a small distinction worth understanding here.</p><p>The Worker Service handles Temporal’s own internal background functionality.</p><p>It takes care of internal/system Workflows and operational tasks such as:</p><ul><li>Maintenance jobs.</li><li>Cleanup.</li><li>Replication.</li><li>Archival.</li><li>Visibility indexing.</li></ul><p>For example, archiving old Event Histories and running scheduled maintenance are part of the operational work handled by Temporal’s internal Worker Service.</p><p>This is different from our application Workers, which execute our own Workflow and Activity code.</p><h3>6.6 Application Workers</h3><p>Temporal Server does not execute our application Workflow and Activity code inside the Temporal Service.</p><p>Instead, we run separate Worker processes.</p><p>These Workers:</p><ul><li>Poll Temporal for tasks.</li><li>Execute Workflow or Activity code.</li><li>Return the results to Temporal.</li></ul><p>This separation is useful because we can scale application Workers independently of the Temporal Server.</p><h3>6.7 Persistence Layer</h3><p>Temporal needs durable storage for Workflow state, Event History and internal tasks.</p><p>This is handled by the Persistence Layer.</p><p>The persistence backend is configurable. The database choices in the notes include Cassandra, MySQL, PostgreSQL and SQLite, depending on the deployment and configuration.</p><p>The important point is that Workflow progress does not exist only in worker memory. It is backed by durable storage.</p><h3>7. How Temporal distributes Workflow state: History Shards</h3><p>A Temporal cluster may be responsible for millions of Workflow Executions.</p><p>The History Service cannot have a single machine responsible for processing every Workflow.</p><p>So how does Temporal distribute this work?</p><p>It uses <strong>History Shards</strong>.</p><p>Workflow Executions are divided across a fixed number of History Shards.</p><p>Each History Service instance owns a subset of these Shards and is responsible for processing requests and timers for the Workflows inside them.</p><p>For example:</p><pre>History Service A<br>    Shard 1<br>    Shard 2<br>    Shard 3</pre><pre>History Service B<br>    Shard 4<br>    Shard 5<br>    Shard 6</pre><p>The actual allocation depends on the cluster’s shard configuration and ownership.</p><p>If History Service instances are added, removed or fail, ownership of their Shards can move between instances.</p><p>This allows Temporal to distribute Workflow processing across multiple History Service instances.</p><h3>Event History vs Mutable State</h3><p>Event History tells us everything that has happened in a Workflow.</p><p>For example:</p><pre>WorkflowStarted<br>PaymentActivityCompleted<br>InventoryActivityCompleted<br>ShipmentActivityScheduled</pre><p>But the History Service also needs an efficient way to know what is happening right now.</p><p>For example:</p><ul><li>Which Activities are pending?</li><li>Which timers are outstanding?</li><li>Are there child Workflows?</li><li>What work needs to happen next?</li></ul><p>Reconstructing all this information from the entire Event History every time would be expensive.</p><p>Temporal maintains something called <strong>Mutable State</strong>.</p><p>Think of Mutable State as a materialised view of the current server-side Workflow state.</p><p>It contains information such as pending Activities, outstanding timers and child Workflows.</p><p>The distinction is:</p><ul><li>Event History is the durable record of what happened.</li><li>Mutable State is an efficient representation of the current execution state.</li></ul><p>Both are important for managing Workflow progress.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ph6YsfhcQgDrCXQXq7xdVw.png" /></figure><h3>8. Why does Temporal need Transfer Tasks?</h3><p>The History Service needs to make sure that updating Workflow state and scheduling whatever happens next do not get separated by a crash.</p><p>Let’s consider a situation where an Activity completes successfully.</p><p>The History Service needs to do two things:</p><ol><li>Record the Activity completion and update the Workflow state.</li><li>Schedule the next work so that a Worker can execute it.</li></ol><p>Now imagine the History Service updates the database successfully but crashes before sending the next task to the Matching Service.</p><p>The database knows that the Activity completed.</p><p>But the Matching Service never received the next task.</p><p>The Workflow might get stuck because nobody knows that there is work to be executed.</p><p>How does Temporal avoid this?</p><p>It uses an internal mechanism called <strong>Transfer Tasks</strong>.</p><h3>How Transfer Tasks work</h3><p>When the History Service processes an Activity completion, it persists three important things together:</p><ol><li>Event History update.</li><li>Mutable State update.</li><li>A Transfer Task.</li></ol><p>A Transfer Task is an internal task created by Temporal’s History Service to tell it to deliver work to the Matching Service.</p><p>Conceptually, the flow looks like this:</p><pre>Activity completes<br>       |<br>       v<br>History Service<br>       |<br>       | 1. Record Event<br>       | 2. Update Mutable State<br>       | 3. Create Transfer Task<br>       |<br>       v<br>Persistence Database<br>       |<br>       | Commit state + task together<br>       |<br>       v<br>Background Processor<br>       |<br>       | Read Transfer Task<br>       |<br>       v<br>Matching Service<br>       |<br>       | Make Workflow Task available<br>       |<br>       v<br>Worker polls and executes</pre><p>The important detail is that Workflow state and the Transfer Task are committed together.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pHx0xMGtlvyxWSQmVEflyw.png" /></figure><p>A background processor eventually reads the pending Transfer Task and sends it to the Matching Service.</p><h3>What happens if History Service crashes?</h3><p>Let’s say the History Service successfully commits the database update but crashes before dispatching the task.</p><p>The database still contains the pending Transfer Task.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*T6CPYrWXD6muyAHZ2yPTbg.png" /></figure><p>After the service recovers, a background processor can read that task and dispatch it to the Matching Service.</p><p>The corresponding Workflow Task can then become available to a Worker.</p><p>This prevents the system from losing the scheduling operation simply because a service crashed between updating the database and communicating with another service.</p><p>Timers also use durable internal tasks. A Timer Task is stored with its deadline, and background processing handles it when it becomes due.</p><h3>9. How Temporal avoids replaying everything constantly</h3><p>Replay gives Temporal a powerful recovery mechanism.</p><p>But imagine a Workflow that has been running for six months and has thousands of events in its Event History.</p><p>Replaying the entire history from the beginning every time anything happens would be expensive.</p><p>Temporal uses an optimisation called <strong>Sticky Execution</strong>.</p><h3>Understanding Workflow Cache and Sticky Queue</h3><p>When a Worker receives a Workflow Task for the first time, it reconstructs the Workflow using its Event History.</p><p>The reconstructed Workflow state is then maintained in the Worker’s Workflow Cache.</p><p>When the same Workflow needs to run again, Temporal tries to route its next Workflow Task to the same Worker.</p><p>Since the Worker already has the reconstructed state in memory, it can avoid unnecessary replay from the beginning.</p><p>This routing optimisation is called Sticky Execution.</p><p>Under the hood, a Worker polls two types of queues:</p><ol><li>The normal Task Queue.</li><li>A Worker-specific Sticky Queue.</li></ol><p>Once a Worker has cached a Workflow, future Workflow Tasks can be routed through that Sticky Queue.</p><p>Consider this example:</p><pre>            Temporal<br>                |<br>          Sticky Queue<br>                |<br>                v<br>             Worker A<br>                |<br>                v<br>          Workflow Cache</pre><p>Worker A has already reconstructed the Workflow and has its state in memory.</p><p>The next Workflow Task can be routed back to Worker A rather than forcing another Worker to reconstruct the Workflow again.</p><h3>What happens if Worker A crashes?</h3><p>Sticky Execution is an optimisation, not a requirement for correctness.</p><p>Suppose Worker A has a Workflow in its cache and suddenly crashes.</p><p>Temporal cannot use that in-memory cache anymore.</p><p>So it falls back to the normal Task Queue.</p><p>Another Worker, Worker B, receives the task and reconstructs the Workflow by replaying the Event History.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bV9Gj9qkRLfPaeMHhhfmDQ.png" /></figure><p>Worker B does not have the cached state, so it rebuilds the execution from the durable Event History.</p><p>The Workflow can then continue from the previously recorded progress.</p><p>This gives us faster execution during normal operation without making correctness dependent on a particular Worker remaining alive.</p><h3>10. How Task Queues connect Temporal to Workers</h3><p>Now let’s understand how Temporal decides which Worker should execute a particular Activity.</p><p>The answer is <strong>Task Queues</strong>.</p><p>When building an application, we can define different Task Queue names.</p><p>For example:</p><pre>orders-workflow<br>payments<br>shipping<br>email</pre><p>We then configure Workers to poll the appropriate queues.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/957/1*l5fe09c7ZUgJUIIpAdcclg.png" /></figure><p>When the History Service needs a Workflow Task or Activity Task to be executed, it creates the necessary work.</p><p>The Matching Service looks for a Worker polling the corresponding Task Queue and makes the task available to it.</p><p>There are two important types of tasks.</p><p><strong>Workflow Task</strong></p><blockquote>A Workflow Task tells a Worker to execute Workflow code and determine what should happen next.</blockquote><p>For example, after the payment Activity completes, the Workflow may need to schedule the inventory reservation Activity.</p><p><strong>Activity Task</strong></p><blockquote>An Activity Task tells a Worker to perform an external operation, such as calling Stripe, updating inventory or sending an email.</blockquote><p>These tasks do not necessarily need to run on the same machines.</p><p>For example, payment Activities can run on a dedicated Worker fleet, while shipping Activities run on another fleet.</p><h3>What if no Worker is available?</h3><p>When a Worker is already polling, Matching can hand the task to it directly.</p><p>Otherwise, the task can be stored in the persistence database until a Worker becomes available.</p><p>Task Queues can also be partitioned across Matching Service instances to increase throughput.</p><p>This means a heavily used Task Queue does not need to depend on a single machine.</p><h3>11. How does the Temporal cluster scale?</h3><p>Now we can put the architecture together.</p><p>One important design decision in Temporal is that the different services can scale independently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iAnk1DJlwL3o8UtlRkxaHA.png" /></figure><p>The major components have different scaling responsibilities.</p><p><strong>Frontend Fleet</strong></p><p>Handles incoming client and Worker requests. Since the Frontend is stateless, we can add more instances independently.</p><p><strong>History Fleet</strong></p><p>Owns and processes History Shards. Adding or removing History Service instances changes shard ownership across the fleet.</p><p><strong>Matching Fleet</strong></p><p>Manages Task Queue partitions and coordinates work delivery to Workers. Task Queue partitions allow Matching to distribute queue processing across multiple instances.</p><p><strong>Application Workers</strong></p><p>Execute our actual Workflow and Activity code.</p><p>We can increase the number of Workers based on how much application work needs to be processed.</p><p>For example, suppose payment processing suddenly gets a lot of traffic.</p><p>We can increase the number of payment Activity Workers without necessarily increasing the number of shipping Workers.</p><p>Similarly, we can scale Temporal’s internal services based on their own workloads.</p><p>This separation is useful when building systems that handle a large number of Workflow Executions.</p><h3>12. When should you use Temporal?</h3><p>Temporal provides quite a lot of functionality, but that does not mean every background job should use it.</p><p>The main question is: does your process need durable orchestration?</p><p>Temporal is a good fit when you have a multi-step process that needs to survive failures, retries and long waiting periods.</p><p>A common sign that you might need Temporal is when you have started building a state machine yourself.</p><p>For example, you might already have:</p><ul><li>Database columns tracking which steps have completed.</li><li>Retry counters.</li><li>Scheduled jobs to resume unfinished work.</li><li>Logic to prevent already-completed steps from executing again.</li><li>Recovery logic when workers crash.</li><li>Webhooks to resume waiting processes.</li></ul><p>At some point, you are effectively building your own workflow engine.</p><h3>When Temporal makes sense</h3><p><strong>1. A process has several dependent steps</strong></p><p>Consider a YouTube video post-processing pipeline.</p><p>A video needs to be split into segments, multiple segments need to be transcoded in parallel, and the results need to be joined into the final output.</p><p>If one processing Worker disappears halfway through the pipeline, Temporal can reconstruct the Workflow and continue scheduling unfinished work.</p><p><strong>2. The process needs to stay open while waiting for something</strong></p><p>Consider an Uber-style ride-matching system.</p><p>The Workflow needs to find a driver, wait for the driver’s response, handle timeouts and potentially move to the next candidate.</p><p>This is not simply a single background job. It is a long-running process that reacts to external events.</p><p><strong>3. You need to recover from partial execution</strong></p><p>If a video-processing Worker disappears halfway through the processing DAG, Temporal can reconstruct the Workflow and continue scheduling unfinished work.</p><p><strong>4. Retries and recovery are becoming a meaningful part of your application logic</strong></p><p>If you are already maintaining execution state, retry counters, scheduled jobs and recovery mechanisms, it might be time to consider a workflow orchestration system.</p><h3>When should you avoid Temporal?</h3><p>Temporal is not a replacement for every messaging or background processing system.</p><p>There are cases where simpler tools are a better choice.</p><p><strong>Simple background jobs: Use SQS</strong></p><p>If you only need to queue a job and process it independently, a simple message queue such as Amazon SQS might be sufficient.</p><p>For a single independent background task, you may not need the additional orchestration capabilities of Temporal.</p><p><strong>Streaming workloads: Use Kafka</strong></p><p>If your primary requirement is handling continuous streams of events, Kafka is designed for that kind of workload.</p><p>Temporal is focused on durable Workflow execution, not stream processing.</p><p><strong>External side effects still need idempotency</strong></p><p>Temporal can retry Activities, but external systems still need to handle duplicate requests safely.</p><p><strong>Workflow code has constraints</strong></p><p>Because Temporal reconstructs Workflows through deterministic replay, you cannot treat Workflow code exactly like ordinary application code.</p><p>Operations involving changing external state, such as database queries or external API calls, generally belong inside Activities.</p><p>These constraints need to be understood before adopting Temporal.</p><h3>Conclusion</h3><p>The interesting thing about Temporal is that it changes how we think about long-running application processes.</p><p>Instead of building a system where a Worker must remain alive until a process is completed, we build a Workflow whose progress is recorded durably.</p><p>A new Worker can reconstruct that execution using Replay, while Activities handle external operations and Event History records what happened. Timers and Signals allow Workflows to wait for deadlines or external events without keeping a Worker occupied.</p><p>Behind the scenes, History Shards distribute Workflow ownership, Mutable State maintains the current execution state, and Transfer Tasks ensure that scheduling work is not lost when a service crashes. Sticky Execution avoids unnecessary replay during normal operation, while Task Queues connect the Temporal Server to application Workers.</p><p>The important distinction is that recovering a Workflow and reversing a business operation are two different things. Temporal can recover execution, but compensating operations such as refunding a payment or releasing inventory still need to be implemented by the application.</p><p>The real value of Temporal is not just that it can execute background jobs. It is that it can manage an entire business process, remember where it stopped, and continue from there even when the infrastructure running it does not survive.</p><p><em>If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, Python, and backend engineering!</em></p><h3>References</h3><ul><li><a href="https://www.hellointerview.com/learn/system-design/deep-dives/temporal">Temporal Deep Dive — Hello Interview</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7c36b50c165d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Database Partitioning in PostgreSQL: A Practical Guide to Managing Large Tables]]></title>
            <link>https://medium.com/@vjgupta78/database-partitioning-in-postgresql-a-practical-guide-to-managing-large-tables-735941c88c24?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/735941c88c24</guid>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[system-design-interview]]></category>
            <category><![CDATA[design-systems]]></category>
            <category><![CDATA[system-design-concepts]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Sat, 14 Nov 2026 09:29:12 GMT</pubDate>
            <atom:updated>2026-11-14T09:29:12.537Z</atom:updated>
            <content:encoded><![CDATA[<blockquote><em>Understanding partition keys, partition pruning, automatic record routing, and the complete lifecycle of partitions.</em></blockquote><p>Every application starts somewhere.</p><p>Initially, the database is small. Tables have a few thousand or maybe a few lakh records. Queries are reasonably fast, indexes are manageable, and database maintenance is not something the team worries about every day.</p><p>But applications grow. More users join, transactions increase, and tables that were once small start containing millions or even crores of records.</p><p>Let’s take an e-commerce application as an example.</p><p>Imagine we have an orders table that stores every order placed on the platform. As the business grows, this table keeps increasing in size. After a few years, it might contain hundreds of millions of records.</p><p>Now, some challenges start appearing:</p><ul><li>Queries involving historical orders become expensive.</li><li>Indexes become larger and require more maintenance.</li><li>Deleting old records becomes a costly operation.</li><li>Backup, restore, and data archival become more complicated.</li><li>Routine database maintenance starts taking longer.</li></ul><p>One approach to handling this growth is <strong>database partitioning</strong>.</p><p>Instead of keeping everything in one physical table, we divide the table into smaller pieces called partitions. Each partition stores a subset of the original table’s data, based on a rule that we define.</p><p>Let’s understand how this works in PostgreSQL and how we can implement it in a real application.</p><h3>1. What is Database Partitioning?</h3><p>Database partitioning is the process of dividing one large logical table into smaller physical tables, called partitions.</p><p>The important thing to understand is that these partitions still belong to the same logical table.</p><p>For example, suppose our application has an orders table. Instead of storing all the records in one physical structure, we can divide the data by month.</p><p><strong>A simple view of monthly partitioning:</strong></p><pre>                 orders<br>              (Parent Table)<br>                    |<br>          PARTITION BY RANGE<br>              (created_at)<br>                    |<br>       +------------+------------+<br>       |            |            |<br>       v            v            v<br>  orders_2026_08 orders_2026_09 orders_2026_10<br>    (August)     (September)     (October)<br>                                   |<br>                                   v<br>                              orders_2026_11<br>                                (November)</pre><p>Each partition contains only the records that belong to its defined date range.</p><p>From the application’s perspective, it is still working with a single table called orders.</p><p>We can execute a normal query:</p><pre>SELECT *<br>FROM orders<br>WHERE created_at &gt;= &#39;2026-11-01&#39;<br>  AND created_at &lt; &#39;2026-12-01&#39;;</pre><p>PostgreSQL identifies which partition contains the required records and can avoid scanning irrelevant partitions.</p><p>This optimisation is known as <strong>partition pruning</strong>.</p><p>That is one of the biggest advantages of partitioning. When queries are designed around the partition key, PostgreSQL can reduce the amount of data it needs to examine.</p><p>However, partitioning does not automatically make every query faster. The benefit depends on the query patterns and whether PostgreSQL can eliminate unnecessary partitions.</p><h3>2. How Do We Choose the Right Partition Key?</h3><p>This is probably the most important decision when designing a partitioned table.</p><p>A partition key determines how records are divided across partitions.</p><p>Choosing the wrong key can make partitioning ineffective.</p><p>Let’s go back to our e-commerce application.</p><p>Most orders-related queries might look something like this:</p><pre>SELECT *<br>FROM orders<br>WHERE created_at &gt;= &#39;2026-11-01&#39;<br>  AND created_at &lt; &#39;2026-12-01&#39;;</pre><p>We can see that the application frequently filters orders using created_at.</p><p>In this case, created_at is a good candidate for the partition key.</p><p>Why?</p><p>Because we can divide the orders table into monthly partitions. Whenever a query requests November orders, PostgreSQL can identify the November partition and avoid scanning unrelated monthly partitions.</p><p>When selecting a partition key, consider the following:</p><p><strong>Understand your query patterns</strong></p><p>Look at the most frequently executed queries. Which columns are commonly used in WHERE conditions?</p><p>A partition key should ideally align with how the application accesses its data.</p><p><strong>Look for natural ranges</strong></p><p>Some columns naturally divide data into ranges.</p><p>For example:</p><ul><li>created_at for transaction dates.</li><li>order_date for orders.</li><li>Numeric IDs for specific ID ranges.</li></ul><p><strong>Consider data volume</strong></p><p>The partitioning strategy should be based on how much data the application generates.</p><p>Monthly partitions might work well for one application, while another application generating millions of orders every day might need daily partitions.</p><p><strong>Choose the right granularity</strong></p><p>Avoid creating too many partitions unnecessarily.</p><p>For example, if monthly partitions contain a manageable amount of data, there is no reason to create daily partitions just for the sake of it.</p><p>The objective is to keep partitions manageable without introducing unnecessary database overhead.</p><h3>3. Types of Partitioning in PostgreSQL</h3><p>PostgreSQL supports three primary partitioning strategies.</p><h3>Range Partitioning</h3><p>Range partitioning divides records based on a range of values.</p><p>This is particularly useful for date and time columns.</p><p>For example, we can create monthly partitions for our orders table:</p><ul><li>August 2026</li><li>September 2026</li><li>October 2026</li><li>November 2026</li></ul><p>Each partition stores records within a defined range.</p><p>This is commonly used for orders, payments, transaction history, and application logs.</p><h3>List Partitioning</h3><p>List partitioning divides records based on specific values.</p><p>For example, suppose we have a customer table and want to partition records based on city.</p><p>We could create partitions for:</p><ul><li>Chennai</li><li>Bengaluru</li><li>Mumbai</li></ul><p>Whenever a record is inserted, PostgreSQL checks the partition key and routes it to the matching partition.</p><p>This approach is useful when the partition key contains a known set of distinct values.</p><h3>Hash Partitioning</h3><p>Sometimes, there is no natural range or fixed list that makes sense.</p><p>In such cases, hash partitioning can be useful.</p><p>PostgreSQL applies a hash function to the partition key and distributes records across a predefined number of partitions.</p><p>For example, we can use store_id to distribute records across four partitions.</p><p>This helps distribute records without manually defining individual ranges.</p><p>The choice depends on the nature of the data and how the application queries it.</p><h3>4. How Does Partition Pruning Work?</h3><p>Let’s understand this with a simple example.</p><p>Suppose our orders table has six monthly partitions.</p><pre>                  orders<br>               (Parent Table)<br>                     |<br>        PARTITION BY RANGE (created_at)<br>                     |<br>       +------+------+------+------+------+------+<br>       |      |      |      |      |      |<br>       v      v      v      v      v      v<br>      Aug    Sep    Oct    Nov    Dec    Jan<br>     2026   2026   2026   2026   2026   2027</pre><p>Now, suppose we execute the following query:</p><pre>SELECT *<br>FROM orders<br>WHERE created_at &gt;= &#39;2026-11-01&#39;<br>  AND created_at &lt; &#39;2026-12-01&#39;;</pre><p>PostgreSQL checks the partition boundaries and determines that only the November partition is relevant.</p><p>Conceptually, the execution looks like this:</p><pre>                +------------------+<br>                |     SQL Query    |<br>                +------------------+<br>                         |<br>                         v<br>                +------------------+<br>                | Partition Pruning|<br>                +------------------+<br>                         |<br>                         v<br>                Check partition key<br>                   (created_at)<br>                         |<br>                         v<br>              Evaluate all partition<br>                     ranges<br>                         |<br>       +--------+--------+--------+--------+--------+<br>       |        |        |        |        |        |<br>       v        v        v        v        v        v<br>    +------+ +------+ +------+ +------+ +------+ +------+<br>    | Aug  | | Sep  | | Oct  | | Nov  | | Dec  | | Jan  |<br>    +------+ +------+ +------+ +------+ +------+ +------+<br>    | SKIP | | SKIP | | SKIP | | SCAN | | SKIP | | SKIP |<br>    +------+ +------+ +------+ +------+ +------+ +------+<br>                                  |<br>                                  v<br>                         +----------------+<br>                         |  Return Rows   |<br>                         +----------------+</pre><p>Instead of scanning every partition, PostgreSQL can skip the partitions that cannot contain the requested data.</p><p>This is <strong>partition pruning.</strong></p><p>It is important to understand that PostgreSQL does this based on the partition boundaries and the query conditions. We do not need to manually mention the physical partition name in our SQL query.</p><p>For production verification, we can use EXPLAIN or EXPLAIN ANALYZE to see which partitions PostgreSQL actually scans.</p><h3>5. How Does PostgreSQL Route Records to the Correct Partition?</h3><p>One of the interesting things about PostgreSQL declarative partitioning is that the application does not need to decide which partition should receive a record.</p><p>PostgreSQL handles this automatically.</p><p>Let’s understand the flow.</p><p>Imagine our Spring Boot application inserts an order with the following value:</p><pre>created_at = &#39;2026-11-14&#39;</pre><p><strong>Insert routing flow:</strong></p><pre>+------------------+<br>|   Spring Boot    |<br>|   Application    |<br>+------------------+<br>         |<br>         | INSERT INTO orders<br>         | created_at = 2026-11-14<br>         v<br>+------------------+<br>| PostgreSQL       |<br>| Query Executor   |<br>+------------------+<br>         |<br>         | Check partition key<br>         | (created_at)<br>         v<br>+------------------+<br>|     Partition    |<br>|     Routing      |<br>+------------------+<br>         |<br>         | Match date range<br>         v<br>+------------------+<br>| orders_2026_11   |<br>| November         |<br>+------------------+<br>         |<br>         v<br>    Record Inserted</pre><p>The process is straightforward:</p><ol><li>The Spring Boot application executes an INSERT statement on the orders table.</li><li>The query reaches the PostgreSQL executor.</li><li>PostgreSQL checks the partition key, which is created_at.</li><li>It evaluates the partition boundaries.</li><li>The record is routed to the matching November partition.</li><li>The record is inserted into that partition.</li></ol><p>The application continues interacting with the parent table. It does not need to know the physical partition where the record is stored.</p><p>This is called <strong>partition routing</strong>.</p><p>It keeps the application code simple because the database handles the partition selection internally.</p><p>The same principle applies when reading data. PostgreSQL’s query planner can use partition pruning to eliminate partitions that cannot contain the requested records.</p><h3>6. Implementing Monthly Partitioning in PostgreSQL</h3><p>Now let’s implement this in our orders table.</p><p>We will use range partitioning based on created_at.</p><h3>Step 1: Create the parent table</h3><p>First, we create the main table.</p><pre>CREATE TABLE orders (<br>    order_id BIGINT,<br>    store_id BIGINT,<br>    created_at TIMESTAMPTZ NOT NULL,<br>    status TEXT,<br>    PRIMARY KEY (order_id, created_at)<br>)<br>PARTITION BY RANGE (created_at);</pre><p>There are two important things to notice here.</p><p>First, we are using created_at as our partition key.</p><p>Second, our primary key contains both order_id and created_at.</p><p>PostgreSQL requires a primary key or unique constraint on a partitioned table to include all the partition key columns.</p><h3>Step 2: Create a default partition</h3><p>We can also create a default partition.</p><pre>CREATE TABLE orders_default<br>PARTITION OF orders DEFAULT;</pre><p>This partition stores records that do not match any of the explicitly defined partitions.</p><p>For example, if an order arrives for a month for which we have not created a partition, PostgreSQL can route it to the default partition.</p><p>This provides a fallback mechanism, but we should not depend on it as a replacement for creating partitions ahead of time.</p><p>A large default partition can create operational challenges when we later need to create the missing monthly partition.</p><h3>Step 3: Create partitions ahead of time</h3><p>Since we have chosen monthly range partitioning, we should create future partitions before they are required.</p><p>For example, let’s create the December 2026 partition.</p><pre>CREATE TABLE orders_2026_12<br>PARTITION OF orders<br>FOR VALUES FROM (&#39;2026-12-01&#39;)<br>TO (&#39;2027-01-01&#39;);</pre><p>Here:</p><ul><li>FROM is the inclusive lower boundary.</li><li>TO is the exclusive upper boundary.</li></ul><p>So this partition stores orders created from December 1, 2026, up to but not including January 1, 2027.</p><p>We should ideally maintain the next two or three months’ partitions in advance.</p><p>This can be automated using a scheduled job or a tool such as pg_partman.</p><h3>Step 4: Create indexes</h3><p>Indexes are also important in partitioned tables.</p><p>For example, suppose our application frequently searches orders using store_id and created_at.</p><p>We can create an index on the parent table:</p><pre>CREATE INDEX idx_orders_store_created<br>ON orders (store_id, created_at);</pre><p>PostgreSQL creates corresponding indexes on the existing partitions and manages index creation for partitions added later.</p><p>Remember that partitioning and indexing solve different problems.</p><p>Partitioning helps PostgreSQL eliminate irrelevant partitions, while indexes help locate records efficiently within the partitions that need to be scanned.</p><p>Both can work together.</p><h3>Step 5: Insert records normally</h3><p>Now comes the interesting part.</p><p>We do not need to write separate INSERT statements for every month.</p><p>Our application can simply execute:</p><pre>INSERT INTO orders (<br>    order_id,<br>    store_id,<br>    created_at,<br>    status<br>)<br>VALUES (<br>    101,<br>    501,<br>    &#39;2026-11-14&#39;,<br>    &#39;PLACED&#39;<br>);</pre><p>PostgreSQL checks the value of created_at and automatically routes the record to the matching November partition.</p><p>The application continues using the same logical table throughout.</p><h3>7. Understanding the Complete Partition Lifecycle</h3><p>Creating partitions is only the beginning.</p><p>In a production application, we need to think about what happens to partitions as they become older.</p><p>For our orders table, the lifecycle might look like this:</p><pre>       +----------------+<br>       |   SETUP        |<br>       | Create parent  |<br>       | and partitions |<br>       +----------------+<br>                |<br>                v<br>       +----------------+<br>       |   CREATE       |<br>       | Future monthly |<br>       | partitions     |<br>       +----------------+<br>                |<br>                v<br>       +----------------+<br>       |   FILL         |<br>       | Insert orders  |<br>       | into partitions|<br>       +----------------+<br>                |<br>                v<br>       +----------------+<br>       |   AGE          |<br>       | Older data     |<br>       | becomes inactive|<br>       +----------------+<br>                |<br>                v<br>       +----------------+<br>       |   ARCHIVE      |<br>       | Detach and     |<br>       | preserve data  |<br>       +----------------+<br>                |<br>                v<br>       +----------------+<br>       |   DROP         |<br>       | Remove expired |<br>       | partition      |<br>       +----------------+</pre><p>Let’s understand each stage.</p><h3>Stage 1: Setup</h3><p>This is a one-time activity.</p><p>We create the parent table, define the partitioning strategy, create the initial partitions, and configure the required indexes.</p><p>Once this is done, the application can start writing data.</p><h3>Stage 2: Create partitions ahead of time</h3><p>We should not wait for the current partition to become full or for a new month to begin.</p><p>For monthly partitioning, a scheduled job can create the next two or three months’ partitions in advance.</p><p>For example:</p><pre>CREATE TABLE orders_2026_12<br>PARTITION OF orders<br>FOR VALUES FROM (&#39;2026-12-01&#39;)<br>TO (&#39;2027-01-01&#39;);</pre><p>This avoids situations where new records arrive but the required partition does not exist.</p><h3>Stage 3: Active partitions</h3><p>These are the partitions receiving most of the new records.</p><p>For example, the November partition is actively receiving orders throughout November.</p><p>Recent partitions are generally more frequently accessed, so they may need more attention from the maintenance perspective.</p><h3>Stage 4: Age out old partitions</h3><p>After 60–90 days, some partitions may become less active.</p><p>For example, our August partition may now contain mostly historical orders.</p><p>At this point, we can review its access patterns and maintenance requirements.</p><p>We can run:</p><pre>VACUUM ANALYZE orders_2026_08;</pre><p>This performs vacuuming and updates statistics.</p><p>PostgreSQL’s autovacuum can also handle routine maintenance, so manually running this command should depend on the actual maintenance requirements.</p><p>The important idea is to understand that older partitions often have a different access pattern from current partitions.</p><h3>Stage 5: Archive old partitions</h3><p>Eventually, the business may decide that older orders do not need to remain in the active table.</p><p>Instead of deleting millions of records individually, we can detach the entire partition.</p><p>For example:</p><pre>ALTER TABLE orders<br>DETACH PARTITION orders_2026_08 CONCURRENTLY;</pre><p>The CONCURRENTLY option allows detaching with reduced locking impact compared with a regular detach, subject to PostgreSQL&#39;s operational restrictions.</p><p>Once detached, the table is no longer part of the parent table.</p><p>We can then export it, move it to an archive database, or retain it separately based on our application’s data retention requirements.</p><h3>Stage 6: Drop old partitions</h3><p>After verifying that the archived data is safely available and the retention policy allows deletion, we can drop the old table.</p><pre>DROP TABLE orders_2026_08;</pre><p>This is one of the biggest benefits of partitioning.</p><p>Think about deleting millions of individual records using a DELETE statement. That can generate a significant amount of database work, including dead tuples and subsequent vacuum requirements.</p><p>Dropping an entire partition can be a much simpler and more efficient way to remove old data.</p><p>Of course, we should always ensure that the required backups and archives are available before dropping anything.</p><h3>8. Automating Partition Management</h3><p>In a real production application, manually creating partitions every month is not a good idea.</p><p>Someone eventually forgets to create the next partition, and the application starts facing insertion failures when no matching partition exists.</p><p>We should automate the complete lifecycle.</p><p>Tools such as pg_partman can help manage recurring partition creation and maintenance.</p><p>Alternatively, we can implement scheduled jobs using cron or another job scheduler.</p><p>For our orders table, the automation could handle:</p><ul><li>Creating partitions for the next two or three months.</li><li>Checking whether expected partitions exist.</li><li>Monitoring the size and growth of individual partitions.</li><li>Identifying partitions that are older than the retention period.</li><li>Archiving and detaching old partitions.</li><li>Dropping partitions after the retention period expires.</li></ul><p>The objective is to make partition management a predictable database operation rather than a manual activity.</p><h3>9. When Does Partitioning Actually Make Sense?</h3><p>Partitioning is useful, but it is not something we should introduce into every database table.</p><p>Before implementing it, we should understand the actual problem.</p><p>For example, if our application has a customer table containing only a few lakh records, partitioning might not provide any meaningful benefit.</p><p>But an orders table containing hundreds of millions of records, with frequent date-based queries and a need for historical data retention, could be a good candidate.</p><p>Partitioning is particularly useful when:</p><ul><li>A table contains a very large volume of data.</li><li>Queries frequently filter on a particular column.</li><li>Historical data needs to be archived or removed regularly.</li><li>Maintenance operations on the complete table are becoming expensive.</li><li>The workload naturally separates into ranges or categories.</li></ul><p>There are also a few things to keep in mind.</p><p>Partitioning does not automatically improve every query. Queries that do not use the partition key may still need to scan multiple partitions.</p><p>Similarly, creating too many partitions can increase planning and maintenance overhead.</p><p>So, the right approach is to start with the application’s query patterns, understand the data growth, and then choose an appropriate partitioning strategy.</p><h3>Final Thoughts</h3><p>Database partitioning is more than just dividing a large table into smaller tables.</p><p>It is about designing our data in a way that makes it easier to query, maintain, and manage as the application grows.</p><p>The most important decision is choosing the right partition key. A good partition key should align with the application’s query patterns and the natural growth of its data.</p><p>But the job does not end after creating partitions. We also need to think about the complete lifecycle: creating future partitions, routing new records, maintaining older partitions, archiving historical data, and eventually dropping partitions.</p><p>For me, the biggest takeaway is this:</p><blockquote><strong>A good partitioning strategy is not just about how we store today’s data. It is also about how we plan to manage tomorrow’s data and the data that our application no longer needs in its active tables.</strong></blockquote><p><em>If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, Python, and backend engineering!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=735941c88c24" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Production-Grade SQL Agent with LangGraph, PostgreSQL, and Query Guardrails ️]]></title>
            <link>https://medium.com/@vjgupta78/building-a-production-grade-sql-agent-with-langgraph-postgresql-and-query-guardrails-%EF%B8%8F-0da3a36364fc?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/0da3a36364fc</guid>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[agentic-ai]]></category>
            <category><![CDATA[llm]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Sun, 20 Sep 2026 19:06:01 GMT</pubDate>
            <atom:updated>2026-09-20T19:06:01.424Z</atom:updated>
            <content:encoded><![CDATA[<p>Giving an LLM direct access to your database feels like magic in a weekend demo. A business user types: <em>“Show me the top 5 customers by revenue in NewYork this quarter,”</em> the model generates clean SQL, runs it, and shows the answer. Everyone in the room is impressed.</p><p>Then you try to take it to production.</p><p>And that’s where things get interesting — not in a good way.</p><p>What happens when an engineer accidentally types: <em>“Clear out dummy users from yesterday”</em>? Or when a user asks a complex aggregation that joins 12 unindexed tables with no LIMIT, effectively locking your live database and spiking CPU to 100%?</p><p>If you’re passing raw text to SQL and running it directly — no checks, no guardrails — you’re one bad prompt away from a disaster. I’ve seen this happen. It’s not pretty.</p><p>Building a SQL Agent that’s actually ready for production is not just about writing good prompts. It’s about <strong>state management, tool safety, query validation at the AST level, and human-in-the-loop checkpoints</strong> where it matters.</p><p>Let’s build this properly using <strong>LangChain, LangGraph, and PostgreSQL/MySQL</strong>.</p><h3>The Architecture: Why a Simple Chain Isn’t Enough</h3><p>A naive SQL agent is just a straight line: Query → Prompt → SQL → DB → Output. If anything breaks — wrong table name, bad syntax, a hallucinated column — the whole thing crashes.</p><p>To make this reliable, we build an <strong>agentic state machine using LangGraph</strong>. Think of it as a flowchart with real safety checks built in before any query reaches your database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*uPdGNPN6JATf78a5V5CenQ.png" /></figure><p>The top half shows the fragile naive pipeline — user input goes straight to the LLM and directly executes on your live database, ending in a shattered production database. The bottom half shows the resilient LangGraph pipeline with schema inspection, proper AST query validation, and an automated self-correction loop.</p><h3>Step 1: The Three Ground Rules Before You Write Any Code</h3><p>Before even opening your IDE, set these three rules in stone:</p><p><strong>1. Least Privilege Database Role</strong> The agent should log in using a dedicated DB user that has <em>only</em> SELECT permissions. No INSERT, UPDATE, DELETE, DROP, or ALTER. Period. If your agent doesn&#39;t need to write, don&#39;t give it write access. Simple.</p><p><strong>2. Statement Timeouts</strong> In PostgreSQL, you can add SET statement_timeout = &#39;5000ms&#39;; at the session level. This kills any query that takes more than 5 seconds. Without this, one poorly written JOIN can hold your connection pool hostage and bring down your application.</p><p><strong>3. AST Validation — Don’t Trust LLM SQL Blindly</strong> System prompts like <em>“Only generate SELECT queries”</em> work… until they don’t. LLMs will eventually break that rule, especially on complex inputs. The right approach is to parse the generated SQL using an AST (Abstract Syntax Tree) parser and programmatically verify it before running anything. We’ll use sqlglot for this.</p><h3>Step 2: Validating Queries with sqlglot</h3><p>Here’s a standalone validator function that catches dangerous queries before they reach your database:</p><pre>import sqlglot<br>from sqlglot import exp<br><br>FORBIDDEN_EXPRESSIONS = (<br>    exp.Insert,<br>    exp.Update,<br>    exp.Delete,<br>    exp.Drop,<br>    exp.Alter,<br>    exp.TruncateTable,<br>    exp.Command, # Blocks raw commands like COPY<br>)<br><br>FORBIDDEN_SCHEMAS = (&quot;information_schema&quot;, &quot;pg_catalog&quot;)<br>FORBIDDEN_TABLES = (&quot;pg_shadow&quot;, &quot;pg_authid&quot;, &quot;pg_user&quot;)<br><br>def validate_sql_query(query: str, max_limit: int = 100) -&gt; str:<br>    &quot;&quot;&quot;<br>    Parses and sanitizes SQL for production safety.<br>    &quot;&quot;&quot;<br>    query = query.strip()<br>    if not query:<br>        raise ValueError(&quot;SQL query cannot be empty.&quot;)<br><br>    # 1. Parse ALL statements (do not use parse_one!)<br>    try:<br>        statements = sqlglot.parse(query, read=&quot;postgres&quot;)<br>    except Exception as e:<br>        raise ValueError(f&quot;SQL Syntax Error: {e}&quot;)<br><br>    # 2. Prevent multi-statement injection chains<br>    if len(statements) != 1:<br>        raise ValueError(&quot;Multi-statement queries are strictly prohibited.&quot;)<br><br>    parsed = statements[0]<br><br>    # 3. Whitelist approach: Only SELECT is allowed<br>    if not isinstance(parsed, exp.Select):<br>        raise PermissionError(&quot;Security violation: only SELECT queries are allowed.&quot;)<br><br>    # 4. Blacklist fallback for nested destructive operations<br>    if parsed.find(*FORBIDDEN_EXPRESSIONS):<br>        raise PermissionError(&quot;Security violation: DDL/DML statements are blocked.&quot;)<br><br>    # 5. Block system table snooping<br>    for table in parsed.find_all(exp.Table):<br>        if table.name.lower() in FORBIDDEN_TABLES:<br>            raise PermissionError(&quot;Security violation: Access to system tables is restricted.&quot;)<br>        if table.db and table.db.lower() in FORBIDDEN_SCHEMAS:<br>            raise PermissionError(&quot;Security violation: Access to system schemas is restricted.&quot;)<br><br>    # 6. Enforce safe result pagination to prevent memory exhaustion<br>    if not parsed.find(exp.Limit):<br>        parsed = parsed.limit(max_limit)<br><br>    return parsed.sql(dialect=&quot;postgres&quot;)</pre><p>This is small, readable, and does three important things: blocks SQL injection chains, blocks write operations, and adds a safety LIMIT if the LLM forgot to include one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*9JXWY6rSm6tUsshsoWQH0A.png" /></figure><p>A raw SQL string from the LLM enters the AST parser. Two separate checks run — one for dangerous operations, another for missing pagination. The output is a safe, paginated query.</p><h3>Step 3: Wiring Everything Together with LangGraph</h3><p>Now let’s build the actual state machine. The key design decision here: if the query fails validation or hits a DB error, we don’t just crash. We capture the error, send it back to the SQL generator, and let the model try again — up to 3 times.</p><pre>import os<br>from typing import TypedDict, Optional<br>from dotenv import load_dotenv<br>from langgraph.graph import StateGraph, END<br>from langchain_openai import ChatOpenAI<br>from langchain_community.utilities import SQLDatabase<br><br>load_dotenv()<br><br># Initialize DB with connection-level safeguards (5-second timeout)<br>db = SQLDatabase.from_uri(<br>    os.getenv(&quot;READONLY_DB_URI&quot;),<br>    engine_args={&quot;connect_args&quot;: {&quot;options&quot;: &quot;-c statement_timeout=5000&quot;}}<br>)<br><br>llm = ChatOpenAI(model=&quot;gpt-4o&quot;, temperature=0)<br><br>class AgentState(TypedDict):<br>    question: str<br>    schema: str<br>    generated_sql: Optional[str]<br>    query_result: Optional[str]<br>    error: Optional[str]<br>    attempts: int<br><br>def generate_sql_node(state: AgentState) -&gt; dict:<br>    prompt = f&quot;&quot;&quot;<br>    Given the schema:<br>    {state[&#39;schema&#39;]}<br>    <br>    Convert this question into a PostgreSQL query:<br>    {state[&#39;question&#39;]}<br>    <br>    Previous Error: {state.get(&#39;error&#39;, &#39;None&#39;)}<br>    Return ONLY valid SQL. Do not wrap in backticks.<br>    &quot;&quot;&quot;<br>    response = llm.invoke(prompt)<br>    return {<br>        &quot;generated_sql&quot;: response.content.strip(),<br>        &quot;attempts&quot;: state.get(&quot;attempts&quot;, 0) + 1<br>    }<br><br>def execute_sql_node(state: AgentState) -&gt; dict:<br>    raw_query = state[&quot;generated_sql&quot;]<br>    try:<br>        # Pass through the strict AST gatekeeper<br>        safe_query = validate_sql_query(raw_query) <br>        result = db.run(safe_query)<br>        return {&quot;query_result&quot;: str(result), &quot;error&quot;: None}<br>    except Exception as err:<br>        return {&quot;error&quot;: str(err), &quot;query_result&quot;: None}<br><br>def validation_router(state: AgentState) -&gt; str:<br>    if state[&quot;error&quot;]:<br>        if state[&quot;attempts&quot;] &gt;= 3:<br>            return &quot;synthesize_node&quot;  # Graceful exit after 3 failed attempts<br>        return &quot;generate_sql_node&quot;    # Trigger LLM self-correction loop!<br>    return &quot;synthesize_node&quot;<br><br>def synthesize_node(state: AgentState) -&gt; dict:<br>    if state.get(&quot;error&quot;):<br>        ans = f&quot;Sorry, I couldn&#39;t run this query safely. Error: {state[&#39;error&#39;]}&quot;<br>    else:<br>        prompt = f&quot;Question: {state[&#39;question&#39;]}\nResult: {state[&#39;query_result&#39;]}\nProvide a concise answer:&quot;<br>        ans = llm.invoke(prompt).content<br>    return {&quot;query_result&quot;: ans}<br><br># Build the Graph<br>workflow = StateGraph(AgentState)<br>workflow.add_node(&quot;generate_sql_node&quot;, generate_sql_node)<br>workflow.add_node(&quot;execute_sql_node&quot;, execute_sql_node)<br>workflow.add_node(&quot;synthesize_node&quot;, synthesize_node)<br><br>workflow.set_entry_point(&quot;generate_sql_node&quot;)<br>workflow.add_edge(&quot;generate_sql_node&quot;, &quot;execute_sql_node&quot;)<br>workflow.add_conditional_edges(<br>    &quot;execute_sql_node&quot;,<br>    validation_router,<br>    {<br>        &quot;generate_sql_node&quot;: &quot;generate_sql_node&quot;,<br>        &quot;synthesize_node&quot;: &quot;synthesize_node&quot;<br>    }<br>)<br>workflow.add_edge(&quot;synthesize_node&quot;, END)<br><br>sql_agent = workflow.compile()</pre><h3>Step 4: Adding Human-in-the-Loop for Sensitive Tables</h3><p>Some databases have tables you really don’t want a model touching automatically — think salary, pan_number, or credit_limit. For these, LangGraph lets you add a <strong>breakpoint</strong> that pauses execution and waits for a human supervisor to approve before proceeding.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uTJxf7r035nO7PVN_mDcyQ.png" /></figure><p>When a query targets sensitive columns or tables, it triggers an execution pause. An admin inspects the actual SQL string generated by the LLM on a dashboard, hits approve, and LangGraph resumes safely from its state checkpoint.</p><p>This is especially crucial in finance and HR systems where data access isn’t just a technical concern — it’s a compliance necessity.</p><h3>Things to Keep in Mind for Production</h3><p>A few things I’d strongly recommend once you’re past the prototype stage:</p><ul><li><strong>Don’t dump your entire database schema into the prompt.</strong> If your DB has 200 tables and you send all of them to the LLM, you’re burning tokens and confusing the model. Use vector search or simple routing to pass only the 4–5 tables relevant to the question.</li><li><strong>Keep credentials out of the model service.</strong> The SQL generation service and the SQL execution service should be separate. Your model should never directly hold a database connection string. Separate the two with network policies.</li><li><strong>Treat natural language input like untrusted user input from a web form.</strong> This mindset shift is important. It means: restrict the DB user’s permissions, always validate query ASTs, and let LangGraph manage self-correction so your users get graceful error messages instead of stack traces.</li></ul><p><em>If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, Python, and backend engineering!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0da3a36364fc" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Beyond Basic RAG: Supercharge Retrieval Quality with Hybrid Search, pgvector, BM25, and Reranking…]]></title>
            <link>https://medium.com/@vjgupta78/beyond-basic-rag-supercharge-retrieval-quality-with-hybrid-search-pgvector-bm25-and-reranking-5af4fa9296c0?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/5af4fa9296c0</guid>
            <category><![CDATA[rags]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Wed, 16 Sep 2026 19:39:34 GMT</pubDate>
            <atom:updated>2026-09-16T19:39:34.715Z</atom:updated>
            <content:encoded><![CDATA[<h3>Beyond Basic RAG: Supercharge Retrieval Quality with Hybrid Search, pgvector, BM25, and Reranking 🚀</h3><p>If you have spent any time in the Generative AI space recently, you already know about RAG (Retrieval-Augmented Generation). It’s the go-to solution for making LLMs chat with your private data.</p><p>But, let me tell you, the real challenge starts when you move from a sweet little POC (Proof of Concept) to actual production.</p><p>A few months back, I was building a standard RAG pipeline using a basic vector search. It worked like a charm during the initial demos. But the moment we pushed it closer to production and real users started firing real-world queries, the feedback was brutal:</p><blockquote><em>“It’s giving me general information about pumps, but I asked specifically about the maintenance schedule for the ‘XYZ-200’ model!”</em></blockquote><p>And:</p><blockquote><em>“It keeps missing the crucial troubleshooting steps mentioned in the manual, even when I search for the exact error code E-404!”</em></blockquote><p>This is the classic failure mode of pure vector-based retrieval. I was scratching my head, wondering how to fix this retrieval quality issue without overcomplicating the backend architecture. That’s when I was introduced to the holy trinity of advanced RAG: <strong>Hybrid Retrieval (Vector + Keyword) + Reranking</strong>.</p><p>In this article, let’s dive into how we can club these technologies together using <strong>LangChain, PostgreSQL (pgvector), BM25, and a Reranker</strong> to build a robust, production-ready RAG system.</p><h3>The Problem: Why Vector Search Isn’t Enough 🤷‍♂️</h3><p>Let’s get one thing straight — Vector embeddings are pure magic. When you search for “automobile”, a vector database knows you are also talking about “cars” and “vehicles” because it maps semantic meaning into a high-dimensional space.</p><p>However, vector search works by mapping semantic meaning. It struggles with exact keyword matching. When your user searches for a specific ID like TX-99402, or an obscure error code like E-404, a pure vector search might bring up documents containing TX-99401 or general troubleshooting advice just because they are semantically &quot;close.&quot; It is just too <em>smart</em> to notice the exact sequence of letters and numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-b_LngwQXbLrjev5udzZ3g.png" /></figure><h3>Enter Hybrid Retrieval: The Jugaad We All Need 🤝</h3><p>To fix our retrieval quality, we need a classic engineering <em>jugaad</em>. We need the best of both worlds by running two different search methods in parallel:</p><ol><li><strong>Dense Retrieval (Vector Search):</strong> To capture the context and semantics. For this, instead of spinning up a costly, dedicated vector database, I highly recommend using <strong>PostgreSQL with the </strong>pgvector<strong> extension</strong>. Most enterprise applications are already running on Postgres. Adding a vector column keeps your data stack clean and your DevOps team very happy.</li><li><strong>Sparse Retrieval (Keyword Search):</strong> To capture exact matches, acronyms, and specific domain jargon. The go-to algorithm here is the old-school but highly effective <strong>BM25</strong>.</li></ol><p>Think of it this way: Vector search finds the <em>right neighborhood</em>, while BM25 finds the <em>exact house number</em>. We combine the top results from both methods.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9lTrVhYC8ycH0eKbhBWawQ.png" /></figure><h3>The Secret Sauce: The Reranker 🌶️</h3><p>Implementing hybrid search is a massive upgrade, but we immediately hit a roadblock.</p><p>When you combine the results from pgvector and BM25, you now have <em>two different sets</em> of documents. We can’t just blindly mix them because BM25 scores (based on word frequency) and Vector similarity scores (cosine distance) are on completely different mathematical scales. A semantic score of 0.82 is not necessarily &quot;better&quot; than a BM25 score of 14.5. You cannot just merge and sort them blindly.</p><p>This is where the <strong>Reranker</strong> comes into the picture. A reranker is a specialized machine learning model (often a Cross-Encoder). Instead of just looking at abstract embeddings, a reranker takes the user’s query and the retrieved document <em>together</em> and scores how relevant they are to each other.</p><p>Instead of deciding which <em>set</em> is better, we take the top 10 candidates from pgvector and the top 10 from BM25, pool all 20 together, and pass them through the Reranker. This model reads the actual text and acts like a strict editor, meticulously re-ordering the documents from most relevant to least relevant. We pick the top 3 results from this refined list and <em>those</em> are what we feed to the LLM.</p><p><em>A quick note for my fellow backend engineers:</em> Cross-encoders are computationally heavy. Running 20 documents through a reranker will add latency to your API response. If you are building a streaming chat API (like with FastAPI), you need to monitor this step closely. The trade-off in response time is almost always worth the massive bump in accuracy, but keep an eye on your latency tracking!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D3ZBJ1duFPcrVfAhnfqo-g.png" /></figure><h3>Conceptual Implementation Guide with LangChain</h3><p>When building this out in Python, LangChain’s abstraction layers make wiring this micro-architecture up fairly straightforward.</p><p><strong>1. Document Ingestion Phase</strong></p><pre>import pickle<br>from langchain_community.document_loaders import TextLoader<br>from langchain_text_splitters import RecursiveCharacterTextSplitter<br>from langchain_openai import OpenAIEmbeddings<br>from langchain_postgres.vectorstores import PGVector<br>from langchain_community.retrievers import BM25Retriever<br><br>CONNECTION_STRING = &quot;postgresql+psycopg://user:password@localhost:5432/mydb&quot;<br>COLLECTION_NAME = &quot;hybrid_docs&quot;<br><br>def ingest_documents(file_path: str):<br>    # 1. Load and chunk the document<br>    loader = TextLoader(file_path)<br>    docs = loader.load()<br>    <br>    text_splitter = RecursiveCharacterTextSplitter(chunk_size=1000, chunk_overlap=100)<br>    chunks = text_splitter.split_documents(docs)<br>    <br>    # 2. Store dense embeddings in pgvector<br>    embeddings = OpenAIEmbeddings(model=&quot;text-embedding-3-small&quot;)<br>    PGVector.from_documents(<br>        embedding=embeddings,<br>        documents=chunks,<br>        collection_name=COLLECTION_NAME,<br>        connection=CONNECTION_STRING,<br>    )<br>    <br>    # 3. Fit and save the BM25 sparse retriever<br>    bm25_retriever = BM25Retriever.from_documents(chunks)<br>    with open(&quot;bm25_retriever.pkl&quot;, &quot;wb&quot;) as f:<br>        pickle.dump(bm25_retriever, f)<br>        <br>    print(f&quot;Successfully ingested {len(chunks)} chunks.&quot;)<br><br># Example usage:<br># ingest_documents(&quot;technical_manual.txt&quot;)</pre><p><strong>2. Hybrid Retrieval Phase</strong></p><pre>import pickle<br>from langchain_openai import OpenAIEmbeddings<br>from langchain_postgres.vectorstores import PGVector<br>from langchain.retrievers import EnsembleRetriever, ContextualCompressionRetriever<br>from langchain_cohere import CohereRerank<br><br>CONNECTION_STRING = &quot;postgresql+psycopg://user:password@localhost:5432/mydb&quot;<br>COLLECTION_NAME = &quot;hybrid_docs&quot;<br><br>def setup_hybrid_retriever():<br>    # 1. Initialize Vector Retriever<br>    embeddings = OpenAIEmbeddings(model=&quot;text-embedding-3-small&quot;)<br>    vectorstore = PGVector(<br>        connection=CONNECTION_STRING,<br>        embeddings=embeddings,<br>        collection_name=COLLECTION_NAME,<br>    )<br>    # Fetch top 10 semantic matches<br>    pgvector_retriever = vectorstore.as_retriever(search_kwargs={&quot;k&quot;: 10})<br>    <br>    # 2. Load Keyword Retriever (BM25)<br>    with open(&quot;bm25_retriever.pkl&quot;, &quot;rb&quot;) as f:<br>        bm25_retriever = pickle.load(f)<br>    # Fetch top 10 exact keyword matches<br>    bm25_retriever.k = 10<br>    <br>    # 3. Merge pools with EnsembleRetriever (50/50 weighting)<br>    hybrid_retriever = EnsembleRetriever(<br>        retrievers=[bm25_retriever, pgvector_retriever],<br>        weights=[0.5, 0.5]<br>    )<br>    <br>    # 4. Rerank the combined 20 chunks to output the absolute top 3<br>    reranker = CohereRerank(cohere_api_key=&quot;YOUR_COHERE_API_KEY&quot;, top_n=3)<br>    advanced_retriever = ContextualCompressionRetriever(<br>        base_compressor=reranker,<br>        base_retriever=hybrid_retriever<br>    )<br>    <br>    return advanced_retriever<br><br>def query_system(query: str):<br>    retriever = setup_hybrid_retriever()<br>    best_docs = retriever.invoke(query)<br>    <br>    for i, doc in enumerate(best_docs):<br>        print(f&quot;\n--- Result {i+1} ---&quot;)<br>        print(doc.page_content)<br><br># Example usage:<br># query_system(&quot;What is the warranty period for the TX-99402 sensor?&quot;)</pre><h3>Conclusion</h3><p>Building a RAG application is easy, but making it production-ready requires a bit of engineering. By implementing this pipeline, you are practically guaranteeing that your LLM gets the most accurate, context-rich, and keyword-precise data possible.</p><p>You cannot afford to settle for “okay” retrieval when your users are asking specific questions. Invest the time to build a hybrid pipeline. Your users will thank you.</p><p>Have you tried implementing Hybrid search in your RAG pipelines? I would love to hear your experiences. Drop a comment below, and let’s discuss!</p><p><em>If you found this article helpful, do leave a clap 👏 and follow for more insights on GenAI, Python, and backend engineering!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5af4fa9296c0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building an LLM CLI from Scratch in Python]]></title>
            <link>https://medium.com/@vjgupta78/building-a-terminal-first-llm-client-in-python-ed0e4b92b351?source=rss-b6c765222c1b------2</link>
            <guid isPermaLink="false">https://medium.com/p/ed0e4b92b351</guid>
            <category><![CDATA[llm-applications]]></category>
            <category><![CDATA[ai]]></category>
            <category><![CDATA[llm]]></category>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Vishwajeet Gupta]]></dc:creator>
            <pubDate>Wed, 09 Sep 2026 15:30:05 GMT</pubDate>
            <atom:updated>2026-09-09T16:01:56.551Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d_njN-WIKM7L-zTJOX6jzQ.jpeg" /></figure><blockquote>Streaming, multi-provider support, terminal UX, and the engineering beneath an AI tool.</blockquote><p>Before reaching for an agent framework, I wanted to understand what actually happens underneath an LLM-powered developer tool. Instead of building another web wrapper, I built a terminal-first client designed around a developer’s daily workflow.</p><p>The goal wasn’t just to query models, but to tackle the system design problems that framework abstractions hide: asynchronous streaming, cross-vendor normalization, terminal state management, and persistence.</p><h3><strong>Architecture and Structure</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NWBkLxUJGY1FZyaBAtbeFw.jpeg" /></figure><p>A robust CLI requires decoupling the interactive terminal loop from upstream vendor churn. The application separates concerns into explicit boundaries:</p><pre>chatbot/<br>├── config/         # Model registries, pricing tables, env parsing<br>├── engine/         # Session orchestrator &amp; slash command dispatcher<br>├── models/         # Pydantic v2 schemas (Message, Session, TokenUsage)<br>├── persistence/    # JSON/Markdown exporter and auto-save<br>├── providers/      # Concrete adapters (OpenAI, Anthropic, Ollama)<br>├── ui/             # Rich terminal themes, spinners, markdown panels<br>└── main.py         # Entry point &amp; CLI flags</pre><p>Using <strong>Pydantic v2</strong> for the domain layer isolates the application from external schema drift. Internally, conversations exist as strongly typed Message objects. Serializing sessions or restoring context across terminal reboots is handled via native .model_dump_json() and .model_validate_json(), eliminating custom serialization glue.</p><h3>The Provider Abstraction</h3><p>Every LLM vendor handles streaming differently. OpenAI uses server-sent events, Anthropic implements custom streaming events, and Ollama serves newline-delimited JSON over plain HTTP.</p><p>If any vendor SDK touches the engine or UI, changing models becomes an untangling nightmare. To fix this, all adapters implement an asynchronous generator interface:</p><pre># chatbot/providers/base.py<br>from abc import ABC, abstractmethod<br>from typing import AsyncIterator<br>from chatbot.models.message import Message<br>from chatbot.models.token_usage import TokenUsage<br><br>class BaseLLMProvider(ABC):<br>    @property<br>    @abstractmethod<br>    def name(self) -&gt; str:<br>        &quot;&quot;&quot;Provider name: &#39;openai&#39;, &#39;anthropic&#39;, or &#39;ollama&#39;&quot;&quot;&quot;<br>        ...<br><br>    @abstractmethod<br>    async def stream_chat(<br>        self,<br>        messages: list[Message],<br>        model: str,<br>        system_prompt: str | None = None,<br>    ) -&gt; AsyncIterator[tuple[str, TokenUsage | None]]:<br>        &quot;&quot;&quot;Yields: (token_text, optional_token_usage)&quot;&quot;&quot;<br>        ...</pre><p>The contract is strict: yield raw text chunks sequentially as they arrive, and pass back token usage metadata on the final frame. The chat engine never knows — or cares — which vendor generated the text.</p><h3>Streaming Ollama via HTTP</h3><p>For local inference, a heavy SDK is unnecessary. Standard httpx streaming against the /api/chat endpoint handles the payload:</p><pre># chatbot/providers/ollama_provider.py<br>import json<br>import httpx<br>from typing import AsyncIterator<br>from chatbot.models.message import Message<br>from chatbot.models.token_usage import TokenUsage<br>from chatbot.providers.base import BaseLLMProvider<br><br>class OllamaProvider(BaseLLMProvider):<br>    def __init__(self, base_url: str = &quot;http://localhost:11434&quot;):<br>        self.base_url = base_url.rstrip(&quot;/&quot;)<br><br>    @property<br>    def name(self) -&gt; str:<br>        return &quot;ollama&quot;<br><br>    async def stream_chat(<br>        self,<br>        messages: list[Message],<br>        model: str,<br>        system_prompt: str | None = None,<br>    ) -&gt; AsyncIterator[tuple[str, TokenUsage | None]]:<br>        payload_messages = [{&quot;role&quot;: m.role, &quot;content&quot;: m.content} for m in messages]<br>        if system_prompt:<br>            payload_messages.insert(0, {&quot;role&quot;: &quot;system&quot;, &quot;content&quot;: system_prompt})<br><br>        payload = {<br>            &quot;model&quot;: model,<br>            &quot;messages&quot;: payload_messages,<br>            &quot;stream&quot;: True,<br>        }<br><br>        async with httpx.AsyncClient(timeout=60.0) as client:<br>            async with client.stream(&quot;POST&quot;, f&quot;{self.base_url}/api/chat&quot;, json=payload) as response:<br>                response.raise_for_status()<br>                async for line in response.aiter_lines():<br>                    if not line:<br>                        continue<br>                    data = json.loads(line)<br>                    chunk = data.get(&quot;message&quot;, {}).get(&quot;content&quot;, &quot;&quot;)<br><br>                    if data.get(&quot;done&quot;, False):<br>                        usage = TokenUsage(<br>                            prompt_tokens=data.get(&quot;prompt_eval_count&quot;, 0),<br>                            completion_tokens=data.get(&quot;eval_count&quot;, 0),<br>                        )<br>                        yield chunk, usage<br>                    else:<br>                        yield chunk, None</pre><p>Switching between remote APIs and local models requires only swapping the provider instance in the engine.</p><h3>Terminal UX Challenges</h3><h3>1. Handling Time to First Token (TTFT)</h3><p>Models introduce a 500ms–2000ms delay before returning the first token. Overlaying a standard terminal spinner causes artifacts: once streaming starts, incoming text clashes with spinner escape sequences, corrupting the terminal scrollback.</p><p>Using <strong>Rich’s</strong> Live display with transient=True isolates the spinner:</p><pre># chatbot/ui/terminal.py<br>import sys<br>from rich.console import Console<br>from rich.live import Live<br>from rich.spinner import Spinner<br><br>console = Console()<br><br>async def stream_response(token_stream):<br>    first_token = True<br>    collected = []<br><br>    console.print(&quot;\n[bold cyan]Assistant:[/bold cyan]&quot;)<br><br>    with Live(Spinner(&quot;dots&quot;, text=&quot;[dim]Thinking...[/dim]&quot;), console=console, transient=True) as spinner:<br>        async for token, usage in token_stream:<br>            if first_token:<br>                spinner.stop()  # Clears the line completely<br>                first_token = False<br><br>            if token:<br>                sys.stdout.write(token)<br>                sys.stdout.flush()<br>                collected.append(token)<br><br>    print()<br>    return &quot;&quot;.join(collected), usage</pre><p>When the first token arrives, spinner.stop() wipes the line before standard output streaming begins.</p><h3>2. Multi-Line Code Input</h3><p>Python’s standard input() interprets newlines as submissions, making multi-line code pastes fail immediately. Replacing it with prompt_toolkit provides dedicated multi-line entry, keybindings, and history tracking:</p><pre>from prompt_toolkit import PromptSession<br>from prompt_toolkit.history import InMemoryHistory<br><br>class InputManager:<br>    def __init__(self):<br>        self.session = PromptSession(history=InMemoryHistory())<br>        self.multiline = False<br><br>    def prompt(self) -&gt; str:<br>        prompt_text = &quot;&gt;&gt;&gt; &quot; if not self.multiline else &quot;[multi] &gt;&gt;&gt; (Esc+Enter to submit)\n&quot;<br>        return self.session.prompt(prompt_text, multiline=self.multiline)</pre><p>Large blocks of code or migrations can be pasted, modified inline, and dispatched via Esc + Enter.</p><h3>3. Usage and Cost Metrics</h3><p>To track token usage across long contexts, the client maintains a pricing registry and evaluates costs per turn:</p><pre>PRICING = {<br>    &quot;gpt-4o&quot;: {&quot;input_per_m&quot;: 2.50, &quot;output_per_m&quot;: 10.00},<br>    &quot;claude-3-5-sonnet-20241022&quot;: {&quot;input_per_m&quot;: 3.00, &quot;output_per_m&quot;: 15.00},<br>    &quot;llama3.2&quot;: {&quot;input_per_m&quot;: 0.00, &quot;output_per_m&quot;: 0.00},<br>}<br><br>def calculate_cost(model: str, prompt_tokens: int, completion_tokens: int) -&gt; float:<br>    rates = PRICING.get(model, {&quot;input_per_m&quot;: 0.0, &quot;output_per_m&quot;: 0.0})<br>    input_cost = (prompt_tokens / 1_000_000) * rates[&quot;input_per_m&quot;]<br>    output_cost = (completion_tokens / 1_000_000) * rates[&quot;output_per_m&quot;]<br>    return input_cost + output_cost</pre><p>Executing /tokens renders active and cumulative metrics:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hjmzgkqIMI5O26P2b-cPjw.png" /></figure><h3>In-Session Commands</h3><p>To modify session state without exiting the REPL, commands are handled directly by the dispatcher:</p><ul><li><strong>/provider &lt;name&gt;</strong>: Switch providers (anthropic, openai, ollama).</li><li><strong>/model &lt;name&gt;</strong>: Change model checkpoints without dropping session memory.</li><li><strong>/system &lt;prompt&gt;</strong>: Update runtime system instructions.</li><li><strong>/save &lt;filename.md&gt;</strong>: Export conversation history to Markdown on disk.</li><li><strong>/clear</strong>: Reset the context buffer.</li></ul><h3>Key Takeaways</h3><p>Building the client directly against vendor APIs exposed several engineering fundamentals:</p><ul><li><strong>Streaming shifts application control flow:</strong> A streaming API cannot be treated as a regular request that arrives in small pieces. It alters error-handling boundaries, terminal I/O states, and token-accounting lifecycles.</li><li><strong>Abstractions earn their keep at vendor boundaries:</strong> A single interface only makes sense if it isolates genuine differences in protocol, authentication, and streaming schemas.</li><li><strong>The scaffolding matters more than the LLM call:</strong> The actual API invocation is trivial. The durable engineering work lies in state persistence, concurrency, and predictable terminal ergonomics.</li></ul><p><strong>Code &amp; Setup</strong></p><p>The complete code, setup instructions, and configuration files are open source on GitHub: <a href="https://github.com/RatFire22/ai-projects/tree/main/01%20llm-cli-chatbot">llm-cli-chatbot</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ed0e4b92b351" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>