Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• packages/server/src/sql/migrations/0001_init.sql
• packages/server/src/sql/migrations/0004_tag.sql
• packages/server/src/model/page.ts
• packages/server/src/model/tag.ts
• packages/server/src/api/pages.ts

The database for the Web Archive application is built on Cloudflare D1. It is responsible for storing metadata about archived web pages, their categorization into folders, and their association with tags. The schema is designed to be simple and efficient, utilizing features like soft deletes for data recovery and JSON fields to manage many-to-many relationships.

The core entities are pages, folders, and tags. A page belongs to a single folder, but can have multiple tags. The server-side logic for interacting with this database is encapsulated in the packages/server/src/model directory, which provides an abstraction layer over the raw SQL queries.

Entity-Relationship Diagram

The following diagram illustrates the relationships between the main tables in the database.

Tables

pages

This table stores the metadata for each archived web page.

Sources: packages/server/src/sql/migrations/0001_init.sql

Schema Definition

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY, AUTOINCREMENT	Unique identifier for the page.
screenshotId	TEXT		Key for the screenshot file stored in R2 bucket.
title	TEXT	NOT NULL	The title of the archived page.
contentUrl	TEXT	NOT NULL	Key for the HTML content file stored in R2 bucket.
pageUrl	TEXT	NOT NULL	The original URL of the archived page.
folderId	INTEGER	NOT NULL	Foreign key referencing the folders.id.
pageDesc	TEXT	NOT NULL, DEFAULT ''	A description of the page.
isDeleted	BOOLEAN	NOT NULL, DEFAULT 0	A flag for soft deletion (0 = not deleted, 1 = deleted).
deletedAt	DATETIME		Timestamp when the page was soft-deleted.
isShowcased	BOOLEAN	NOT NULL, DEFAULT 0	Flag indicating if the page is featured on the showcase.
createdAt	DATETIME	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Timestamp of creation.
updatedAt	DATETIME	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Timestamp of the last update.

Sources: packages/server/src/sql/migrations/0001_init.sql:3-16, packages/server/src/sql/migrations/0003_showcase.sql:3-7

Indexes

• idx_pages_id: Index on the primary key id.
• idx_pages_pageUrl: Index on the pageUrl column for faster lookups by original URL.
• idx_pages_folderId: Index on the folderId column to speed up queries for pages within a folder.

Sources: packages/server/src/sql/migrations/0001_init.sql:18-20

Key Operations

The pages table supports standard CRUD operations, along with soft delete and restore functionality.

• Creating a Page: A new page is created via the /api/pages/upload_new_page endpoint. This involves saving the page content and screenshot to an R2 bucket, then inserting a record into the pages table. Associated tags are also linked.
• Querying Pages: Pages can be queried with various filters like folderId, tagId, and keyword. Pagination is supported.
• Soft Deleting a Page: A page is soft-deleted by setting isDeleted to 1 and recording deletedAt. The actual record and its associated files in R2 are not removed immediately.
• Restoring a Page: A soft-deleted page can be restored by setting isDeleted back to 0. If its parent folder was also deleted, the folder is restored first.
• Permanently Deleting Pages: The clearDeletedPage function removes all soft-deleted pages from the database and deletes their corresponding files from the R2 bucket.

The following sequence diagram shows the process of uploading a new page.

folders

This table organizes pages into categories.

Sources: packages/server/src/sql/migrations/0001_init.sql

Schema Definition

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY, AUTOINCREMENT	Unique identifier for the folder.
name	TEXT	NOT NULL	The name of the folder.
isDeleted	BOOLEAN	NOT NULL, DEFAULT 0	A flag for soft deletion.
deletedAt	DATETIME		Timestamp when the folder was soft-deleted.
createdAt	DATETIME	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Timestamp of creation.
updatedAt	DATETIME	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Timestamp of the last update.

Sources: packages/server/src/sql/migrations/0001_init.sql:23-30

Indexes

• idx_folders_id: Index on the primary key id.

Sources: packages/server/src/sql/migrations/0001_init.sql:32

Special Records

• A default folder named uncategorized with id = 0 is created during the initial database migration. This serves as a fallback for pages without a specified folder.

INSERT INTO folders (name, id) VALUES ('uncategorized', 0);

Sources: packages/server/src/sql/migrations/0001_init.sql:34

tags

This table stores tags and manages the many-to-many relationship between tags and pages.

Sources: packages/server/src/sql/migrations/0004_tag.sql

Schema Definition

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY, AUTOINCREMENT	Unique identifier for the tag.
name	TEXT	NOT NULL, UNIQUE	The name of the tag.
pageIdDict	TEXT	NOT NULL, DEFAULT '{}'	A JSON object storing associated page IDs. Example: {"1":1, "5":5}.
color	TEXT	NOT NULL, DEFAULT '#FFFFFF'	The color code for the tag.
createdAt	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Timestamp of creation.
updatedAt	TIMESTAMP	NOT NULL, DEFAULT CURRENT_TIMESTAMP	Timestamp of the last update.

Sources: packages/server/src/sql/migrations/0004_tag.sql:3-10

Indexes

• idx_tags_id: Index on the primary key id.
• idx_tags_name: Index on the unique name column.

Sources: packages/server/src/sql/migrations/0004_tag.sql:12-13

Page-Tag Relationship Management

Instead of a traditional junction table, the many-to-many relationship between pages and tags is managed using the pageIdDict JSON field in the tags table. This field stores a dictionary where keys and values are both the pageId.

• Binding a Page to a Tag: To associate a page with a tag, the page's ID is added as a key-value pair to the pageIdDict JSON object.
• Unbinding a Page: To remove an association, the corresponding key is set to null in the JSON object, which effectively removes it upon a json_patch.

The generateUpdateTagSql function in packages/server/src/model/tag.ts constructs the D1 database statements to perform these updates using INSERT ... ON CONFLICT ... DO UPDATE with json_patch.

// packages/server/src/model/tag.ts:90-104
function generateUpdateTagSql(
  DB: D1Database,
  bindList: Array<TagBindRecord>,
  unbindList: Array<TagBindRecord>,
) {
  const updateStmt = DB.prepare(`
    INSERT INTO tags (name, pageIdDict) VALUES (?, ?)
      ON CONFLICT(name) DO UPDATE SET pageIdDict = json_patch(pageIdDict, ?) WHERE name = ?
    `)
  const bindCommands = bindList.map(({ tagName, pageIds }) => {
    const mergePageJson = pageIdsToBindDictString(pageIds)
    return updateStmt.bind(tagName, mergePageJson, mergePageJson, tagName)
  })
  // ... unbind logic is similar
  return bindCommands.concat(unbindCommands)
}

Sources: packages/server/src/model/tag.ts:90-104

The following flowchart shows the logic for updating tag-page associations.

Summary

The database schema for Web Archive is a lean and effective structure leveraging Cloudflare D1. It uses standard relational concepts for pages and folders but adopts a modern, NoSQL-style approach with a JSON field for the many-to-many tags-to-pages relationship. This avoids the need for a separate junction table, simplifying some queries. The consistent use of soft deletes across tables provides a safety net for data management, allowing for easy restoration of accidentally deleted content.