Low Level Design: Bookmark Service – Tech Interview Dot Org

Bookmark Service: Core Schema

The bookmark service persists saved URLs with rich metadata and organizational structure.

Bookmark Table

id            BIGSERIAL PRIMARY KEY
user_id       BIGINT NOT NULL
url           TEXT NOT NULL
title         TEXT
description   TEXT
favicon_url   TEXT
tags          JSONB          -- array of tag strings
collection_id BIGINT NULL    -- FK to Collection
is_public     BOOL DEFAULT false
created_at    TIMESTAMPTZ DEFAULT now()

UNIQUE (user_id, url)        -- per-user URL deduplication

Collection Table

id         BIGSERIAL PRIMARY KEY
user_id    BIGINT NOT NULL
name       TEXT NOT NULL
is_public  BOOL DEFAULT false
created_at TIMESTAMPTZ DEFAULT now()

Indexing Strategy

Tag Index

Tags are stored as a JSONB array. A GIN index enables efficient tag-based filtering without normalizing tags into a separate table.

CREATE INDEX idx_bookmarks_tags ON bookmarks USING GIN (tags);
-- Query: SELECT * FROM bookmarks WHERE tags @> '["javascript"]';

Full-Text Search

A GIN index on a generated tsvector column covering title and description enables fast keyword search across all bookmarks for a user.

ALTER TABLE bookmarks ADD COLUMN fts tsvector
    GENERATED ALWAYS AS (
        to_tsvector('english', coalesce(title,'') || ' ' || coalesce(description,''))
    ) STORED;
CREATE INDEX idx_bookmarks_fts ON bookmarks USING GIN (fts);
-- Query: WHERE fts @@ plainto_tsquery('english', 'input query')

URL Deduplication

The UNIQUE constraint on (user_id, url) prevents a user from saving the same URL twice. On conflict, the application can offer to update the existing bookmark instead of inserting a duplicate.

INSERT INTO bookmarks (user_id, url, title, ...)
VALUES ($1, $2, $3, ...)
ON CONFLICT (user_id, url) DO UPDATE SET title = EXCLUDED.title;

Metadata Extraction

After a user saves a URL, an async worker fetches the page and extracts metadata without blocking the save response.

Title: og:title meta tag, fallback to <title>
Description: og:description, fallback to meta[name=description]
Favicon: link[rel=icon] or /favicon.ico

Worker flow:
1. Dequeue bookmark_id from extraction queue
2. HTTP GET url (with timeout + user-agent)
3. Parse HTML → extract og:title, og:description, link[rel=icon]
4. UPDATE bookmarks SET title=..., description=..., favicon_url=... WHERE id=bookmark_id

Import and Export

Import: Netscape Bookmark Format

Browsers export bookmarks as an HTML file in Netscape Bookmark Format. The importer parses <A> tags and batch-inserts records, skipping duplicates via ON CONFLICT.

Parse: <DT><A HREF="url" ADD_DATE="ts">Title</A>
Batch INSERT with ON CONFLICT (user_id, url) DO NOTHING
Enqueue metadata extraction for each new bookmark

Export: Netscape Bookmark Format

Generate a standards-compliant HTML file browsers can import directly.

<!DOCTYPE NETSCAPE-Bookmark-file-1>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8">
<TITLE>Bookmarks</TITLE>
<H1>Bookmarks</H1>
<DL><p>
    <DT><A HREF="{url}" ADD_DATE="{unix_ts}">{title}</A>
</DL><p>

Public Bookmarks and Social Sharing

When is_public=true, the bookmark is accessible via a public URL without authentication. Public collections are similarly accessible. Shareable links use the pattern /u/{username}/b/{bookmark_id}. Open Graph meta tags on the public page enable rich previews when shared on social platforms.

Cross-Device Sync

Each bookmark carries a last_updated_at timestamp. Mobile clients store the timestamp of their last sync and request only changed items.

-- Server endpoint: GET /api/bookmarks?since=2024-01-01T00:00:00Z
SELECT * FROM bookmarks
WHERE user_id = $1 AND last_updated_at > $2
ORDER BY last_updated_at ASC
LIMIT 500;

Deleted bookmarks are soft-deleted (deleted_at timestamp) so clients can remove them locally. Hard deletion runs after all clients have synced past the deletion timestamp.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How does a bookmark service handle duplicate URLs for the same user?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A UNIQUE constraint on (user_id, url) prevents duplicate bookmarks at the database level. On conflict, the application can surface the existing bookmark and offer to update its metadata or move it to a different collection instead of creating a duplicate.”
}
},
{
“@type”: “Question”,
“name”: “How is full-text search implemented in a bookmark service?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A GIN index on a generated tsvector column covering the title and description fields enables efficient full-text keyword search. PostgreSQL's plainto_tsquery function converts user input into a search query that runs against this index without a sequential scan.”
}
},
{
“@type”: “Question”,
“name”: “How does cross-device sync work for bookmarks?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Each bookmark carries a last_updated_at timestamp. Mobile and desktop clients store the timestamp of their last successful sync and request only bookmarks changed since that time. Soft-deleted bookmarks retain a deleted_at timestamp so clients can remove them locally before hard deletion occurs.”
}
},
{
“@type”: “Question”,
“name”: “How are tags indexed efficiently in a bookmark service?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Tags are stored as a JSONB array on the bookmark row. A GIN index on the JSONB column supports containment queries such as WHERE tags @> '[“javascript”]', enabling fast tag-based filtering without a separate normalized tags table or join.”
}
}
]
}