Initialize D1 Database

Initialize the Database

Open the Cloudflare console, select Workers & Pages -> D1 -> Create, and click to create the database.

Once created, we can see the D1 database in the Cloudflare console.

Open the Console tab, input the following content, and click Execute to run.

Initialize User Table

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  user_type TEXT NOT NULL DEFAULT 'user',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CHECK(user_type IN ('user', 'admin'))
);

Create Admin Account (Default username and password are both "admin", can be changed later in the backend)

INSERT INTO users (username, email, password_hash, user_type) 
VALUES ('admin', '[email protected]', '$2a$10$OaC7gcRwbiyw7fe6/tmZAeDZ0j7q.U0xDEpEzpvVXgQofXdIPBgpi', 'admin');

Initialize Image Information Table

CREATE TABLE images (
    id TEXT PRIMARY KEY,
    user_id INTEGER,
    filename TEXT,
    mime_type TEXT,
    kv_key TEXT,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add index for user_id to speed up image queries filtered by user
CREATE INDEX IF NOT EXISTS idx_images_user_id ON images(user_id);

-- Add index for upload_date to speed up queries sorted by upload date
CREATE INDEX IF NOT EXISTS idx_images_upload_date ON images(upload_date);

Create Site Configuration Table and Add Basic Configuration

CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT NOT NULL UNIQUE, 
    value TEXT NOT NULL,
    value_type TEXT NOT NULL DEFAULT 'string',
    description TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO settings (key, value, value_type, description) 
VALUES ('enable_register', 'true', 'boolean', 'Whether to enable user registration');

INSERT INTO settings (key, value, value_type, description) 
VALUES ('expiration_time', 1, 'integer', 'Token expiration time');

INSERT INTO settings (key, value, value_type, description) 
VALUES ('upload_require_auth', 'true', 'boolean', 'Must be logged in to upload');

The final result should look like the following image: