Database

One variable: DB_PROVIDER=sqlite|mongodb|supabase. App code never imports a driver — only providers/db/index.js and models/*.js shims.

Verbs (Lego blocks)

Every engine implements the same surface, returning plain { _id, id, … }:

• connect(), upsertUserByEmail()
• User{findById,findOne,create,counts} — findById supports .populate("boards").
• Board{create,findById,findOne,deleteOne,setCategories,rename,sanitizeCategories}
• Lead{findOne,create}
• Post{create,findById,listByBoard,setStatus,deleteOne,deleteByBoard}
• Vote{toggle,hasVoted}, Comment{create,listByPost}
• Product{create,getById,listActive,listBySeller,setActive,setProviderRef,deleteOne/delete}
• Order{create,updateStatus,updateStatusByPaymentRef,listBySeller,listAll,listByBuyer,findByPaymentRef,hasBought}

Files per engine

• SQLite: providers/db/sqlite/client.js (DDL + guarded ALTER TABLE migrations, WAL, FK ON) + users/boards/leads/posts/store.js + index.js facade.
• MongoDB: providers/db/mongodb/models.js (Mongoose schemas + indexes) + index.js.
• Supabase: providers/db/supabase/schema.sql (run once) + per-entity helpers + index.js.

Parity notes: SQLite tables are singular (user, board), Supabase plural (users, boards) — hidden by providers. providerPaymentRef is unique (partial/sparse) on all three; pricingType exists on products and orders everywhere; githubUsername exists on users everywhere.

Migrations (Additive Only, Zero Downtime)

Run the migration script anytime:

npm run db:migrate

The 5 Golden Rules of Migrations:

1. Never use destructive SQL: No DROP TABLE, DROP COLUMN, or TRUNCATE.
2. Never DELETE without a strict WHERE clause.
3. Always use additive changes: Use ALTER TABLE ... ADD COLUMN with a safe default value (DEFAULT '', DEFAULT 0, DEFAULT false).
4. Pre-DDL automated backups: npm run db:migrate automatically dumps a snapshot to db/backups/ before running any DDL on SQLite.
5. Idempotency: Migrations must be safe to run 100 times without failing (use IF NOT EXISTS or check schema before adding).

---

How to Write a New Migration

1. Adding an Additive Column in SQLite (providers/db/sqlite/client.js)

Inside initRaw(db), add a guarded migration block:

// Example: Add 'avatarUrl' and 'role' to 'user' table
function applyMigrations(db) {
  const userCols = db.prepare(`PRAGMA table_info("user")`).all().map((c) => c.name);

  if (!userCols.includes("avatarUrl")) {
    db.exec(`ALTER TABLE "user" ADD COLUMN "avatarUrl" TEXT DEFAULT ''`);
  }

  if (!userCols.includes("role")) {
    db.exec(`ALTER TABLE "user" ADD COLUMN "role" TEXT NOT NULL DEFAULT 'member'`);
  }

  // Create lookup index if needed
  db.exec(`CREATE INDEX IF NOT EXISTS idx_user_role ON "user"("role")`);
}

2. Writing the Postgres / Supabase Migration SQL (providers/db/supabase/schema.sql)

Append the additive SQL block to providers/db/supabase/schema.sql:

-- Migration: Add avatarUrl and role to users
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "avatarUrl" text DEFAULT '';
ALTER TABLE "users" ADD COLUMN IF NOT EXISTS "role" text NOT NULL DEFAULT 'member';
CREATE INDEX IF NOT EXISTS "idx_users_role" ON "users"("role");

To apply in Supabase: Copy this snippet into the Supabase Dashboard SQL Editor and click Run.

3. Writing the MongoDB Migration (providers/db/mongodb/models.js)

Update the Mongoose schema:

const userSchema = new mongoose.Schema(
  {
    // Existing fields...
    avatarUrl: { type: String, default: "" },
    role: { type: String, default: "member", index: true },
  },
  { timestamps: true }
);

Run npm run db:migrate — it automatically runs model.syncIndexes() on all models.

---

Writing a Standalone Custom Migration Script

If you have a complex data transformation (e.g. backfilling data or converting old records), create a script under scripts/migrations/:

// File: scripts/migrations/2026_09_backfill_roles.mjs
import { getRaw } from "../../providers/db/sqlite/client.js";
import { DB_PROVIDER } from "../../libs/config.js";

async function run() {
  console.log(`Running migration on ${DB_PROVIDER}...`);

  if (DB_PROVIDER === "sqlite") {
    const raw = getRaw();
    // Safe transactional update
    const update = raw.transaction(() => {
      raw.prepare(`UPDATE "user" SET "role" = 'admin' WHERE email LIKE '%@mycompany.com' AND "role" = 'member'`).run();
    });
    update();
    console.log("✓ SQLite backfill complete.");
  }
}

run().catch(console.error);

Run with: node scripts/migrations/2026_09_backfill_roles.mjs

Backups & Optimization

• App DB: npm run backup:db (scripts/backup-sqlite.sh, WAL trio, keeps last 24, cron hourly).
• Optimize DB: npm run db:optimize (scripts/optimize.mjs, truncates WAL to 0 bytes, runs VACUUM and PRAGMA optimize).
• Analytics DB (analytics.sqlite) must be included in volume snapshots — it is separate by design so telemetry never contends with product writes.
• Prod SQLite path: SQLITE_PATH=/var/data/app.db on a persistent volume. Ephemeral disk = data loss.

Single $5 Server Deployment vs Managed Cloud

Option A: The Single $5 VPS (Zero Database Bills)

Deploy your entire SaaS on a single $4–$5/month server (Hetzner, DigitalOcean, Linode) or container volume (Fly.io, Railway):

• Next.js App + SQLite DB + Analytics + Jobs all live on one server.
• 0ms Network Latency: In-process C++ native queries (better-sqlite3) executing in microseconds from RAM.
• $0 Database Hosting Bills: Never pay $29–$49/month for hosted Postgres before making your first dollar.
• Setup in .env.prod:

  DB_PROVIDER=sqlite
  SQLITE_PATH=/var/data/app.db
  

Option B: Managed Cloud (Supabase or MongoDB Atlas)

When you want managed relational Postgres or document clusters:

• Flip one environment variable in .env.prod:

  DB_PROVIDER=supabase  # or mongodb
  SUPABASE_URL=https://<project-ref>.supabase.co
  SUPABASE_SERVICE_KEY=your_service_role_key
  

• Zero Code Rewrites: Every model, query, and thin API route handler works identically across engines with full schema parity.

---

Supabase Setup Walkthrough (Step-by-Step)

If you want to use Supabase as your database backend, follow these 3 steps:

Step 1: Copy & Run the SQL Schema (One-Time)

Unlike SQLite (which creates tables dynamically) and MongoDB (which creates collections on the fly), Postgres requires tables to exist before the first query:

1. Open your Supabase Dashboard.
2. In the left navigation menu, click SQL Editor (the terminal/code icon).
3. Click New query.
4. Open providers/db/supabase/schema.sql in this repo, copy the entire file contents, and paste them into the Supabase SQL editor.
5. Click Run (green button).
   • This creates all 11 required tables (users, accounts, sessions, verification_tokens, boards, leads, posts, votes, comments, products, orders) along with foreign key constraints and lookup indexes.

Step 2: Grab Your Project URL and Service Role Key

1. In your Supabase project dashboard, click the Settings gear icon (bottom left) → API.
2. Under Project URL, copy your URL:
   https://<project-ref>.supabase.co
3. Under Project API keys, find the key named service_role (marked "secret"):
   • Click Reveal and copy it.
   • ⚠️ CRITICAL: Do not use the anon / publishable key. The server needs the service_role key to bypass Row Level Security (RLS) when creating users, processing webhook orders, and updating subscriptions.

Step 3: Add to Your Environment File

In .env.local (for development) or .env.prod (for production):

DB_PROVIDER=supabase
SUPABASE_URL=https://<project-ref>.supabase.co
SUPABASE_SERVICE_KEY=eyJhbGciOi...your_service_role_key_here

Restart your dev server:

npm run dev

Note on Supabase CLI (supabase login, supabase link)

You do NOT need the Supabase CLI.
BoringPush communicates with Supabase over HTTPS using the official @supabase/supabase-js client SDK. It does not require local Docker containers, CLI logins, or supabase link to function. All queries execute safely through the service role API.

---

How to Add a New Table (Across All 3 Engines)

When you need to create a new database table (e.g. notifications, invoices, teams), follow this deterministic protocol:

Step 1: SQLite (providers/db/sqlite/client.js)

Add the CREATE TABLE IF NOT EXISTS definition to initRaw(db):

CREATE TABLE IF NOT EXISTS notification (
  id TEXT PRIMARY KEY,
  userId TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
  message TEXT NOT NULL,
  read INTEGER NOT NULL DEFAULT 0,
  createdAt INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_notification_userId ON notification(userId);

• How to apply: Run npm run db:migrate. The migration script automatically creates an automated pre-DDL backup checkpoint, runs the DDL, and logs it in schema_migrations.

Step 2: Supabase / Postgres (providers/db/supabase/schema.sql)

Add the Postgres definition to schema.sql:

create table if not exists "notifications" (
  "id" text primary key,
  "userId" text not null references "users"("id") on delete cascade,
  "message" text not null,
  "read" boolean not null default false,
  "createdAt" timestamptz not null default now()
);
create index if not exists "idx_notifications_userId" on "notifications"("userId");

• How to apply: Paste and run this SQL block in Supabase Dashboard → SQL Editor (or via psql).

Step 3: MongoDB (providers/db/mongodb/models.js)

Define the Mongoose schema:

const notificationSchema = new mongoose.Schema(
  {
    userId: { type: mongoose.Schema.Types.ObjectId, ref: "User", required: true, index: true },
    message: { type: String, required: true },
    read: { type: Boolean, default: false },
  },
  { timestamps: true }
);

export const NotificationModel =
  mongoose.models.Notification || mongoose.model("Notification", notificationSchema);

Step 4: Wire Provider Surface & Model Shim

1. In providers/db/index.js, expose the entity:

export const Notification = {
  create: delegate("Notification", "create"),
  listByUser: delegate("Notification", "listByUser"),
  markRead: delegate("Notification", "markRead"),
};

2. Create models/Notification.js (one-line re-export shim):

export { Notification as default, Notification } from "@/providers/db";

Step 5: Verification Gate

Run the verification commands to guarantee 100% parity:

npm run db:migrate
npm run test:matrix
npm run lint