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