Docs

Database

Full database schema for CraftlyNow — tables, columns, types, RLS policies and relationships.

Database

CraftlyNow uses Supabase (PostgreSQL). Two core tables power the entire v1 product.

Tables

`courses`

Stores each course created by a user.

create table courses (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null references auth.users(id) on delete cascade,
  title         text not null,
  description   text,
  is_published  boolean not null default false,
  share_token   text unique not null default gen_random_uuid()::text,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);

Column	Type	Notes
`id`	uuid	Primary key, auto-generated
`user_id`	uuid	Owner — references `auth.users`
`title`	text	Required
`description`	text	Optional course-level description
`is_published`	boolean	`true` = visible on public landing page
`share_token`	text	Unique token for private share URLs — auto-generated
`created_at`	timestamptz	Auto-set on insert
`updated_at`	timestamptz	Updated manually on each save

`course_videos`

Stores each video inside a course, including all pulled metadata and user content.

create table course_videos (
  id                  uuid primary key default gen_random_uuid(),
  course_id           uuid not null references courses(id) on delete cascade,
  youtube_url         text not null,
  youtube_id          text not null,
  youtube_title       text,
  youtube_thumbnail   text,
  youtube_description text,
  transcript          text,
  notes               text,
  position            integer not null default 0,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now()
);

Column	Type	Notes
`id`	uuid	Primary key, auto-generated
`course_id`	uuid	References `courses(id)` — cascade delete
`youtube_url`	text	Full YouTube URL as entered by user
`youtube_id`	text	Extracted video ID (e.g. `dQw4w9WgXcQ`)
`youtube_title`	text	Auto-pulled from YouTube
`youtube_thumbnail`	text	Thumbnail URL auto-pulled from YouTube
`youtube_description`	text	Auto-pulled from YouTube
`transcript`	text	Pulled from YouTube — `null` if unavailable
`notes`	text	TipTap editor content (HTML or JSON) — AI-generated or manual
`position`	integer	Order within the course, starts at 0
`created_at`	timestamptz	Auto-set on insert
`updated_at`	timestamptz	Updated on each save

Relationships

auth.users
    │
    └── courses (user_id)
            │
            └── course_videos (course_id)

One user → many courses
One course → many videos
Deleting a course cascades to delete all its videos
Deleting a user cascades to delete all their courses

RLS Policies

Row Level Security is enabled on both tables.

`courses`

alter table courses enable row level security;

-- Anyone can read published courses
create policy "Public read published courses"
  on courses for select
  using (is_published = true);

-- Authenticated users can read all their own courses
create policy "Owner read own courses"
  on courses for select
  using (auth.uid() = user_id);

-- Authenticated users can insert their own courses
create policy "Owner insert courses"
  on courses for insert
  with check (auth.uid() = user_id);

-- Authenticated users can update their own courses
create policy "Owner update courses"
  on courses for update
  using (auth.uid() = user_id);

-- Authenticated users can delete their own courses
create policy "Owner delete courses"
  on courses for delete
  using (auth.uid() = user_id);

`course_videos`

alter table course_videos enable row level security;

-- Anyone can read videos in a published course
create policy "Public read videos in published course"
  on course_videos for select
  using (
    exists (
      select 1 from courses
      where courses.id = course_videos.course_id
      and courses.is_published = true
    )
  );

-- Owners can read videos in their own courses
create policy "Owner read own course videos"
  on course_videos for select
  using (
    exists (
      select 1 from courses
      where courses.id = course_videos.course_id
      and courses.user_id = auth.uid()
    )
  );

-- Owners can insert videos into their own courses
create policy "Owner insert course videos"
  on course_videos for insert
  with check (
    exists (
      select 1 from courses
      where courses.id = course_videos.course_id
      and courses.user_id = auth.uid()
    )
  );

-- Owners can update videos in their own courses
create policy "Owner update course videos"
  on course_videos for update
  using (
    exists (
      select 1 from courses
      where courses.id = course_videos.course_id
      and courses.user_id = auth.uid()
    )
  );

-- Owners can delete videos from their own courses
create policy "Owner delete course videos"
  on course_videos for delete
  using (
    exists (
      select 1 from courses
      where courses.id = course_videos.course_id
      and courses.user_id = auth.uid()
    )
  );

The share_token column enables private sharing without authentication.

A private share URL looks like:

craftlynow.com/c/[share_token]

When a visitor hits this URL, the app queries Supabase directly by share_token — bypassing the is_published RLS policy. This is handled in a server-side API route (Nuxt server route or Supabase edge function) using the service role key, never exposed to the client.

Indexes

-- Speed up lookups by user
create index on courses(user_id);

-- Speed up public listing
create index on courses(is_published);

-- Speed up private share lookups
create index on courses(share_token);

-- Speed up video ordering within a course
create index on course_videos(course_id, position);

Auto-update `updated_at`

create or replace function update_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger set_updated_at_courses
  before update on courses
  for each row execute function update_updated_at();

create trigger set_updated_at_course_videos
  before update on course_videos
  for each row execute function update_updated_at();