News Aggregator API

-- CreateTable CREATE TABLE "articles" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "uuid" TEXT NOT NULL, "title" TEXT NOT NULL, "description" TEXT, "url" TEXT NOT NULL, "image_url" TEXT, "source" TEXT NOT NULL, "published_at" DATETIME NOT NULL, "categories" TEXT NOT NULL, "language" TEXT NOT NULL, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NOT NULL, "read_count" INTEGER NOT NULL DEFAULT 0, "relevance_score" REAL, "sentiment" REAL ); -- CreateTable CREATE TABLE "user_articles" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "user_id" INTEGER NOT NULL, "article_id" INTEGER NOT NULL, "is_bookmarked" BOOLEAN NOT NULL DEFAULT false, "is_read" BOOLEAN NOT NULL DEFAULT false, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NOT NULL, "read_at" DATETIME, CONSTRAINT "user_articles_article_id_fkey" FOREIGN KEY ("article_id") REFERENCES "articles" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "user_articles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); -- CreateTable CREATE TABLE "users" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "email" TEXT NOT NULL, "name" TEXT, "password_hash" TEXT NOT NULL, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NOT NULL, "last_login_at" DATETIME, "preferred_categories" TEXT, "preferred_sources" TEXT, "preferred_language" TEXT NOT NULL DEFAULT 'en' ); -- CreateTable CREATE TABLE "topics" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "article_count" INTEGER NOT NULL DEFAULT 0, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NOT NULL ); -- CreateTable CREATE TABLE "article_topics" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "article_id" INTEGER NOT NULL, "topic_id" INTEGER NOT NULL, "confidence" REAL NOT NULL DEFAULT 1.0, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "article_topics_article_id_fkey" FOREIGN KEY ("article_id") REFERENCES "articles" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "article_topics_topic_id_fkey" FOREIGN KEY ("topic_id") REFERENCES "topics" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); -- CreateTable CREATE TABLE "entities" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" TEXT NOT NULL, "type" TEXT NOT NULL, "article_count" INTEGER NOT NULL DEFAULT 0, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NOT NULL ); -- CreateTable CREATE TABLE "article_entities" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "article_id" INTEGER NOT NULL, "entity_id" INTEGER NOT NULL, "frequency" INTEGER NOT NULL DEFAULT 1, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "article_entities_article_id_fkey" FOREIGN KEY ("article_id") REFERENCES "articles" ("id") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "article_entities_entity_id_fkey" FOREIGN KEY ("entity_id") REFERENCES "entities" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); -- CreateTable CREATE TABLE "saved_searches" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "user_id" INTEGER NOT NULL, "name" TEXT NOT NULL, "query" TEXT NOT NULL, "parameters" TEXT NOT NULL, "created_at" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" DATETIME NOT NULL, "last_run_at" DATETIME, CONSTRAINT "saved_searches_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); -- CreateIndex CREATE UNIQUE INDEX "articles_uuid_key" ON "articles"("uuid"); -- CreateIndex CREATE INDEX "articles_uuid_idx" ON "articles"("uuid"); -- CreateIndex CREATE INDEX "articles_language_idx" ON "articles"("language"); -- CreateIndex CREATE INDEX "articles_source_idx" ON "articles"("source"); -- CreateIndex CREATE INDEX "articles_published_at_idx" ON "articles"("published_at"); -- CreateIndex CREATE INDEX "user_articles_user_id_idx" ON "user_articles"("user_id"); -- CreateIndex CREATE INDEX "user_articles_article_id_idx" ON "user_articles"("article_id"); -- CreateIndex CREATE UNIQUE INDEX "user_articles_user_id_article_id_key" ON "user_articles"("user_id", "article_id"); -- CreateIndex CREATE UNIQUE INDEX "users_email_key" ON "users"("email"); -- CreateIndex CREATE UNIQUE INDEX "topics_name_key" ON "topics"("name"); -- CreateIndex CREATE INDEX "topics_name_idx" ON "topics"("name"); -- CreateIndex CREATE INDEX "article_topics_article_id_idx" ON "article_topics"("article_id"); -- CreateIndex CREATE INDEX "article_topics_topic_id_idx" ON "article_topics"("topic_id"); -- CreateIndex CREATE UNIQUE INDEX "article_topics_article_id_topic_id_key" ON "article_topics"("article_id", "topic_id"); -- CreateIndex CREATE UNIQUE INDEX "entities_name_key" ON "entities"("name"); -- CreateIndex CREATE INDEX "entities_name_idx" ON "entities"("name"); -- CreateIndex CREATE INDEX "entities_type_idx" ON "entities"("type"); -- CreateIndex CREATE INDEX "article_entities_article_id_idx" ON "article_entities"("article_id"); -- CreateIndex CREATE INDEX "article_entities_entity_id_idx" ON "article_entities"("entity_id"); -- CreateIndex CREATE UNIQUE INDEX "article_entities_article_id_entity_id_key" ON "article_entities"("article_id", "entity_id"); -- CreateIndex CREATE INDEX "saved_searches_user_id_idx" ON "saved_searches"("user_id");