pocketenv.io
open, interoperable sandbox platform for agents and humans 📦 ✨
pocketenv.io
claude-code
atproto
sandbox
openclaw
agent
tsiry-sandratraina.com
1import SqliteDb from "better-sqlite3";
2import chalk from "chalk";
3import type { Context } from "context";
4import {
5 Kysely,
6 type Migration,
7 type MigrationProvider,
8 Migrator,
9 SqliteDialect,
10} from "kysely";
11import { consola } from "consola";
12
13export type DatabaseSchema = {
14 status: Status;
15 auth_session: AuthSession;
16 auth_state: AuthState;
17};
18
19export type Status = {
20 uri: string;
21 authorDid: string;
22 status: string;
23 createdAt: string;
24 indexedAt: string;
25};
26
27export type AuthSession = {
28 key: string;
29 session: AuthSessionJson;
30 expiresAt?: string | null;
31};
32
33export type AuthState = {
34 key: string;
35 state: AuthStateJson;
36};
37
38type AuthStateJson = string;
39
40type AuthSessionJson = string;
41
42// Migrations
43
44const migrations: Record<string, Migration> = {};
45
46const migrationProvider: MigrationProvider = {
47 async getMigrations() {
48 return migrations;
49 },
50};
51
52migrations["001"] = {
53 async up(db: Kysely<unknown>) {
54 await db.schema
55 .createTable("status")
56 .addColumn("uri", "varchar", (col) => col.primaryKey())
57 .addColumn("authorDid", "varchar", (col) => col.notNull())
58 .addColumn("status", "varchar", (col) => col.notNull())
59 .addColumn("createdAt", "varchar", (col) => col.notNull())
60 .addColumn("indexedAt", "varchar", (col) => col.notNull())
61 .execute();
62 await db.schema
63 .createTable("auth_session")
64 .addColumn("key", "varchar", (col) => col.primaryKey())
65 .addColumn("session", "varchar", (col) => col.notNull())
66 .execute();
67 await db.schema
68 .createTable("auth_state")
69 .addColumn("key", "varchar", (col) => col.primaryKey())
70 .addColumn("state", "varchar", (col) => col.notNull())
71 .execute();
72 },
73 async down(db: Kysely<unknown>) {
74 await db.schema.dropTable("auth_state").execute();
75 await db.schema.dropTable("auth_session").execute();
76 await db.schema.dropTable("status").execute();
77 },
78};
79
80migrations["002"] = {
81 async up(db: Kysely<unknown>) {
82 await db.schema
83 .alterTable("auth_session")
84 .addColumn("expiresAt", "text", (col) => col.defaultTo("NULL"))
85 .execute();
86 },
87 async down(db: Kysely<unknown>) {
88 await db.schema
89 .alterTable("auth_session")
90 .dropColumn("expiresAt")
91 .execute();
92 },
93};
94
95// APIs
96
97export const createDb = (location: string): Database => {
98 return new Kysely<DatabaseSchema>({
99 dialect: new SqliteDialect({
100 database: new SqliteDb(location),
101 }),
102 });
103};
104
105export const migrateToLatest = async (db: Database) => {
106 const migrator = new Migrator({ db, provider: migrationProvider });
107 const { error } = await migrator.migrateToLatest();
108 if (error) throw error;
109};
110
111export const updateExpiresAt = async (db: Database) => {
112 // get all sessions that have expiresAt is null
113 const sessions = await db.selectFrom("auth_session").selectAll().execute();
114 consola.info("Found", sessions.length, "sessions to update");
115 for (const session of sessions) {
116 const data = JSON.parse(session.session) as {
117 tokenSet: { expires_at?: string | null };
118 };
119 consola.info(session.key, data.tokenSet.expires_at);
120 await db
121 .updateTable("auth_session")
122 .set({ expiresAt: data.tokenSet.expires_at })
123 .where("key", "=", session.key)
124 .execute();
125 }
126
127 consola.info(`Updated ${chalk.greenBright(sessions.length)} sessions`);
128};
129
130export type Database = Kysely<DatabaseSchema>;