refactor(proxy): move manga chapter counts behind indexed cache

1 files changed, 39 insertions, 0 deletions

diff --git a/supabase/mangadex.sql b/supabase/mangadex.sql
new file mode 100644
index 00000000..79ea0408
--- /dev/null
+++ b/supabase/mangadex.sql
@@ -0,0 +1,39 @@
+create table if not exists public.mangadex_manga_index (
+  anilist_id bigint primary key,
+  mangadex_id uuid not null,
+  latest_en_chapter_text text,
+  latest_en_chapter_number double precision,
+  latest_en_volume_text text,
+  latest_en_chapter_id uuid,
+  latest_chapter_updated_at timestamp with time zone,
+  last_seen_at timestamp with time zone default (now() at time zone 'utc'::text) not null,
+  last_indexed_at timestamp with time zone,
+  is_releasing boolean default true not null,
+  created_at timestamp with time zone default (now() at time zone 'utc'::text) not null,
+  updated_at timestamp with time zone default (now() at time zone 'utc'::text) not null
+);
+
+create index if not exists mangadex_manga_index_mangadex_id_idx
+  on public.mangadex_manga_index (mangadex_id);
+
+create index if not exists mangadex_manga_index_last_indexed_at_idx
+  on public.mangadex_manga_index (last_indexed_at);
+
+create table if not exists public.mangadex_resolution_failures (
+  anilist_id bigint primary key,
+  last_attempted_at timestamp with time zone default (now() at time zone 'utc'::text) not null,
+  updated_at timestamp with time zone default (now() at time zone 'utc'::text) not null
+);
+
+create index if not exists mangadex_resolution_failures_last_attempted_at_idx
+  on public.mangadex_resolution_failures (last_attempted_at);
+
+create table if not exists public.mangadex_sync_state (
+  name text primary key,
+  cursor_updated_at timestamp with time zone not null,
+  updated_at timestamp with time zone default (now() at time zone 'utc'::text) not null
+);
+
+insert into public.mangadex_sync_state (name, cursor_updated_at)
+values ('chapter_en', now() at time zone 'utc'::text)
+on conflict (name) do nothing;