Every analytics tool wants a subscription. And your data. I built Twiligent instead. YouTube stats. Instagram stats. One dashboard. Runs locally on your machine.
import { execute, maybeRow, rows } from './query.ts';
import type { SupabaseClient } from '../types/app.ts';
import type { PostStatus, ScheduledPost } from '../../shared/types/domain.ts';
interface ScheduledPostRow {
id: string;
account_id: string;
status: PostStatus;
scheduled_at: string;
claimed_at: string | null;
attempts: number | null;
data: Partial<ScheduledPost> | null;
}
// a published post whose comment automation has no rule yet, with the person it belongs to
export interface PostAwaitingAutomation {
post: ScheduledPost;
userId: string;
}
export interface DeleteAllResult {
deleted: number;
kept: number;
}
const COLUMNS = 'id, account_id, status, scheduled_at, claimed_at, attempts, data';
function toPost(row: ScheduledPostRow): ScheduledPost {
return {
id: row.id,
accountId: row.account_id,
status: row.status,
scheduledAt: row.scheduled_at,
...(row.data || {}),
claimedAt: row.claimed_at,
attempts: row.attempts ?? 0,
};
}
// methods named ...ForUser filter by the verified user id; listDue, listStale, listAwaitingAutomation, claim and updateById serve the schedulers across all users
export interface ScheduledPostRepository {
listForUser(userId: string): Promise<ScheduledPost[]>;
findForUser(id: string, userId: string): Promise<ScheduledPost | null>;
listDue(): Promise<ScheduledPost[]>;
listStale(claimedBefore: string): Promise<ScheduledPost[]>;
listAwaitingAutomation(limit: number): Promise<PostAwaitingAutomation[]>;
claim(post: ScheduledPost): Promise<ScheduledPost | null>;
create(post: ScheduledPost, userId: string): Promise<void>;
updatePendingForUser(id: string, updates: Partial<ScheduledPost>, userId: string): Promise<boolean>;
updateById(id: string, updates: Partial<ScheduledPost>): Promise<void>;
deleteForUser(id: string, userId: string): Promise<boolean>;
deleteAllForUser(userId: string): Promise<DeleteAllResult>;
}
export function createScheduledPostRepository(db: SupabaseClient): ScheduledPostRepository {
// one atomic statement that merges the rest into data; claim alone sets claimedAt and attempts, and onlyStatus guards edits
async function update(id: string, updates: Partial<ScheduledPost>, userId: string | null, onlyStatus?: PostStatus): Promise<boolean> {
const { status, scheduledAt, id: _id, accountId: _accountId, claimedAt: _claimedAt, attempts: _attempts, ...patch } = updates;
const { data } = await execute('scheduledPosts.update', () => db.rpc('merge_scheduled_post', {
p_id: id,
p_user_id: userId,
p_patch: patch,
p_status: status ?? null,
p_scheduled_at: scheduledAt ?? null,
p_only_status: onlyStatus ?? null,
}));
return data === true;
}
return {
async listForUser(userId) {
const found = await rows<ScheduledPostRow>('scheduledPosts.listForUser', () => db
.from('scheduled_posts')
.select(COLUMNS)
.order('scheduled_at', { ascending: true })
.eq('user_id', userId));
return found.map(toPost);
},
async findForUser(id, userId) {
const row = await maybeRow<ScheduledPostRow>('scheduledPosts.findForUser', () => db
.from('scheduled_posts')
.select(COLUMNS)
.eq('id', id)
.eq('user_id', userId)
.maybeSingle());
return row ? toPost(row) : null;
},
async listDue() {
const found = await rows<ScheduledPostRow>('scheduledPosts.listDue', () => db
.from('scheduled_posts')
.select(COLUMNS)
.eq('status', 'pending')
.lte('scheduled_at', new Date().toISOString()));
return found.map(toPost);
},
async listStale(claimedBefore) {
const found = await rows<ScheduledPostRow>('scheduledPosts.listStale', () => db
.from('scheduled_posts')
.select(COLUMNS)
.eq('status', 'publishing')
.lt('claimed_at', claimedBefore));
return found.map(toPost);
},
// oldest first, so a backlog clears in order; a post whose rule could not be made keeps its error and is not tried again
async listAwaitingAutomation(limit) {
const found = await rows<ScheduledPostRow & { user_id: string }>('scheduledPosts.listAwaitingAutomation', () => db
.from('scheduled_posts')
.select(`${COLUMNS}, user_id`)
.eq('status', 'published')
.not('data->automation', 'is', null)
.is('data->automationRuleId', null)
.is('data->automationError', null)
.order('scheduled_at', { ascending: true })
.limit(limit));
return found.map(row => ({ post: toPost(row), userId: row.user_id }));
},
// matches only the post as it was listed (pending, or the same expired claim) with the same attempts; null means another run won
async claim(post) {
const claimed = await rows<ScheduledPostRow>('scheduledPosts.claim', () => {
const query = db
.from('scheduled_posts')
.update({ status: 'publishing', claimed_at: new Date().toISOString(), attempts: (post.attempts ?? 0) + 1 })
.eq('id', post.id)
.eq('attempts', post.attempts ?? 0);
const unchanged = post.status === 'publishing'
? query.eq('status', 'publishing').eq('claimed_at', post.claimedAt)
: query.eq('status', 'pending');
return unchanged.select(COLUMNS);
});
return claimed[0] ? toPost(claimed[0]) : null;
},
async create(post, userId) {
const { id, accountId, status, scheduledAt, ...rest } = post;
await execute('scheduledPosts.create', () => db.from('scheduled_posts').insert({
id,
user_id: userId,
account_id: accountId,
status: status || 'pending',
scheduled_at: scheduledAt,
data: rest,
}));
},
updatePendingForUser: (id, updates, userId) => update(id, updates, userId, 'pending'),
async updateById(id, updates) {
await update(id, updates, null);
},
// a post being published is never deleted, checked in the delete itself so a claim in between cannot slip past
async deleteForUser(id, userId) {
const deleted = await rows<{ id: string }>('scheduledPosts.delete', () => db
.from('scheduled_posts')
.delete()
.eq('id', id)
.eq('user_id', userId)
.neq('status', 'publishing')
.select('id'));
return deleted.length > 0;
},
// posts being published right now are kept, so a publish never loses its row
async deleteAllForUser(userId) {
const total = await execute('scheduledPosts.count', () => db
.from('scheduled_posts').select('*', { count: 'exact', head: true }).eq('user_id', userId));
const keeping = await execute('scheduledPosts.countPublishing', () => db
.from('scheduled_posts').select('*', { count: 'exact', head: true }).eq('status', 'publishing').eq('user_id', userId));
await execute('scheduledPosts.deleteAll', () => db.from('scheduled_posts').delete().neq('status', 'publishing').eq('user_id', userId));
const kept = keeping.count || 0;
return { deleted: (total.count || 0) - kept, kept };
},
};
}