Database Schema & Storage
Relevant source files
The following files were used as context for generating this wiki page:
PuppyPad employs a hybrid storage architecture to manage member sites and social interactions. It utilizes a relational SQLite database for metadata, account details, and social graph information, while storing static site files and screenshots directly on the host filesystem.
The system manages storage within a central directory defined by the DATA_DIR environment variable. This directory contains the SQLite database file (puppypad.db), a sites/ subdirectory for user-uploaded content, a shots/ directory for site screenshots, and a tmp/ directory for handling active uploads.
Sources: PLAN.md:12-23, src/db.js:5-10, src/server.js:103-108
Relational Database Schema
The relational schema is managed using the better-sqlite3 driver. The database operates in Write-Ahead Logging (WAL) mode to improve concurrency and enforces foreign key constraints.
Entity Relationship Diagram
The following diagram illustrates the relationships between core tables in the puppypad.db database.
The diagram shows the central role of the users table, which connects to social features (follows, blocks), content features (guestbook, comments), and session management.
Sources: src/db.js:15-125
Core Tables Summary
| Table | Description | Key Fields |
|---|---|---|
users | Stores account credentials, profile metadata, and site status. | username, email, pw_hash, verified, banned, quota_mb |
sessions | Manages active login sessions and CSRF protection tokens. | token_hash, user_id, csrf, expires |
tokens | Temporary hashes for email verification, password resets, and 2FA. | token_hash, kind, expires |
follows | Maps follower-followed relationships between site owners. | follower_id, followed_id |
guestbook | Stores entries left by visitors on member sites. | user_id, name, message, approved |
events | Records site updates and join events for social feeds. | user_id, kind, files |
admin_log | Audits moderator actions including bans and deletions. | admin, action, site, detail |
Sources: src/db.js:16-86, src/db.js:103-112
Filesystem Storage Architecture
PuppyPad stores large-scale and static data outside the relational database to optimize performance and simplify static file serving.
Directory Structure
- Sites Directory (
data/sites/): Each member has a dedicated folder named after theirusername. This folder contains all HTML, CSS, JS, and image files uploaded by the user. - Screenshots Directory (
data/shots/): Contains.jpgfiles representing the front-page state of member sites, used for the "Dog Park" and profile previews. - Temporary Directory (
data/tmp/): Acts as a staging area for multipart uploads and zip imports before they are validated and moved to the user's site directory.
The diagram represents the hierarchical organization of files on the host disk.
Sources: src/db.js:5-10, src/server.js:105-110, PLAN.md:14-23
File Integrity and Security
- Atomic Writes: The system writes data to a temporary file before renaming it to the target path to prevent file corruption during failures.
- Path Normalization: User-supplied paths are normalized to prevent directory traversal attacks (e.g., preventing access to
../). - Type Allowlist: Uploads are restricted to specific extensions (HTML, CSS, JS, images, etc.) to prevent execution of malicious server-side scripts.
Sources: src/server.js:452-463, src/util.js:10-25
Storage Management and Quotas
The application enforces strict limits to ensure server stability and resource fairness.
Quota Enforcement Logic
The server calculates disk usage for a site by walking its directory. To avoid performance degradation, these totals are stored in an in-memory usageCache and updated during file operations or periodic background refreshes.
This flow illustrates the validation process performed before any data is committed to the filesystem.
Sources: src/server.js:115-132, src/server.js:468-485
Storage and Bandwidth Limits
| Parameter | Default Value | Source |
|---|---|---|
QUOTA_MB | 500 MB | src/server.js:22 |
MAX_FILE_MB | 5 MB | src/server.js:23 |
ZIP_MAX_MB | 20 MB | src/server.js:24 |
BANDWIDTH_MB | 1024 MB/day | src/server.js:25 |
MAX_FILES | 2000 files | src/server.js:464 |
Sources: src/server.js:22-26, src/server.js:464
Data Maintenance
Housekeeping Tasks
A background interval runs every hour to perform the following cleanup operations:
- Delete expired sessions and tokens.
- Remove
released_namesentries older than 90 days. - Anonymize guestbook and report IP addresses after 90 days.
- Purge unconfirmed accounts created more than 3 days ago.
- Orphaned temp files older than one hour are deleted.
Sources: src/server.js:907-936
Backups
The scripts/backup.sh utility performs online SQLite backups, which are safe to run while the application is active. It archives the database file along with the data/sites directory into nightly archives, maintaining a 14-day rotation.
Sources: PLAN.md:88-93, README.md:92-99
Database schema and storage logic ensure that PuppyPad remains performant on a single VPS while providing social features and isolated static hosting for thousands of members. The separation of relational metadata and flat-file site content allows for efficient scaling and simple backup procedures.