3 files changed, 57 insertions, 1 deletions
diff --git a/bskylink/src/db/migrations/002-safelink.ts b/bskylink/src/db/migrations/002-safelink.ts
new file mode 100644
index 000000000..723d7b2e7
--- /dev/null
+++ b/bskylink/src/db/migrations/002-safelink.ts
@@ -0,0 +1,34 @@
+import {type Kysely, sql} from 'kysely'
+
+export async function up(db: Kysely<unknown>): Promise<void> {
+  await db.schema
+    .createTable('safelink_rule')
+    .addColumn('id', 'bigserial', col => col.primaryKey())
+    .addColumn('eventType', 'varchar', col => col.notNull())
+    .addColumn('url', 'varchar', col => col.notNull())
+    .addColumn('pattern', 'varchar', col => col.notNull())
+    .addColumn('action', 'varchar', col => col.notNull())
+    .addColumn('createdAt', 'timestamptz', col => col.notNull())
+    .execute()
+
+  await db.schema
+    .createTable('safelink_cursor')
+    .addColumn('id', 'bigserial', col => col.notNull())
+    .addColumn('cursor', 'varchar', col => col.notNull())
+    .addColumn('updatedAt', 'timestamptz', col => col.notNull())
+    .execute()
+
+  await db.schema
+    .createIndex('safelink_rule_url_pattern_created_at_idx')
+    .on('safelink_rule')
+    .expression(sql`"url", "pattern", "createdAt" DESC`)
+    .execute()
+}
+
+export async function down(db: Kysely<unknown>): Promise<void> {
+  await db.schema
+    .dropIndex('safelink_rule_url_pattern_created_at_idx')
+    .execute()
+  await db.schema.dropTable('safelink_rule').execute()
+  await db.schema.dropTable('safelink_cursor').execute()
+}
diff --git a/bskylink/src/db/migrations/index.ts b/bskylink/src/db/migrations/index.ts
index 05e4de937..1f7385ab1 100644
--- a/bskylink/src/db/migrations/index.ts
+++ b/bskylink/src/db/migrations/index.ts
@@ -1,5 +1,7 @@
 import * as init from './001-init.js'
+import * as safelink from './002-safelink.js'
 
 export default {
   '001': init,
+  '002': safelink,
 }
diff --git a/bskylink/src/db/schema.ts b/bskylink/src/db/schema.ts
index 8d97f5800..d13a28038 100644
--- a/bskylink/src/db/schema.ts
+++ b/bskylink/src/db/schema.ts
@@ -1,7 +1,10 @@
-import {Selectable} from 'kysely'
+import {type ToolsOzoneSafelinkDefs} from '@atproto/api'
+import {type Selectable} from 'kysely'
 
 export type DbSchema = {
   link: Link
+  safelink_rule: SafelinkRule
+  safelink_cursor: SafelinkCursor
 }
 
 export interface Link {
@@ -14,4 +17,21 @@ export enum LinkType {
   StarterPack = 1,
 }
 
+export interface SafelinkRule {
+  id: number
+  eventType: ToolsOzoneSafelinkDefs.EventType
+  url: string
+  pattern: ToolsOzoneSafelinkDefs.PatternType
+  action: ToolsOzoneSafelinkDefs.ActionType
+  createdAt: string
+}
+
+export interface SafelinkCursor {
+  id: number
+  cursor: string
+  updatedAt: Date
+}
+
 export type LinkEntry = Selectable<Link>
+export type SafelinkRuleEntry = Selectable<SafelinkRule>
+export type SafelinkCursorEntry = Selectable<SafelinkCursor>