556
Bn
bees-nest / files
bees-nest / src / db / schema.ts
3.2 KB
1import { relations, sql } from "drizzle-orm"; 2import { 3 index, 4 integer, 5 pgEnum, 6 pgTable, 7 serial, 8 text, 9 timestamp, 10 uniqueIndex, 11} from "drizzle-orm/pg-core"; 12 13export const urlStatusEnum = pgEnum("url_status", [ 14 "pending", 15 "processing", 16 "completed", 17 "failed", 18]); 19 20export const domains = pgTable("domains", { 21 id: serial("id").primaryKey(), 22 domain: text("domain").notNull().unique(), 23 robotsTxt: text("robots_txt"), 24 lastCrawledAt: timestamp("last_crawled_at"), 25 createdAt: timestamp("created_at").defaultNow(), 26}); 27 28export const urls = pgTable( 29 "urls", 30 { 31 id: serial("id").primaryKey(), 32 url: text("url").notNull().unique(), 33 domainId: integer("domain_id").references(() => domains.id), 34 status: urlStatusEnum("status").default("pending").notNull(), 35 priority: integer("priority").default(10).notNull(), 36 depth: integer("depth").default(0).notNull(), 37 errorMessage: text("error_message"), 38 nextRetryAt: timestamp("next_retry_at"), 39 createdAt: timestamp("created_at").defaultNow(), 40 updatedAt: timestamp("updated_at").defaultNow(), 41 }, 42 (t) => ({ 43 statusIdx: index("status_idx").on(t.status), 44 priorityIdx: index("priority_idx").on(t.priority), 45 }), 46); 47 48export const pages = pgTable( 49 "pages", 50 { 51 id: serial("id").primaryKey(), 52 urlId: integer("url_id") 53 .references(() => urls.id) 54 .notNull(), 55 title: text("title"), 56 description: text("description"), 57 siteName: text("site_name"), 58 image: text("image"), 59 content: text("content"), // Main text content for search 60 createdAt: timestamp("created_at").defaultNow(), 61 }, 62 (t) => ({ 63 urlIdIdx: index("url_id_idx").on(t.urlId), 64 // Add a GIN index for full text search 65 searchIdx: index("search_idx").using( 66 "gin", 67 sql`to_tsvector('english', coalesce(${t.title}, '') || ' ' || coalesce(${t.description}, '') || ' ' || coalesce(${t.content}, ''))`, 68 ), 69 }), 70); 71 72export const links = pgTable( 73 "links", 74 { 75 id: serial("id").primaryKey(), 76 sourceUrlId: integer("source_url_id") 77 .references(() => urls.id) 78 .notNull(), 79 targetUrlId: integer("target_url_id") 80 .references(() => urls.id) 81 .notNull(), 82 createdAt: timestamp("created_at").defaultNow(), 83 }, 84 (t) => ({ 85 sourceTargetIdx: uniqueIndex("source_target_idx").on( 86 t.sourceUrlId, 87 t.targetUrlId, 88 ), 89 }), 90); 91 92export const domainsRelations = relations(domains, ({ many }) => ({ 93 urls: many(urls), 94})); 95 96export const urlsRelations = relations(urls, ({ one, many }) => ({ 97 domain: one(domains, { 98 fields: [urls.domainId], 99 references: [domains.id], 100 }), 101 page: one(pages, { 102 fields: [urls.id], 103 references: [pages.urlId], 104 }), 105 outgoingLinks: many(links, { relationName: "sourceLinks" }), 106 incomingLinks: many(links, { relationName: "targetLinks" }), 107})); 108 109export const pagesRelations = relations(pages, ({ one }) => ({ 110 url: one(urls, { 111 fields: [pages.urlId], 112 references: [urls.id], 113 }), 114})); 115 116export const linksRelations = relations(links, ({ one }) => ({ 117 sourceUrl: one(urls, { 118 fields: [links.sourceUrlId], 119 references: [urls.id], 120 relationName: "sourceLinks", 121 }), 122 targetUrl: one(urls, { 123 fields: [links.targetUrlId], 124 references: [urls.id], 125 relationName: "targetLinks", 126 }), 127}));