release-monitor/src/db/schema.ts at main · aboutbits/release-monitor · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
import { sql } from 'drizzle-orm'
import {
  bigint,
  bigserial,
  boolean,
  index,
  integer,
  jsonb,
  pgSchema,
  text,
  timestamp,
  unique,
} from 'drizzle-orm/pg-core'

export type NotificationMode = 'digest' | 'immediately' | 'security-only'

export const DB_SCHEMA = 'main'

const tableBuilder = pgSchema(DB_SCHEMA).table

export const repositories = tableBuilder(
  'repositories',
  {
    id: bigserial('id', { mode: 'bigint' }).primaryKey(),
    forge: text('forge').notNull(),
    owner: text('owner').notNull(),
    repo: text('repo').notNull(),
    url: text('url'),
    lastCheckedAt: timestamp('last_checked_at', { withTimezone: true }),
    pollToken: text('poll_token'),
    lastKnownReleaseId: text('last_known_release_id'),
    createdAt: timestamp('created_at', { withTimezone: true })
      .notNull()
      .default(sql`now()`),
  },
  (t) => [unique().on(t.forge, t.owner, t.repo)],
)

export const subscriptions = tableBuilder(
  'subscriptions',
  {
    id: bigserial('id', { mode: 'bigint' }).primaryKey(),
    channelId: text('channel_id').notNull(),
    repositoryId: bigint('repository_id', { mode: 'bigint' })
      .notNull()
      .references(() => repositories.id, { onDelete: 'cascade' }),
    subscribedAt: timestamp('subscribed_at', { withTimezone: true })
      .notNull()
      .default(sql`now()`),
    subscribedBy: text('subscribed_by').notNull(),
    notificationMode: text('notification_mode').notNull(),
  },
  (t) => [
    unique().on(t.channelId, t.repositoryId),
    index('idx_subscriptions_repository_id').on(t.repositoryId),
  ],
)

export const releases = tableBuilder(
  'releases',
  {
    id: bigserial('id', { mode: 'bigint' }).primaryKey(),
    repositoryId: bigint('repository_id', { mode: 'bigint' })
      .notNull()
      .references(() => repositories.id, { onDelete: 'cascade' }),
    forgeReleaseId: text('forge_release_id').notNull(),
    tagName: text('tag_name').notNull(),
    name: text('name'),
    url: text('url').notNull(),
    publishedAt: timestamp('published_at', {
      withTimezone: true,
    }).notNull(),
    isSecurity: boolean('is_security').notNull().default(false),
    securityScore: integer('security_score'),
    securityReasons: jsonb('security_reasons'),
    discoveredAt: timestamp('discovered_at', { withTimezone: true })
      .notNull()
      .default(sql`now()`),
  },
  (t) => [
    unique().on(t.repositoryId, t.forgeReleaseId),
    index('idx_releases_repository_id_published_at').on(
      t.repositoryId,
      sql`${t.publishedAt} DESC`,
    ),
  ],
)

export const notifications = tableBuilder(
  'notifications',
  {
    id: bigserial('id', { mode: 'bigint' }).primaryKey(),
    releaseId: bigint('release_id', { mode: 'bigint' })
      .notNull()
      .references(() => releases.id, { onDelete: 'cascade' }),
    channelId: text('channel_id').notNull(),
    kind: text('kind').notNull(),
    sentAt: timestamp('sent_at', { withTimezone: true })
      .notNull()
      .default(sql`now()`),
  },
  (t) => [
    unique().on(t.releaseId, t.channelId),
    index('idx_notifications_channel_id').on(t.channelId),
  ],
)

export type Repository = typeof repositories.$inferSelect
export type Release = typeof releases.$inferSelect
export type Subscription = typeof subscriptions.$inferSelect