chrisandClaude Opus 5.5
6791b62d87
- Fastify API with cookie (web) and bearer (mobile) sessions, follows, pinned friends, featured songs, account deletion, Redis-backed rate limits - Providers: Spotify (OAuth), Last.fm, ListenBrainz, Plex (PIN flow and server discovery), Jellyfin (token auth, revoked on disconnect) - Worker polls on an adaptive schedule in a Redis sorted set, so polling scales horizontally and API usage tracks whether anyone is watching - Track resolver links songs across services via ISRC (Deezer, Spotify), iTunes search and song.link - Realtime updates over WebSocket fan out through Redis pub/sub - User-supplied server URLs are restricted to public addresses, checked both up front and at connect time to defeat DNS rebinding Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01MzFywujuH9TiuXbr9vjoX8
101 lines
5.2 KiB
SQL
101 lines
5.2 KiB
SQL
CREATE TABLE "connections" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"provider" text NOT NULL,
|
|
"external_user_id" text NOT NULL,
|
|
"display_name" text,
|
|
"access_token_enc" text,
|
|
"refresh_token_enc" text,
|
|
"token_expires_at" timestamp with time zone,
|
|
"status" text DEFAULT 'active' NOT NULL,
|
|
"last_error" text,
|
|
"last_polled_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "featured_songs" (
|
|
"user_id" uuid PRIMARY KEY NOT NULL,
|
|
"track_id" uuid NOT NULL,
|
|
"comment" varchar(280),
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "follows" (
|
|
"follower_id" uuid NOT NULL,
|
|
"followee_id" uuid NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "follows_follower_id_followee_id_pk" PRIMARY KEY("follower_id","followee_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "now_playing" (
|
|
"user_id" uuid PRIMARY KEY NOT NULL,
|
|
"track_id" uuid NOT NULL,
|
|
"source" text NOT NULL,
|
|
"is_playing" boolean NOT NULL,
|
|
"progress_ms" integer,
|
|
"started_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "sessions" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"expires_at" timestamp with time zone NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "track_links" (
|
|
"track_id" uuid NOT NULL,
|
|
"service" text NOT NULL,
|
|
"external_id" text,
|
|
"url" text NOT NULL,
|
|
"match_type" text NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "track_links_track_id_service_pk" PRIMARY KEY("track_id","service")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "tracks" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"isrc" varchar(12),
|
|
"match_key" text NOT NULL,
|
|
"title" text NOT NULL,
|
|
"artist" text NOT NULL,
|
|
"album" text,
|
|
"duration_ms" integer,
|
|
"artwork_url" text,
|
|
"links_resolved_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "users" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"username" varchar(32) NOT NULL,
|
|
"email" varchar(320) NOT NULL,
|
|
"password_hash" text NOT NULL,
|
|
"display_name" varchar(64),
|
|
"bio" varchar(280),
|
|
"avatar_url" varchar(500),
|
|
"preferred_service" text,
|
|
"plan" text DEFAULT 'free' NOT NULL,
|
|
"plan_expires_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
CONSTRAINT "users_username_unique" UNIQUE("username"),
|
|
CONSTRAINT "users_email_unique" UNIQUE("email")
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "connections" ADD CONSTRAINT "connections_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "featured_songs" ADD CONSTRAINT "featured_songs_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "featured_songs" ADD CONSTRAINT "featured_songs_track_id_tracks_id_fk" FOREIGN KEY ("track_id") REFERENCES "public"."tracks"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "follows" ADD CONSTRAINT "follows_follower_id_users_id_fk" FOREIGN KEY ("follower_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "follows" ADD CONSTRAINT "follows_followee_id_users_id_fk" FOREIGN KEY ("followee_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "now_playing" ADD CONSTRAINT "now_playing_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "now_playing" ADD CONSTRAINT "now_playing_track_id_tracks_id_fk" FOREIGN KEY ("track_id") REFERENCES "public"."tracks"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "track_links" ADD CONSTRAINT "track_links_track_id_tracks_id_fk" FOREIGN KEY ("track_id") REFERENCES "public"."tracks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "connections_user_provider_idx" ON "connections" USING btree ("user_id","provider");--> statement-breakpoint
|
|
CREATE INDEX "follows_followee_idx" ON "follows" USING btree ("followee_id");--> statement-breakpoint
|
|
CREATE INDEX "now_playing_track_idx" ON "now_playing" USING btree ("track_id");--> statement-breakpoint
|
|
CREATE INDEX "sessions_user_idx" ON "sessions" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "track_links_external_idx" ON "track_links" USING btree ("service","external_id");--> statement-breakpoint
|
|
CREATE INDEX "tracks_isrc_idx" ON "tracks" USING btree ("isrc");--> statement-breakpoint
|
|
CREATE INDEX "tracks_match_key_idx" ON "tracks" USING btree ("match_key"); |