🌩️ Supabase Cloud Sync & Database Setup Guide

September 6, 2026 · View on GitHub

This guide walks you through setting up your own free, private Supabase database to synchronize your novels, reading progress, and Spirit Stones across all your devices (Android, Desktop JVM, iOS).

📌 Why Use Personal Supabase Sync?

100% Free Forever: Supabase's free tier provides 500MB of database storage (enough for millions of synced books and chapters).
Private & Secure: Your library data and reading history are stored on your personal cloud database, never on developer or community servers.
Full-Fidelity Synchronization (sync_manifest): Syncs books, complete chapters (titles, numbers, read status, bookmarks, without chapter body content), cover art, reading progress, scroll percentages, and reading history.
Cross-Device Gamification: Seamlessly earn Spirit Stones, keep your check-in streak, and sync balances across all your devices.
Quick Configuration Sharing: Export a lightweight JSON config on one device and import it on your phone or desktop in 2 clicks.

📋 Table of Contents

Step 1: Create a Free Supabase Project
Step 2: Copy Your Project Credentials
Step 3: Run the Database Setup SQL Script
Step 4: Configure IReader App
Step 5: Sync to Multiple Devices (Export & Import)
Step 6: Daily Check-in & Spirit Stones
Step 7: How Chapter Synchronization Works
Troubleshooting & Common Errors

Step 1: Create a Free Supabase Project

Open your browser and navigate to https://supabase.com.
Click Start your project (or sign in with GitHub / Email).
In the Supabase Dashboard, click New project.
Choose an organization (or create a personal one).
Fill in the project details:
- Name: IReader Sync (or any name you like).
- Database Password: Choose a strong password and save it in your password manager.
- Region: Choose the region geographically closest to you for fastest sync speeds.
- Pricing Plan: Select Free tier ($0/month).
Click Create new project. Supabase will take 1–2 minutes to provision your database.

Step 2: Copy Your Project Credentials

Once the project is ready:

In the left navigation sidebar, click the Settings gear icon (⚙️) at the bottom.
Select API (or Data API).
Locate the following two values:
- Project URL: Format looks like https://abcdefghijklm.supabase.co
- Project API Keys: Copy the anon / public key (starts with ey...).
Keep these handy—you will enter them into IReader.

Step 3: Run the Database Setup SQL Script

Choose the setup that best fits your hosting environment:

Option A: Lightweight (Recommended for Supabase Free Tier): Saves bandwidth and storage by synchronizing all book & chapter metadata without chapter body text. Fits millions of records in 500MB. Uses supabase/schema_lightweight.sql.
Option B: Full Content (For Self-Hosted PostgreSQL / Supabase): Backs up full novel chapter text to the cloud for offline access across self-hosted instances (VPS, Docker, TrueNAS). Uses supabase/schema_with_chapter_content.sql.

Note

The setup script below supports both modes. It creates synced_chapters with an optional content column that consumes 0 bytes when empty. The app setting "Sync Chapter Content (Full Text)" defaults to OFF, so your cloud database stays lightweight unless you explicitly enable content sync.

To prepare your database tables and RPC functions for book syncing and Spirit Stones check-in:

In the Supabase dashboard sidebar, click SQL Editor (icon with >_).
Click New query (or the + button).
Copy and paste the complete SQL script below into the editor:

-- ==========================================================
-- IReader Unified Sync & Gamification Setup Script
-- ==========================================================

-- 1. Full Sync Manifest (High-Fidelity Document Store)
CREATE TABLE IF NOT EXISTS public.sync_manifest (
    user_id    TEXT NOT NULL PRIMARY KEY,
    manifest   JSONB NOT NULL,
    updated_at BIGINT NOT NULL DEFAULT 0
);
ALTER TABLE public.sync_manifest ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow public sync_manifest access" ON public.sync_manifest;
CREATE POLICY "Allow public sync_manifest access" ON public.sync_manifest FOR ALL USING (true) WITH CHECK (true);
CREATE INDEX IF NOT EXISTS idx_sync_manifest_gin ON public.sync_manifest USING GIN (manifest jsonb_path_ops);

-- 2. Synced Books (Relational View with rich metadata)
CREATE TABLE IF NOT EXISTS public.synced_books (
    user_id     TEXT NOT NULL,
    book_id     TEXT NOT NULL,
    source_id   BIGINT NOT NULL,
    title       TEXT NOT NULL,
    book_url    TEXT NOT NULL,
    last_read   BIGINT NOT NULL DEFAULT 0,
    cover_url   TEXT DEFAULT '',
    source_name TEXT DEFAULT '',
    author      TEXT DEFAULT '',
    description TEXT DEFAULT '',
    genres      TEXT DEFAULT '',
    status      BIGINT DEFAULT 0,
    favorite    BOOLEAN DEFAULT true,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (user_id, book_id)
);

ALTER TABLE public.synced_books DROP CONSTRAINT IF EXISTS synced_books_user_id_fkey;

ALTER TABLE public.synced_books
    ADD COLUMN IF NOT EXISTS author      TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS description TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS genres      TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS status      BIGINT DEFAULT 0,
    ADD COLUMN IF NOT EXISTS favorite    BOOLEAN DEFAULT true,
    ADD COLUMN IF NOT EXISTS cover_url   TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS source_name TEXT DEFAULT '',
    ADD COLUMN IF NOT EXISTS updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW();

CREATE INDEX IF NOT EXISTS idx_synced_books_user_id ON public.synced_books(user_id);
ALTER TABLE public.synced_books ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow public synced_books access" ON public.synced_books;
CREATE POLICY "Allow public synced_books access" ON public.synced_books FOR ALL USING (true) WITH CHECK (true);

-- 3. Reading Progress (Relational View)
CREATE TABLE IF NOT EXISTS public.reading_progress (
    user_id              TEXT NOT NULL,
    book_id              TEXT NOT NULL,
    last_chapter_slug    TEXT NOT NULL,
    last_scroll_position FLOAT DEFAULT 0,
    updated_at           BIGINT DEFAULT 0,
    PRIMARY KEY (user_id, book_id)
);

ALTER TABLE public.reading_progress DROP CONSTRAINT IF EXISTS reading_progress_user_id_fkey;
ALTER TABLE public.reading_progress DROP CONSTRAINT IF EXISTS scroll_position_range;

CREATE INDEX IF NOT EXISTS idx_reading_progress_user_id ON public.reading_progress(user_id);
ALTER TABLE public.reading_progress ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow public reading_progress access" ON public.reading_progress;
CREATE POLICY "Allow public reading_progress access" ON public.reading_progress FOR ALL USING (true) WITH CHECK (true);

-- 4. Synced Chapters (Relational Table with optional content support)
CREATE TABLE IF NOT EXISTS public.synced_chapters (
    user_id        TEXT NOT NULL,
    chapter_id     TEXT NOT NULL,
    book_id        TEXT NOT NULL,
    chapter_key    TEXT NOT NULL,
    name           TEXT NOT NULL,
    chapter_number REAL DEFAULT 0,
    source_order   BIGINT DEFAULT 0,
    read           BOOLEAN DEFAULT false,
    bookmark       BOOLEAN DEFAULT false,
    last_page_read BIGINT DEFAULT 0,
    date_upload    BIGINT DEFAULT 0,
    date_fetch     BIGINT DEFAULT 0,
    translator     TEXT DEFAULT '',
    content        TEXT DEFAULT '',
    updated_at     TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    PRIMARY KEY (user_id, chapter_id)
);
ALTER TABLE public.synced_chapters ADD COLUMN IF NOT EXISTS content TEXT DEFAULT '';

CREATE INDEX IF NOT EXISTS idx_synced_chapters_user_id ON public.synced_chapters(user_id);
CREATE INDEX IF NOT EXISTS idx_synced_chapters_book_id ON public.synced_chapters(user_id, book_id);
CREATE INDEX IF NOT EXISTS idx_synced_chapters_read ON public.synced_chapters(user_id, read);
CREATE INDEX IF NOT EXISTS idx_synced_chapters_bookmark ON public.synced_chapters(user_id, bookmark);

ALTER TABLE public.synced_chapters ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Allow public synced_chapters access" ON public.synced_chapters;
CREATE POLICY "Allow public synced_chapters access" ON public.synced_chapters FOR ALL USING (true) WITH CHECK (true);

-- 5. Synced Chapters Dynamic View (Unpacked from JSONB Manifest)
CREATE OR REPLACE VIEW public.synced_chapters_view 
WITH (security_invoker = true) AS
SELECT 
    sm.user_id,
    ch->>'globalId' AS chapter_id,
    ch->>'bookGlobalId' AS book_id,
    ch->>'key' AS chapter_key,
    ch->>'name' AS name,
    COALESCE((ch->>'number')::numeric, 0) AS chapter_number,
    COALESCE((ch->>'sourceOrder')::bigint, 0) AS source_order,
    COALESCE((ch->>'read')::boolean, false) AS read,
    COALESCE((ch->>'bookmark')::boolean, false) AS bookmark,
    COALESCE((ch->>'lastPageRead')::bigint, 0) AS last_page_read,
    COALESCE((ch->>'dateUpload')::bigint, 0) AS date_upload,
    COALESCE((ch->>'dateFetch')::bigint, 0) AS date_fetch,
    COALESCE(ch->>'translator', '') AS translator,
    COALESCE(ch->>'content', '') AS content,
    sm.updated_at
FROM public.sync_manifest sm,
LATERAL jsonb_array_elements(sm.manifest->'chapters') AS ch;

-- 6. Users & Gamification Economy
CREATE TABLE IF NOT EXISTS public.users (
    id UUID PRIMARY KEY DEFAULT auth.uid(),
    email TEXT,
    username TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Users can read own profile" ON public.users;
CREATE POLICY "Users can read own profile" ON public.users FOR SELECT USING (auth.uid() = id);
DROP POLICY IF EXISTS "Users can update own profile" ON public.users;
CREATE POLICY "Users can update own profile" ON public.users FOR UPDATE USING (auth.uid() = id);

ALTER TABLE public.users
    ADD COLUMN IF NOT EXISTS display_name      TEXT,
    ADD COLUMN IF NOT EXISTS bio               TEXT    DEFAULT '',
    ADD COLUMN IF NOT EXISTS avatar_url        TEXT,
    ADD COLUMN IF NOT EXISTS cover_image_url   TEXT,
    ADD COLUMN IF NOT EXISTS level             INT     DEFAULT 1,
    ADD COLUMN IF NOT EXISTS xp                BIGINT  DEFAULT 0,
    ADD COLUMN IF NOT EXISTS level_title       TEXT    DEFAULT 'Novice Reader',
    ADD COLUMN IF NOT EXISTS spirit_stones     BIGINT  DEFAULT 0,
    ADD COLUMN IF NOT EXISTS active_title_id   TEXT,
    ADD COLUMN IF NOT EXISTS checkin_streak    INT     DEFAULT 0,
    ADD COLUMN IF NOT EXISTS last_checkin_date DATE;

-- 5. Daily Check-ins Table
CREATE TABLE IF NOT EXISTS public.daily_checkins (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    checkin_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    streak_day    INT NOT NULL DEFAULT 1,
    reward_amount INT NOT NULL DEFAULT 10,
    created_at    TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (user_id, checkin_date)
);
ALTER TABLE public.daily_checkins ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS daily_checkins_read ON public.daily_checkins;
CREATE POLICY daily_checkins_read ON public.daily_checkins FOR SELECT USING (auth.uid() = user_id);

-- 6. Spirit Stone Transactions Table
CREATE TABLE IF NOT EXISTS public.spirit_stone_transactions (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    amount       BIGINT NOT NULL,
    type         TEXT NOT NULL,
    description  TEXT DEFAULT '',
    reference_id TEXT,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);
ALTER TABLE public.spirit_stone_transactions ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS sst_read ON public.spirit_stone_transactions;
CREATE POLICY sst_read ON public.spirit_stone_transactions FOR SELECT USING (auth.uid() = user_id);

-- 7. User Titles Table
CREATE TABLE IF NOT EXISTS public.user_titles (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    title_id    TEXT NOT NULL,
    title_name  TEXT NOT NULL,
    rarity      TEXT NOT NULL DEFAULT 'COMMON',
    is_active   BOOLEAN DEFAULT FALSE,
    acquired_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (user_id, title_id)
);
ALTER TABLE public.user_titles ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS user_titles_all ON public.user_titles;
CREATE POLICY user_titles_all ON public.user_titles FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

-- 8. Daily Check-in RPC Function (Clean drop prevents return-type conflict)
DROP FUNCTION IF EXISTS public.checkin_daily();

CREATE OR REPLACE FUNCTION public.checkin_daily()
RETURNS JSON
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
    v_user UUID := auth.uid();
    v_today DATE := CURRENT_DATE;
    v_last DATE;
    v_streak INT;
    v_reward INT;
BEGIN
    IF v_user IS NULL THEN RAISE EXCEPTION 'Not authenticated'; END IF;

    INSERT INTO public.users (id, email, username)
    SELECT v_user, auth.users.email, COALESCE(auth.users.raw_user_meta_data->>'username', 'Reader')
    FROM auth.users WHERE id = v_user
    ON CONFLICT (id) DO NOTHING;

    SELECT last_checkin_date, COALESCE(checkin_streak, 0) INTO v_last, v_streak
      FROM public.users WHERE id = v_user;

    IF v_last = v_today THEN
        RETURN json_build_object('already', true, 'streak_day', v_streak, 'reward', 0);
    END IF;

    IF v_last = v_today - 1 THEN
        v_streak := v_streak + 1;
    ELSE
        v_streak := 1;
    END IF;

    v_reward := CASE
        WHEN v_streak % 30 = 0 THEN 200
        WHEN v_streak % 7 = 0 THEN 50
        ELSE 10
    END;

    INSERT INTO public.daily_checkins (user_id, checkin_date, streak_day, reward_amount)
    VALUES (v_user, v_today, v_streak, v_reward)
    ON CONFLICT (user_id, checkin_date) DO NOTHING;

    UPDATE public.users
       SET spirit_stones = COALESCE(spirit_stones, 0) + v_reward,
           checkin_streak = v_streak,
           last_checkin_date = v_today
     WHERE id = v_user;

    INSERT INTO public.spirit_stone_transactions (user_id, amount, type, description)
    VALUES (v_user, v_reward, 'CHECKIN', 'Daily check-in (day ' || v_streak || ')');

    RETURN json_build_object('already', false, 'streak_day', v_streak, 'reward', v_reward);
END;
$$;
GRANT EXECUTE ON FUNCTION public.checkin_daily() TO authenticated;

-- 9. Spend Stones RPC Function
DROP FUNCTION IF EXISTS public.spend_stones(TEXT, TEXT, INT);
DROP FUNCTION IF EXISTS public.spend_stones(INT, TEXT);
DROP FUNCTION IF EXISTS public.spend_stones;

CREATE OR REPLACE FUNCTION public.spend_stones(
    p_item_type TEXT,
    p_item_id   TEXT,
    p_cost      INT
)
RETURNS JSON
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
    v_user UUID := auth.uid();
    v_balance BIGINT;
BEGIN
    IF v_user IS NULL THEN RAISE EXCEPTION 'Not authenticated'; END IF;
    IF p_cost < 0 THEN RAISE EXCEPTION 'Invalid cost'; END IF;

    SELECT COALESCE(spirit_stones, 0) INTO v_balance FROM public.users WHERE id = v_user FOR UPDATE;
    IF v_balance < p_cost THEN
        RETURN json_build_object('ok', false, 'reason', 'INSUFFICIENT_STONES', 'balance', v_balance);
    END IF;

    UPDATE public.users SET spirit_stones = spirit_stones - p_cost WHERE id = v_user;
    INSERT INTO public.spirit_stone_transactions (user_id, amount, type, description, reference_id)
    VALUES (v_user, -p_cost, 'SPEND', 'Purchased ' || p_item_type || ': ' || p_item_id, p_item_id);

    IF p_item_type = 'TITLE' THEN
        INSERT INTO public.user_titles (user_id, title_id, title_name)
        VALUES (v_user, p_item_id, p_item_id)
        ON CONFLICT (user_id, title_id) DO NOTHING;
    END IF;

    RETURN json_build_object('ok', true, 'balance', v_balance - p_cost);
END;
$$;
GRANT EXECUTE ON FUNCTION public.spend_stones(TEXT, TEXT, INT) TO authenticated;

Click Run (or press Ctrl+Enter / Cmd+Enter).
Verify you see Success. No rows returned. in the results pane.

Tip

You can also copy this script directly inside the app at any time by navigating to Settings → Supabase Configuration and tapping "Copy Setup SQL".

Step 4: Configure IReader App

Open IReader.
Go to More / Settings → Sync → Supabase Configuration.
Under Personal Supabase (Single Project - Recommended):
- Paste your Project URL into the URL field.
- Paste your anon / public Key into the API Key field.
Under Content Sync Options:
- Sync Chapter Content (Full Text): Default is OFF.
  - Keep OFF if you use Supabase's free cloud tier (recommended).
  - Turn ON only if you self-host Supabase/PostgreSQL (VPS, TrueNAS, local Docker) with sufficient storage and want full novel text backed up offline.
Tap Save Configuration.
Tap Test Connection.
- You should see: ✓ Connection successful! Personal Supabase is ready for sync.

Step 5: Sync to Multiple Devices (Export & Import)

You don't need to type long URLs and keys on every device:

On your configured device, go to Settings → Supabase Configuration.
Tap "Share Config" (or Export). A JSON configuration will be copied to your clipboard.
Send this snippet securely to your other device (via Signal, Telegram Saved Messages, Notes, etc.).
On your second device, open Settings → Supabase Configuration.
Tap "Import Config", paste the JSON snippet, and tap Import.
The URL and Key will be populated automatically! Tap Test Connection to confirm.

Step 6: Daily Check-in & Spirit Stones

Once configured:

Library Sync: In Settings → Unified Sync, tap "Sync Now" (or enable Auto-Sync) to backup your books and reading progress to your private cloud.
Daily Check-in: Open Profile / Webnovel Profile, then tap "Daily Check-in".
- Your streak will increment (+1 day).
- Spirit Stones will be awarded (10 base, 50 at 7-day streak, 200 at 30-day streak).
- Your balance will sync across all connected devices.

Step 7: How Chapter Synchronization Works

IReader includes complete chapter synchronization across all your devices, giving users total control over storage and bandwidth:

1. Two Operational Modes

Mode 1: Metadata-Only Sync (Default & Recommended)

Setting: Sync Chapter Content (Full Text) = OFF (default)
What is synced: Chapter titles, numbers, source order, read/unread status, bookmarks, last read page, and fetch dates.
What is NOT synced: Chapter body text and novel paragraphs are omitted from upload.
Benefits:
- Lightning fast: Libraries with hundreds of novels and 50,000+ chapters sync in under 2 seconds with payloads <1MB.
- Free-tier friendly: Uses less than 5MB of database storage, staying well below Supabase's 500MB free quota.
- Bandwidth safe: Perfect for mobile networks and roaming.

Mode 2: Full Chapter Content Backup (For Self-Hosters)

Setting: Sync Chapter Content (Full Text) = ON
What is synced: Everything in Mode 1, plus full downloaded chapter text/body contents stored directly into public.synced_chapters(content).
Benefits:
- Complete, self-contained offline novel backup in your own database.
- Easy recovery of downloaded chapters when migrating to a new phone or desktop.
- Ideal for self-hosted instances running PostgreSQL on Docker, TrueNAS, unRAID, or a dedicated VPS.

2. Single-Request Atomic Sync (`sync_manifest`)

All book and chapter states are uploaded together into the sync_manifest table as an atomic JSONB document. This prevents partial sync failures or network timeouts caused by firing thousands of individual row inserts. A specialized PostgreSQL GIN index (idx_sync_manifest_gin) ensures fast updates and querying.

3. Inspecting Synced Chapters in Supabase

You can view your synced chapters in relational format directly in the Supabase Dashboard:

In the Supabase sidebar, open Table Editor.
Click on synced_chapters_view.
You will see a live, unpacked relational table of every chapter across your books, including its read status, bookmark flag, last read page, and optional content!

You can also run SQL queries in the SQL Editor:

-- Find all bookmarked chapters across your library
SELECT book_id, name, chapter_number, last_page_read 
FROM public.synced_chapters_view 
WHERE bookmark = true;

-- Count total read chapters per book
SELECT book_id, COUNT(*) AS read_chapters 
FROM public.synced_chapters_view 
WHERE read = true 
GROUP BY book_id;

Troubleshooting & Common Errors

1. `ERROR: 42P13: cannot change return type of existing function`

Cause: PostgreSQL does not allow changing function return types with CREATE OR REPLACE.
Fix: Ensure you run DROP FUNCTION IF EXISTS public.checkin_daily(); before recreating the function (this is already included in Step 3's script).

2. `Connection failed: ✗ ... (Have you run the Setup SQL script?)`

Cause: The app pinged sync_manifest, but the table has not been created yet.
Fix: Open the Supabase SQL Editor and run the script from Step 3.

3. `permission denied for table ...` or RLS Violation

Cause: Row Level Security (RLS) is blocking anonymous single-project access.
Fix: Ensure the RLS policies in Step 3 (Allow public sync_manifest access, etc.) were executed. They allow the anon key of your personal Supabase project to insert and query your library.

4. `column "author" of relation "synced_books" does not exist`

Cause: The synced_books table existed prior to commit 6cc33417 and lacks the new rich metadata columns.
Fix: Running the script in Step 3 executes ALTER TABLE public.synced_books ADD COLUMN IF NOT EXISTS author... to upgrade your existing table without deleting your books.