xonecas.io
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}));