Database Schema

The gallery uses Turso (LibSQL/SQLite) with Drizzle ORM.

Tables Overview

erDiagram
    users ||--o{ sessions : has
    users ||--o{ oauth_accounts : has
    users ||--o{ themes : submits

    users {
        text id PK
        text email UK
        text password_hash
        text name
        text avatar_url
        int created_at
    }

    sessions {
        text id PK
        text user_id FK
        int expires_at
    }

    oauth_accounts {
        text id PK
        text user_id FK
        text provider
        text provider_user_id
        text provider_username
        text access_token
        int created_at
    }

    themes {
        text id PK
        text name
        text description
        text author_id FK
        text author_name
        text repo_url UK
        text demo_url
        text preview_image
        int stars
        text tags
        text status
        int created_at
        int updated_at
    }

Table: users

User accounts for authentication.

Column Type Description

id

TEXT PK

UUID primary key

email

TEXT UNIQUE

Email address (nullable for OAuth-only)

password_hash

TEXT

Argon2 hash (null for OAuth users)

name

TEXT NOT NULL

Display name

avatar_url

TEXT

Profile picture URL

created_at

INTEGER

Unix timestamp

Table: sessions

Active user sessions for Lucia auth.

Column Type Description

id

TEXT PK

Session ID (random UUID)

user_id

TEXT FK

References users.id

expires_at

INTEGER NOT NULL

Expiration timestamp

Table: oauth_accounts

Links OAuth providers to user accounts.

Column Type Description

id

TEXT PK

UUID primary key

user_id

TEXT FK

References users.id

provider

TEXT NOT NULL

'github', 'gitlab', or 'google'

provider_user_id

TEXT NOT NULL

User ID from the provider

provider_username

TEXT

Username from the provider

access_token

TEXT

OAuth access token

created_at

INTEGER

Unix timestamp

Table: themes

Submitted theme registry.

Column Type Description

id

TEXT PK

UUID primary key

name

TEXT NOT NULL

Theme display name

description

TEXT NOT NULL

Theme description

author_id

TEXT FK

References users.id (nullable)

author_name

TEXT NOT NULL

Denormalized author name

repo_url

TEXT NOT NULL UNIQUE

GitHub repository URL

demo_url

TEXT

Live demo URL

preview_image

TEXT

Screenshot URL

stars

INTEGER DEFAULT 0

GitHub star count

tags

TEXT DEFAULT '[]'

JSON array of tags

status

TEXT DEFAULT 'pending'

'pending', 'approved', 'rejected'

created_at

INTEGER

Submission timestamp

updated_at

INTEGER

Last update timestamp

Drizzle Schema

The schema is defined in src/db/schema.ts:

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').unique(),
  passwordHash: text('password_hash'),
  name: text('name').notNull(),
  avatarUrl: text('avatar_url'),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
});

// ... other tables

Migrations

Push schema changes to database:

npx drizzle-kit push

Generate migration files:

npx drizzle-kit generate

Queries

Fetch Approved Themes

const themes = await db
  .select()
  .from(themes)
  .where(eq(themes.status, 'approved'))
  .orderBy(desc(themes.stars));

Find User by Email

const user = await db.query.users.findFirst({
  where: eq(users.email, email),
});

Create Theme

await db.insert(themes).values({
  id: generateId(),
  name: 'My Theme',
  description: 'A beautiful theme',
  authorId: user.id,
  authorName: user.name,
  repoUrl: 'https://github.com/...',
  status: 'pending',
});