schema.prisma•5.49 kB
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
// Article metadata and tracking
model Article {
id Int @id @default(autoincrement())
uuid String @unique // UUID from The News API
title String
description String?
url String
imageUrl String? @map("image_url")
source String
publishedAt DateTime @map("published_at")
categories String // Stored as comma-separated values
language String
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
// User interactions
userArticles UserArticle[]
// Enhanced metadata fields
readCount Int @default(0) @map("read_count")
relevanceScore Float? @map("relevance_score")
sentiment Float? // Sentiment score (-1.0 to 1.0)
topics ArticleTopic[]
entities ArticleEntity[]
@@index([uuid])
@@index([language])
@@index([source])
@@index([publishedAt])
@@map("articles")
}
// User-specific article interactions
model UserArticle {
id Int @id @default(autoincrement())
userId Int @map("user_id")
articleId Int @map("article_id")
isBookmarked Boolean @default(false) @map("is_bookmarked")
isRead Boolean @default(false) @map("is_read")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
readAt DateTime? @map("read_at")
article Article @relation(fields: [articleId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@unique([userId, articleId])
@@index([userId])
@@index([articleId])
@@map("user_articles")
}
// User preferences and auth info
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
passwordHash String @map("password_hash")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
lastLoginAt DateTime? @map("last_login_at")
// Preferences
preferredCategories String? @map("preferred_categories") // Comma-separated
preferredSources String? @map("preferred_sources") // Comma-separated
preferredLanguage String @default("en") @map("preferred_language")
// User interactions
userArticles UserArticle[]
// Saved searches
searches SavedSearch[]
@@map("users")
}
// Named topics extracted from articles
model Topic {
id Int @id @default(autoincrement())
name String @unique
articleCount Int @default(0) @map("article_count")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
articles ArticleTopic[]
@@index([name])
@@map("topics")
}
// Many-to-many relationship between articles and topics
model ArticleTopic {
id Int @id @default(autoincrement())
articleId Int @map("article_id")
topicId Int @map("topic_id")
article Article @relation(fields: [articleId], references: [id], onDelete: Cascade)
topic Topic @relation(fields: [topicId], references: [id], onDelete: Cascade)
confidence Float @default(1.0)
createdAt DateTime @default(now()) @map("created_at")
@@unique([articleId, topicId])
@@index([articleId])
@@index([topicId])
@@map("article_topics")
}
// Named entities (people, organizations, locations) extracted from articles
model Entity {
id Int @id @default(autoincrement())
name String @unique
type String // PERSON, ORGANIZATION, LOCATION, etc.
articleCount Int @default(0) @map("article_count")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
articles ArticleEntity[]
@@index([name])
@@index([type])
@@map("entities")
}
// Many-to-many relationship between articles and entities
model ArticleEntity {
id Int @id @default(autoincrement())
articleId Int @map("article_id")
entityId Int @map("entity_id")
article Article @relation(fields: [articleId], references: [id], onDelete: Cascade)
entity Entity @relation(fields: [entityId], references: [id], onDelete: Cascade)
frequency Int @default(1)
createdAt DateTime @default(now()) @map("created_at")
@@unique([articleId, entityId])
@@index([articleId])
@@index([entityId])
@@map("article_entities")
}
// User saved searches
model SavedSearch {
id Int @id @default(autoincrement())
userId Int @map("user_id")
name String
query String
parameters String // JSON string of search parameters
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
lastRunAt DateTime? @map("last_run_at")
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
@@index([userId])
@@map("saved_searches")
}