socksthewolf.com
Schedule posts to Bluesky with Cloudflare workers.
skyscheduler.work
cf
tool
bsky-tool
cloudflare
bluesky
schedule
bsky
service
social-media
cloudflare-workers
1import { sql } from "drizzle-orm";
2import { index, integer, sqliteTable, text, unique } from "drizzle-orm/sqlite-core";
3import type { RepostInfo } from "../classes/repost";
4import { PostLabel } from "../enums";
5import type { EmbedData } from '../types';
6import { users } from "./auth.schema";
7
8export const posts = sqliteTable('posts', {
9 uuid: text('uuid', {mode: 'text'}).primaryKey(),
10 content: text('content').notNull(),
11 scheduledDate: integer('scheduled_date', { mode: 'timestamp_ms' }).notNull(),
12 posted: integer('posted', { mode: 'boolean' }).default(false),
13 // This is a flag to help beat any race conditions with our cron jobs
14 postNow: integer('postNow', { mode: 'boolean' }).default(false),
15 embedContent: text('embedContent', {mode: 'json'}).notNull().$type<EmbedData[]>().default(sql`(json_array())`),
16 // Contains the reposting cadence of this post object, actionable rules are in the reposts table
17 repostInfo: text('repostInfo', {mode: 'json'}).$type<RepostInfo[]>(),
18 // bsky/atproto record information once a post is posted
19 uri: text('uri'),
20 cid: text('cid'),
21 // if this post is a pseudo post (i.e. a repost of anything)
22 isRepost: integer('isRepost', { mode: 'boolean' }).default(false),
23 rootPost: text('rootPost'),
24 parentPost: text('parentPost'),
25 threadOrder: integer('threadOrder').default(-1),
26 // bsky content labels
27 contentLabel: text('contentLabel', {mode: 'text'}).$type<PostLabel>().default(PostLabel.None).notNull(),
28 // metadata timestamps
29 createdAt: integer('created_at', { mode: 'timestamp_ms' })
30 .default(sql`CURRENT_TIMESTAMP`)
31 .notNull(),
32 updatedAt: integer("updated_at", { mode: "timestamp_ms" })
33 .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
34 // who created this post
35 userId: text("user")
36 .notNull()
37 .references(() => users.id, { onDelete: "cascade" }),
38}, (table) => [
39 // finding posts by user
40 index("user_idx").on(table.userId),
41 // for purging posted posts after a set time
42 index("postedUpdate_idx")
43 .on(table.updatedAt, table.posted)
44 .where(sql`posted = 1`),
45 // for reposts of the user
46 index("repostOnlyUser_idx")
47 .on(table.userId, table.isRepost)
48 .where(sql`isRepost = 1`),
49 // for db pruning and parity with the PDS
50 index("postedUUID_idx").on(table.uuid, table.posted),
51 // for db deletion on delete posts
52 index("rootPostedUUID_idx")
53 .on(table.rootPost, table.posted)
54 .where(sql`rootPost is not NULL and posted = 1`),
55 // Querying children
56 index("generalThread_idx")
57 .on(table.parentPost, table.rootPost)
58 .where(sql`parentPost is not NULL`),
59 // Updating thread orders
60 index("threadOrder_idx")
61 .on(table.rootPost, table.threadOrder)
62 .where(sql`threadOrder <> -1`),
63 // cron job
64 index("postNowScheduledDatePosted_idx")
65 .on(table.posted, table.scheduledDate, table.postNow)
66 .where(sql`posted = 0 and postNow <> 1`),
67 // used to lower down the amount of posts that fill up the post table
68 index("repostAddOn_idx").on(table.userId, table.cid)
69]);
70
71export const reposts = sqliteTable('reposts', {
72 // garbage key
73 id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
74 // reflected post uuid
75 uuid: text('post_uuid')
76 .notNull()
77 .references(() => posts.uuid, {onDelete: "cascade"}),
78 scheduledDate: integer('scheduled_date', { mode: 'timestamp_ms' }).notNull(),
79 // bunching schedule cadence actions
80 scheduleGuid: text('schedule_guid')
81}, (table) => [
82 // cron queries
83 index("repost_scheduledDate_idx").on(table.scheduledDate),
84 // used for left joining and matching with posts field
85 index("repost_postid_idx").on(table.uuid),
86 // used for checking if a schedule still has actions left
87 index("repost_scheduleGuid_idx").on(table.scheduleGuid, table.uuid),
88 // preventing similar actions from pushing to the table
89 unique("repost_noduplicates_idx").on(table.uuid, table.scheduledDate),
90]);
91
92// cache table for handling repost counts, without having to scan the entire
93// repost table
94export const repostCounts = sqliteTable('repostCounts', {
95 uuid: text('post_uuid')
96 .notNull()
97 .references(() => posts.uuid, {onDelete: "cascade"}).primaryKey(),
98 count: integer('count').default(0).notNull()
99});
100
101// helper bookkeeping to make sure we don't have a ton of abandoned files in R2
102export const mediaFiles = sqliteTable('media', {
103 fileName: text('file', {mode: 'text'}).primaryKey(),
104 hasPost: integer('hasPost', { mode: 'boolean' }).default(false),
105 userId: text("user")
106 .references(() => users.id, { onDelete: "cascade" }),
107 createdAt: integer('created_at', { mode: 'timestamp_ms' })
108 .$defaultFn(() => /* @__PURE__ */ new Date())
109 .notNull(),
110}, (table) => [
111 index("media_oldWithNoPost_idx").on(table.hasPost, table.createdAt).where(sql`hasPost = 0`),
112 index("media_userid_idx").on(table.userId)
113]);