Database Setup

Overview

Sptfy.in uses PocketBase as its backend database. PocketBase is a self-hosted SQLite database with a built-in admin UI and REST API. This guide covers:

Creating the admin account
Importing the database schema
Understanding the collections structure
Running migrations

Initial Setup

Create Admin Account

After starting PocketBase for the first time, create an admin account:

Access the admin panel

Open your browser and navigate to:Local development:

http://127.0.0.1:8090/_/

Production (after setting up Nginx reverse proxy):

https://pbbase.yourdomain.com/_/

If running in Docker on a VPS, you may need to SSH tunnel to access the admin panel:

ssh -L 8090:localhost:8091 user@your-vps

Then open http://localhost:8090/_/ in your browser.

Create admin credentials

On first access, you’ll see a setup screen:

Email: admin@yourdomain.com (use a real email)
Password: Choose a strong password (16+ characters, use a password manager)
Confirm Password: Re-enter your password

Click Create and Login.

Store these credentials securely. You cannot recover them if lost. If you lose access, you’ll need to delete pb_data/ and start over.

Import Database Schema

The repository includes migration files that define the database schema. You have two options:

Option 1: Automatic Migration (Recommended)

Migrations in pocketbase/pb_migrations/ run automatically when the container starts.

# Start the container (migrations run automatically)
cd ~/pb-docker
docker compose up -d

# Check logs to verify migrations ran
docker compose logs pocketbase | grep migration

You should see output like:

> applying migration 1738081665_created_analytic.js
> applying migration 1738156111_created_viewList.js

If you see errors about duplicate collections, migrations may have already run. This is normal on restarts.

Option 2: Manual Schema Import

If you need to manually import the schema (e.g., from production to local dev):

Export schema from production

Log in to production PocketBase admin: https://pbbase.sptfy.in/_/
Go to Settings → Export collections
Download the JSON file (e.g., pb_schema_export.json)

Import schema to your instance

Log in to your PocketBase admin panel
Go to Settings → Import collections
Upload the JSON file from step 1
Click Import

Importing overwrites existing collections. Back up your data first if you have existing records.

Database Structure

Collections Overview

Sptfy.in uses the following collections:

users - User accounts

Stores user authentication and profile data.Key fields:

id (text) - Unique user ID
email (email) - User email
emailVisibility (bool) - Whether email is public
verified (bool) - Email verification status
name (text) - Display name
avatar (file) - Profile picture

Auth providers:

Spotify OAuth (primary authentication method)

API Rules:

List: @request.auth.id != "" (authenticated users only)
View: @request.auth.id != "" (authenticated users only)
Create: Public (via Spotify OAuth)
Update: @request.auth.id = id (users can update their own profile)
Delete: Admin only

random_short - Short links

Stores all shortened Spotify links (both random and custom slugs).Key fields:

id (text) - Unique link ID
slug (text) - Short URL slug (e.g., “abc123” or “my-playlist”)
destination (url) - Original Spotify URL
user (relation) - User who created the link (optional, for authenticated users)
created (date) - Timestamp of creation
clicks (number) - Total click count
last_clicked (date) - Last click timestamp

Indexes:

Unique index on slug (prevents duplicate slugs)

API Rules:

List: @request.auth.id != "" && user = @request.auth.id (users see their own links)
View: Public (anyone can view link details)
Create: Public with Turnstile validation
Update: @request.auth.id = user (owner can update)
Delete: @request.auth.id = user (owner can delete)

analytics - Click tracking

Tracks individual clicks on short links for analytics.Key fields:

id (text) - Unique analytics record ID
link (relation) - Related short link
timestamp (date) - Click timestamp
ip (text) - IP address (hashed for privacy)
user_agent (text) - Browser user agent
referrer (url) - Referrer URL
country_code (text) - 2-letter country code (from IP geolocation)

Privacy:

IP addresses are hashed before storage
Country code determined via ip-api.com (see pb_hooks/random.pb.js)

API Rules:

List: @request.auth.id != "" && link.user = @request.auth.id (owner sees analytics)
View: @request.auth.id != "" && link.user = @request.auth.id
Create: Internal only (via hooks)
Update: Admin only
Delete: Admin only

blocks - Blocked users/IPs

Stores blocked users or IP addresses to prevent abuse.Key fields:

id (text) - Unique block ID
type (select) - Block type: user or ip
value (text) - User ID or IP address
reason (text) - Block reason
created (date) - Block timestamp
expires (date) - Expiration date (optional)

API Rules:

List: Admin only
View: Admin only
Create: Admin only
Update: Admin only
Delete: Admin only

viewList - View analytics aggregation

View collection that aggregates analytics data for dashboard display.Purpose: Pre-computed analytics summaries to improve dashboard performance.Fields: Aggregates clicks by time period, country, referrer, etc.API Rules:

List: @request.auth.id != "" (authenticated users only)
View: @request.auth.id != ""
Create/Update/Delete: Not applicable (view-only)

PocketBase Hooks

Hooks are custom JavaScript functions that run on specific events (e.g., record creation). Location: pocketbase/pb_hooks/

Available Hooks

/// <reference path="../pb_data/types.d.ts" />

// IP geolocation hook for analytics
onRecordCreateRequest((e) => {
    const requestIp = e.realIP();
    const res = $http.send({
        url: 'http://ip-api.com/json/' + requestIp + '?fields=countryCode',
        headers: { 'content-type': 'application/json' }
    });

    const response = res.json;
    const countryCode = response.countryCode;
    $app.logger().info('Country Code detected', 'countryCode', countryCode);
    
    e.next();
}, 'analytics');

Hooks are loaded automatically when PocketBase starts. Changes to hook files require a container restart:

docker compose restart

Migrations

Migrations define schema changes over time. They’re stored in pocketbase/pb_migrations/.

How Migrations Work

Each migration is a JavaScript file with a timestamp name:

1738081665_created_analytic.js
1738156111_created_viewList.js

PocketBase tracks which migrations have run in pb_data/data.db
On startup, PocketBase runs any new migrations in order

Creating New Migrations

Don’t manually create migration files. Instead:

Make schema changes in the PocketBase admin UI
PocketBase automatically generates migration files
Commit the new migration files to git
Deploy (GitHub Actions will pull the new migrations)

Version Compatibility: The production instance uses PocketBase v0.23.12. Migrations from v0.22 and earlier use a different API (Dao) and won’t work. Always export/import schema from the running v0.23+ instance.

Local Development Setup

For local development, you need to run PocketBase locally:

Windows

cd pocketbase
start-dev.bat

This script:

Sets CF_SECRET_KEY to the Turnstile test key (always passes)
Starts PocketBase on http://127.0.0.1:8090

Linux/macOS

cd pocketbase
CF_SECRET_KEY=1x0000000000000000000000000000000AA ./pocketbase serve

Connecting Frontend to Local Backend

Update your frontend .env file:

PUBLIC_POCKETBASE_URL=http://127.0.0.1:8090
PUBLIC_TURNSTILE_SITE_KEY=1x0000000000000000000000000000000AA

Then start the dev server:

pnpm dev

The test Turnstile site key 1x0000000000000000000000000000000AA always passes validation. Perfect for local development.

Database Backups

Automated Backups (Production)

Production uses PocketBase’s built-in S3 backup to Cloudflare R2. Configure in PocketBase Admin:

Settings → Backups
Enable S3 backups
Set schedule (e.g., daily at 3 AM)

Manual Backups

# Stop the container
cd ~/pb-docker
docker compose down

# Backup pb_data directory
tar -czf pb_data_backup_$(date +%Y%m%d).tar.gz pb_data/

# Copy to safe location
scp pb_data_backup_*.tar.gz user@backup-server:/backups/

# Restart container
docker compose up -d

Restore from Backup

# Stop the container
docker compose down

# Remove current data
mv pb_data pb_data.old

# Extract backup
tar -xzf pb_data_backup_20260302.tar.gz

# Restart container
docker compose up -d

Troubleshooting

Migrations fail to run

Error: migration already applied Solution: This is normal on restarts. PocketBase tracks applied migrations and skips them.

Cannot access admin panel

Error: Connection refused at http://localhost:8090 Solution: Check if PocketBase is running:

docker compose ps
curl http://localhost:8091/api/health

“Collection not found” errors

Cause: Migrations haven’t run or schema import failed. Solution: Check logs for migration errors:

docker compose logs pocketbase | grep -i error

If migrations failed, try manual schema import (see above).

Database locked errors

Cause: SQLite can have lock contention under high load. Solution: Increase VPS resources or optimize queries. For very high traffic, consider migrating to PostgreSQL (requires forking PocketBase or using a different backend).

Next Steps

Configuration - Set up environment variables and settings
Deployment - Deploy to production with Nginx and Cloudflare Pages

Get Started

Using sptfy.in

Self-Hosting

Overview

Initial Setup

Create Admin Account

Import Database Schema

Option 1: Automatic Migration (Recommended)

Option 2: Manual Schema Import

Database Structure

Collections Overview

PocketBase Hooks

Available Hooks

Migrations

How Migrations Work

Creating New Migrations

Local Development Setup

Windows

Linux/macOS

Connecting Frontend to Local Backend

Database Backups

Automated Backups (Production)

Manual Backups

Restore from Backup

Troubleshooting

Migrations fail to run

Cannot access admin panel

“Collection not found” errors

Database locked errors

Next Steps

Build docs developers (and LLMs) love

Get Started

Using sptfy.in

Self-Hosting

​Overview

​Initial Setup

​Create Admin Account

​Import Database Schema

​Option 1: Automatic Migration (Recommended)

​Option 2: Manual Schema Import

​Database Structure

​Collections Overview

​PocketBase Hooks

​Available Hooks

​Migrations

​How Migrations Work

​Creating New Migrations

​Local Development Setup

​Windows

​Linux/macOS

​Connecting Frontend to Local Backend

​Database Backups

​Automated Backups (Production)

​Manual Backups

​Restore from Backup

​Troubleshooting

​Migrations fail to run

​Cannot access admin panel

​“Collection not found” errors

​Database locked errors

​Next Steps

Build docs developers (and LLMs) love

Overview

Initial Setup

Create Admin Account

Import Database Schema

Option 1: Automatic Migration (Recommended)

Option 2: Manual Schema Import

Database Structure

Collections Overview

PocketBase Hooks

Available Hooks

Migrations

How Migrations Work

Creating New Migrations

Local Development Setup

Windows

Linux/macOS

Connecting Frontend to Local Backend

Database Backups

Automated Backups (Production)

Manual Backups

Restore from Backup

Troubleshooting

Migrations fail to run

Cannot access admin panel

“Collection not found” errors

Database locked errors

Next Steps