38fe92d695
- модели Track / TrackPlay / TrackLike (+ миграция add_charts) - сбор проигрываний в now-playing-поллере: при смене трека на станции пишется TrackPlay (нормализация artist+song -> Track), fire-and-forget обогащение через MusicBrainz (album/releaseDate) - ChartsModule: GET /charts/tracks (период day/week/month/all, ранг, тренд, проигрывания, станции, лайки), GET /charts/tracks/:id (метрики, таймлайны популярности и лайков по дням, топ станций, isLiked), POST/DELETE like - OptionalAuthGuard для публичной детальной страницы с опц. userId
69 lines
2.2 KiB
SQL
69 lines
2.2 KiB
SQL
-- CreateTable: tracks
|
|
CREATE TABLE "tracks" (
|
|
"id" TEXT NOT NULL,
|
|
"norm_key" TEXT NOT NULL,
|
|
"artist" TEXT NOT NULL,
|
|
"song" TEXT NOT NULL,
|
|
"cover_url" TEXT,
|
|
"album" TEXT,
|
|
"release_date" TIMESTAMPTZ,
|
|
"first_seen_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
"enriched_at" TIMESTAMPTZ,
|
|
|
|
CONSTRAINT "tracks_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable: track_plays
|
|
CREATE TABLE "track_plays" (
|
|
"id" TEXT NOT NULL,
|
|
"track_id" TEXT NOT NULL,
|
|
"station_id" TEXT NOT NULL,
|
|
"played_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT "track_plays_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable: track_likes
|
|
CREATE TABLE "track_likes" (
|
|
"id" TEXT NOT NULL,
|
|
"track_id" TEXT NOT NULL,
|
|
"user_id" TEXT NOT NULL,
|
|
"created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT "track_likes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "tracks_norm_key_key" ON "tracks"("norm_key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "track_plays_track_id_played_at_idx" ON "track_plays"("track_id", "played_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "track_plays_played_at_idx" ON "track_plays"("played_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "track_plays_station_id_idx" ON "track_plays"("station_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "track_likes_track_id_user_id_key" ON "track_likes"("track_id", "user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "track_likes_track_id_idx" ON "track_likes"("track_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "track_plays" ADD CONSTRAINT "track_plays_track_id_fkey"
|
|
FOREIGN KEY ("track_id") REFERENCES "tracks"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "track_plays" ADD CONSTRAINT "track_plays_station_id_fkey"
|
|
FOREIGN KEY ("station_id") REFERENCES "stations"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "track_likes" ADD CONSTRAINT "track_likes_track_id_fkey"
|
|
FOREIGN KEY ("track_id") REFERENCES "tracks"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "track_likes" ADD CONSTRAINT "track_likes_user_id_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|