Database Schema

Complete reference for EpiNeko database tables, columns, and relationships

Overview

EpiNeko uses PostgreSQL via Supabase with two main tables: profiles for user information and user_library for tracking anime in user libraries.

The complete schema is defined in supabase/migrations/20260218_initial_schema.sql:1

Tables

profiles

Stores user profile information. Automatically populated when a new user signs up via the handle_new_user() trigger.

create table public.profiles (
  id uuid references auth.users on delete cascade not null primary key,
  updated_at timestamp with time zone,
  username text unique,
  full_name text,
  avatar_url text,
  website text,

  constraint username_length check (char_length(username) >= 3)
);

Columns

id · uuid · required

Primary key. References auth.users.id. Automatically deleted when user is deleted (cascade).

updated_at · timestamp with time zone

Timestamp of last profile update

username · text

Unique username for the user. Must be at least 3 characters long.

full_name · text

User’s full name or display name

avatar_url · text

URL to user’s avatar/profile picture

website · text

User’s personal website or social media link

Constraints

Constraint	Description
PRIMARY KEY (id)	UUID from auth.users
UNIQUE (username)	Usernames must be unique across all users
username_length	Username must be at least 3 characters
FOREIGN KEY (id) REFERENCES auth.users ON DELETE CASCADE	Profile deleted when user deleted

Example Queries

Select Profile

SELECT * FROM profiles
WHERE id = 'user-uuid-here';

---

user_library

Stores anime in each user’s personal library with status, score, and progress tracking.

create table public.user_library (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles(id) on delete cascade not null,
  anime_id_jikan integer not null,
  title text not null,
  image_url text,
  status public.library_status default 'watching' not null,
  score integer check (score >= 0 and score <= 10),
  episodes_watched integer default 0,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  updated_at timestamp with time zone default timezone('utc'::text, now()) not null,

  unique(user_id, anime_id_jikan)
);

Columns

id · uuid · required

Primary key. Automatically generated using gen_random_uuid().

user_id · uuid · required

Foreign key to profiles.id. The owner of this library item.

anime_id_jikan · integer · required

The MyAnimeList ID from Jikan API. Used to identify the anime.

title · text · required

Cached anime title for quick display without API calls.

image_url · text

Cached image URL for the anime poster/cover.

status · library_status · default: "watching" · required

Current status: watching, completed, dropped, or plan_to_watch.

score · integer

User’s rating from 0-10. Optional.

episodes_watched · integer · default: 0

Number of episodes the user has watched.

created_at · timestamp with time zone · required

When the anime was added to the library. Defaults to current UTC time.

updated_at · timestamp with time zone · required

Last time the library item was modified. Defaults to current UTC time.

library_status Enum

create type public.library_status as enum (
  'watching', 
  'completed', 
  'dropped', 
  'plan_to_watch'
);

watching · enum

Currently watching this anime

completed · enum

Finished watching this anime

dropped · enum

Started but stopped watching

plan_to_watch · enum

Intend to watch in the future

Constraints

Constraint	Description
PRIMARY KEY (id)	UUID primary key
FOREIGN KEY (user_id) REFERENCES profiles(id) ON DELETE CASCADE	Library items deleted when user deleted
UNIQUE (user_id, anime_id_jikan)	User cannot have duplicate anime in library
CHECK (score >= 0 AND score <= 10)	Score must be between 0 and 10

Indexes

The schema should include these indexes for optimal performance:

-- Index for user queries (most common)
CREATE INDEX idx_user_library_user_id ON user_library(user_id);

-- Index for anime lookups
CREATE INDEX idx_user_library_anime_id ON user_library(anime_id_jikan);

-- Index for status filtering
CREATE INDEX idx_user_library_status ON user_library(status);

-- Index for ordering by update time
CREATE INDEX idx_user_library_updated_at ON user_library(updated_at DESC);

Example Queries

Get User's Library

SELECT * FROM user_library
WHERE user_id = 'user-uuid-here'
ORDER BY updated_at DESC;

Relationships

Relationship Details

Show auth.users → profiles (One-to-One)

Each authenticated user has exactly one profile. The profile is automatically created by the handle_new_user() trigger when a user signs up.

-- Foreign key constraint
id uuid references auth.users on delete cascade

When a user is deleted from auth.users, their profile is automatically deleted (cascade).

Show profiles → user_library (One-to-Many)

Each profile can have many library items (anime). A user can track multiple anime in their library.

-- Foreign key constraint
user_id uuid references public.profiles(id) on delete cascade

When a profile is deleted, all their library items are automatically deleted (cascade).

Database Triggers

handle_new_user()

Automatically creates a profile when a new user signs up.

-- supabase/migrations/20260218_initial_schema.sql:26
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, full_name, username, avatar_url)
  values (
    new.id, 
    new.raw_user_meta_data->>'full_name', 
    new.raw_user_meta_data->>'username',
    new.raw_user_meta_data->>'avatar_url'
  );
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

This trigger extracts user metadata from the signup form and populates the profile automatically. No manual profile creation needed!

Security

All tables have Row Level Security (RLS) enabled. See Row Level Security for details on policies.

-- Enable RLS
alter table public.profiles enable row level security;
alter table public.user_library enable row level security;

Best Practices

Show 1. Always Use Transactions for Multiple Operations

BEGIN;
  UPDATE user_library 
  SET episodes_watched = episodes_watched + 1 
  WHERE id = 'item-uuid';
  
  UPDATE user_library 
  SET status = 'completed' 
  WHERE id = 'item-uuid' 
    AND episodes_watched >= total_episodes;
COMMIT;

Show 2. Update updated_at on Modifications

UPDATE user_library
SET 
  score = 10,
  updated_at = NOW()  -- Always update this
WHERE id = 'item-uuid';

Show 3. Use Unique Constraint to Prevent Duplicates

The unique(user_id, anime_id_jikan) constraint prevents users from adding the same anime twice:

// Use upsert to handle duplicates gracefully
const { data, error } = await supabase
  .from('user_library')
  .upsert({
    user_id: user.id,
    anime_id_jikan: 5114,
    title: 'FMA: Brotherhood',
    status: 'watching'
  }, {
    onConflict: 'user_id,anime_id_jikan'
  });

Show 4. Cache Anime Data

Store title and image_url in the library to avoid repeated API calls to Jikan:

const { data, error } = await supabase
  .from('user_library')
  .insert({
    anime_id_jikan: anime.mal_id,
    title: anime.title,  // Cache title
    image_url: anime.images.jpg.image_url,  // Cache image
    status: 'watching'
  });

Migration

The complete schema is defined in a single migration file:

supabase/migrations/20260218_initial_schema.sql

To apply the migration:

# Local development
supabase db reset

# Production
supabase db push

Related Resources

Row Level Security

Learn about RLS policies

Supabase Integration

How to use Supabase clients

Library Service

High-level library operations

Supabase CLI

Managing migrations