> ## Documentation Index
> Fetch the complete documentation index at: https://devperez08-platform-list-anime-54.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

<Note>
  The complete schema is defined in `supabase/migrations/20260218_initial_schema.sql:1`
</Note>

## Tables

### profiles

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

```sql theme={null}
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

<ParamField path="id" type="uuid" required>
  Primary key. References `auth.users.id`. Automatically deleted when user is deleted (cascade).
</ParamField>

<ParamField path="updated_at" type="timestamp with time zone">
  Timestamp of last profile update
</ParamField>

<ParamField path="username" type="text" unique>
  Unique username for the user. Must be at least 3 characters long.
</ParamField>

<ParamField path="full_name" type="text">
  User's full name or display name
</ParamField>

<ParamField path="avatar_url" type="text">
  URL to user's avatar/profile picture
</ParamField>

<ParamField path="website" type="text">
  User's personal website or social media link
</ParamField>

#### 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

<CodeGroup>
  ```sql Select Profile theme={null}
  SELECT * FROM profiles
  WHERE id = 'user-uuid-here';
  ```

  ```sql Update Profile theme={null}
  UPDATE profiles
  SET 
    username = 'newusername',
    full_name = 'New Name',
    updated_at = NOW()
  WHERE id = 'user-uuid-here';
  ```

  ```typescript TypeScript (Supabase Client) theme={null}
  // Get current user's profile
  const { data: profile, error } = await supabase
    .from('profiles')
    .select('*')
    .eq('id', user.id)
    .single();

  // Update profile
  const { error } = await supabase
    .from('profiles')
    .update({
      username: 'newusername',
      full_name: 'New Name',
      updated_at: new Date().toISOString()
    })
    .eq('id', user.id);
  ```
</CodeGroup>

***

### user\_library

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

```sql theme={null}
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

<ParamField path="id" type="uuid" required>
  Primary key. Automatically generated using `gen_random_uuid()`.
</ParamField>

<ParamField path="user_id" type="uuid" required>
  Foreign key to `profiles.id`. The owner of this library item.
</ParamField>

<ParamField path="anime_id_jikan" type="integer" required>
  The MyAnimeList ID from Jikan API. Used to identify the anime.
</ParamField>

<ParamField path="title" type="text" required>
  Cached anime title for quick display without API calls.
</ParamField>

<ParamField path="image_url" type="text">
  Cached image URL for the anime poster/cover.
</ParamField>

<ParamField path="status" type="library_status" default="watching" required>
  Current status: `watching`, `completed`, `dropped`, or `plan_to_watch`.
</ParamField>

<ParamField path="score" type="integer">
  User's rating from 0-10. Optional.
</ParamField>

<ParamField path="episodes_watched" type="integer" default={0}>
  Number of episodes the user has watched.
</ParamField>

<ParamField path="created_at" type="timestamp with time zone" required>
  When the anime was added to the library. Defaults to current UTC time.
</ParamField>

<ParamField path="updated_at" type="timestamp with time zone" required>
  Last time the library item was modified. Defaults to current UTC time.
</ParamField>

#### library\_status Enum

```sql theme={null}
create type public.library_status as enum (
  'watching', 
  'completed', 
  'dropped', 
  'plan_to_watch'
);
```

<ResponseField name="watching" type="enum">
  Currently watching this anime
</ResponseField>

<ResponseField name="completed" type="enum">
  Finished watching this anime
</ResponseField>

<ResponseField name="dropped" type="enum">
  Started but stopped watching
</ResponseField>

<ResponseField name="plan_to_watch" type="enum">
  Intend to watch in the future
</ResponseField>

#### 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:

```sql theme={null}
-- 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

<CodeGroup>
  ```sql Get User's Library theme={null}
  SELECT * FROM user_library
  WHERE user_id = 'user-uuid-here'
  ORDER BY updated_at DESC;
  ```

  ```sql Get Watching Anime theme={null}
  SELECT * FROM user_library
  WHERE user_id = 'user-uuid-here'
    AND status = 'watching'
  ORDER BY updated_at DESC;
  ```

  ```sql Add to Library theme={null}
  INSERT INTO user_library (
    user_id,
    anime_id_jikan,
    title,
    image_url,
    status
  ) VALUES (
    'user-uuid-here',
    5114,
    'Fullmetal Alchemist: Brotherhood',
    'https://cdn.myanimelist.net/images/anime/...',
    'watching'
  ) RETURNING *;
  ```

  ```sql Update Progress theme={null}
  UPDATE user_library
  SET 
    episodes_watched = episodes_watched + 1,
    updated_at = NOW()
  WHERE user_id = 'user-uuid-here'
    AND anime_id_jikan = 5114
  RETURNING *;
  ```

  ```sql Complete Anime theme={null}
  UPDATE user_library
  SET 
    status = 'completed',
    score = 10,
    updated_at = NOW()
  WHERE user_id = 'user-uuid-here'
    AND anime_id_jikan = 5114;
  ```

  ```sql Remove from Library theme={null}
  DELETE FROM user_library
  WHERE user_id = 'user-uuid-here'
    AND anime_id_jikan = 5114;
  ```

  ```typescript TypeScript (Supabase Client) theme={null}
  // Add to library
  const { data, error } = await supabase
    .from('user_library')
    .insert({
      user_id: user.id,
      anime_id_jikan: 5114,
      title: 'Fullmetal Alchemist: Brotherhood',
      image_url: 'https://...',
      status: 'watching'
    })
    .select()
    .single();

  // Get library with filtering
  const { data: library } = await supabase
    .from('user_library')
    .select('*')
    .eq('status', 'watching')
    .order('updated_at', { ascending: false });

  // Update item
  const { data } = await supabase
    .from('user_library')
    .update({ 
      episodes_watched: 10,
      updated_at: new Date().toISOString()
    })
    .eq('user_id', user.id)
    .eq('anime_id_jikan', 5114)
    .select()
    .single();
  ```
</CodeGroup>

## Relationships

```mermaid theme={null}
erDiagram
    auth_users ||--|| profiles : "one-to-one"
    profiles ||--o{ user_library : "one-to-many"
    
    auth_users {
        uuid id PK
        text email
        timestamp created_at
    }
    
    profiles {
        uuid id PK,FK
        text username UK
        text full_name
        text avatar_url
        text website
        timestamp updated_at
    }
    
    user_library {
        uuid id PK
        uuid user_id FK
        integer anime_id_jikan
        text title
        text image_url
        library_status status
        integer score
        integer episodes_watched
        timestamp created_at
        timestamp updated_at
    }
```

### Relationship Details

<Expandable title="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.

  ```sql theme={null}
  -- 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).
</Expandable>

<Expandable title="profiles → user_library (One-to-Many)">
  Each profile can have many library items (anime). A user can track multiple anime in their library.

  ```sql theme={null}
  -- 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).
</Expandable>

## Database Triggers

### handle\_new\_user()

Automatically creates a profile when a new user signs up.

```sql theme={null}
-- 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();
```

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

## Security

All tables have Row Level Security (RLS) enabled. See [Row Level Security](/integration/row-level-security) for details on policies.

```sql theme={null}
-- Enable RLS
alter table public.profiles enable row level security;
alter table public.user_library enable row level security;
```

## Best Practices

<Expandable title="1. Always Use Transactions for Multiple Operations">
  ```sql theme={null}
  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;
  ```
</Expandable>

<Expandable title="2. Update updated_at on Modifications">
  ```sql theme={null}
  UPDATE user_library
  SET 
    score = 10,
    updated_at = NOW()  -- Always update this
  WHERE id = 'item-uuid';
  ```
</Expandable>

<Expandable title="3. Use Unique Constraint to Prevent Duplicates">
  The `unique(user_id, anime_id_jikan)` constraint prevents users from adding the same anime twice:

  ```typescript theme={null}
  // 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'
    });
  ```
</Expandable>

<Expandable title="4. Cache Anime Data">
  Store `title` and `image_url` in the library to avoid repeated API calls to Jikan:

  ```typescript theme={null}
  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'
    });
  ```
</Expandable>

## Migration

The complete schema is defined in a single migration file:

```bash theme={null}
supabase/migrations/20260218_initial_schema.sql
```

To apply the migration:

```bash theme={null}
# Local development
supabase db reset

# Production
supabase db push
```

## Related Resources

<CardGroup cols={2}>
  <Card title="Row Level Security" icon="shield" href="/integration/row-level-security">
    Learn about RLS policies
  </Card>

  <Card title="Supabase Integration" icon="database" href="/integration/supabase">
    How to use Supabase clients
  </Card>

  <Card title="Library Service" icon="code" href="/development/services">
    High-level library operations
  </Card>

  <Card title="Supabase CLI" icon="terminal" href="https://supabase.com/docs/guides/cli">
    Managing migrations
  </Card>
</CardGroup>