Something went wrong. Try again.
[READ-ONLY] Mirror of https://github.com/nperez0111/bookhive. Track your books, share your shelves, see what others are reading bookhive.buzz
atproto bluesky books bookshelf goodreads management-system
Something went wrong. Try again.
TypeScript
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318import { Kysely, Migrator, PostgresDialect, sql, type Migration, type MigrationProvider,} from "kysely";import pg from "pg";import type { BookIdentifiersRow, BookListRow, BookListItemRow, Buzz, HiveBook, HiveBookGenre, HiveId, UserBookRow, UserFollow,} from "./types";
// Typesexport type DatabaseSchema = { hive_book: HiveBook; hive_book_genre: HiveBookGenre; book_id_map: BookIdentifiersRow; user_book: UserBookRow; buzz: Buzz; user_follows: UserFollow; book_list: BookListRow; book_list_item: BookListItemRow;};
export const BookFields = [ "user_book.authors", "user_book.cid", "user_book.createdAt", "user_book.finishedAt", "user_book.hiveId", "user_book.indexedAt", "user_book.review", "user_book.stars", "user_book.startedAt", "user_book.status", "user_book.owned", "user_book.title", "user_book.uri", "user_book.userDid", "user_book.bookProgress", "hive_book.cover", "hive_book.thumbnail", "hive_book.description", "hive_book.rating", "hive_book.ratingsCount", "hive_book.rawTitle",] as const;
// Migrations — consolidated for PostgreSQL (fresh schema, no incremental SQLite history)
const migrations: Record<string, Migration> = {};
const migrationProvider: MigrationProvider = { async getMigrations() { return migrations; },};
migrations["pg-001"] = { async up(db: Kysely<unknown>) { // -- user_book -- await db.schema .createTable("user_book") .addColumn("uri", "text", (col) => col.primaryKey()) .addColumn("cid", "text", (col) => col.notNull()) .addColumn("userDid", "text", (col) => col.notNull()) .addColumn("createdAt", "text", (col) => col.notNull()) .addColumn("indexedAt", "text", (col) => col.notNull()) .addColumn("hiveId", "text", (col) => col.notNull()) .addColumn("title", "text", (col) => col.notNull()) .addColumn("authors", "text", (col) => col.notNull()) .addColumn("status", "text") .addColumn("startedAt", "text") .addColumn("finishedAt", "text") .addColumn("stars", "integer") .addColumn("review", "text") .addColumn("bookProgress", "text") .addColumn("owned", "integer", (col) => col.notNull().defaultTo(0)) .execute();
await db.schema .createIndex("idx_user_book_created_at") .on("user_book") .column("createdAt") .execute(); await db.schema .createIndex("idx_user_book_user_did") .on("user_book") .column("userDid") .execute(); await db.schema.createIndex("idx_user_book_hive_id").on("user_book").column("hiveId").execute(); await db.schema .createIndex("idx_user_book_user_created") .on("user_book") .columns(["userDid", "createdAt"]) .execute();
// -- hive_book -- await db.schema .createTable("hive_book") .addColumn("id", "text", (col) => col.primaryKey()) .addColumn("title", "text", (col) => col.notNull()) .addColumn("authors", "text", (col) => col.notNull()) .addColumn("source", "text", (col) => col.notNull()) .addColumn("sourceUrl", "text") .addColumn("sourceId", "text") .addColumn("cover", "text") .addColumn("thumbnail", "text", (col) => col.notNull()) .addColumn("description", "text") .addColumn("rating", "real") .addColumn("ratingsCount", "integer") .addColumn("createdAt", "text", (col) => col.notNull()) .addColumn("updatedAt", "text", (col) => col.notNull()) .addColumn("rawTitle", "text") .addColumn("series", "text") .addColumn("meta", "text") .addColumn("enrichedAt", "text") .addColumn("identifiers", "text") .addColumn("hiveBookAtUri", "text") .addColumn("hiveBookCatalogUpdatedAt", "text") .execute();
await sql`CREATE INDEX idx_hive_book_ratings_thumbnail ON hive_book("ratingsCount" DESC, authors, thumbnail) WHERE thumbnail IS NOT NULL AND thumbnail != ''`.execute(db); await sql`CREATE INDEX idx_hive_book_author_ratings ON hive_book(authors, "ratingsCount", rating)`.execute(db);
// -- buzz -- await db.schema .createTable("buzz") .addColumn("uri", "text", (col) => col.primaryKey()) .addColumn("cid", "text", (col) => col.notNull()) .addColumn("userDid", "text", (col) => col.notNull()) .addColumn("createdAt", "text", (col) => col.notNull()) .addColumn("indexedAt", "text", (col) => col.notNull()) .addColumn("comment", "text", (col) => col.notNull()) .addColumn("bookUri", "text", (col) => col.notNull()) .addColumn("bookCid", "text", (col) => col.notNull()) .addColumn("hiveId", "text", (col) => col.notNull()) .addColumn("parentUri", "text", (col) => col.notNull()) .addColumn("parentCid", "text", (col) => col.notNull()) .execute();
await sql`CREATE INDEX idx_buzz_hive_id ON buzz("hiveId", "createdAt")`.execute(db); await sql`CREATE INDEX idx_buzz_parent_uri ON buzz("parentUri")`.execute(db);
// -- user_follows -- await db.schema .createTable("user_follows") .addColumn("userDid", "text", (col) => col.notNull()) .addColumn("followsDid", "text", (col) => col.notNull()) .addColumn("followedAt", "text", (col) => col.notNull()) .addColumn("syncedAt", "text", (col) => col.notNull()) .addColumn("lastSeenAt", "text", (col) => col.notNull()) .addColumn("isActive", "integer", (col) => col.notNull().defaultTo(1)) .execute();
await db.schema .createIndex("idx_user_follows_primary") .on("user_follows") .columns(["userDid", "followsDid"]) .unique() .execute(); await db.schema .createIndex("idx_user_follows_synced") .on("user_follows") .columns(["userDid", "syncedAt"]) .execute(); await db.schema .createIndex("idx_user_follows_active") .on("user_follows") .columns(["userDid", "isActive", "followsDid"]) .execute();
// -- book_id_map -- await db.schema .createTable("book_id_map") .addColumn("hiveId", "text", (col) => col.primaryKey()) .addColumn("isbn", "text") .addColumn("isbn13", "text") .addColumn("goodreadsId", "text") .addColumn("updatedAt", "text", (col) => col.notNull()) .execute();
await db.schema.createIndex("idx_book_id_map_isbn").on("book_id_map").column("isbn").execute(); await db.schema .createIndex("idx_book_id_map_isbn13") .on("book_id_map") .column("isbn13") .execute(); await db.schema .createIndex("idx_book_id_map_goodreads_id") .on("book_id_map") .column("goodreadsId") .execute();
// -- hive_book_genre (with serial id to replace SQLite rowid) -- await db.schema .createTable("hive_book_genre") .addColumn("id", "serial", (col) => col.primaryKey()) .addColumn("hiveId", "text", (col) => col.notNull()) .addColumn("genre", "text", (col) => col.notNull()) .execute();
await sql`CREATE UNIQUE INDEX idx_hive_book_genre_pk ON hive_book_genre("hiveId", genre)`.execute( db, );
// -- book_list -- await db.schema .createTable("book_list") .addColumn("uri", "text", (col) => col.primaryKey()) .addColumn("cid", "text", (col) => col.notNull()) .addColumn("userDid", "text", (col) => col.notNull()) .addColumn("name", "text", (col) => col.notNull()) .addColumn("description", "text") .addColumn("ordered", "integer", (col) => col.notNull().defaultTo(0)) .addColumn("tags", "text") .addColumn("createdAt", "text", (col) => col.notNull()) .addColumn("indexedAt", "text", (col) => col.notNull()) .execute();
await db.schema.createIndex("idx_book_list_user").on("book_list").column("userDid").execute();
// -- book_list_item -- await db.schema .createTable("book_list_item") .addColumn("uri", "text", (col) => col.primaryKey()) .addColumn("cid", "text", (col) => col.notNull()) .addColumn("userDid", "text", (col) => col.notNull()) .addColumn("listUri", "text", (col) => col.notNull()) .addColumn("hiveId", "text") .addColumn("description", "text") .addColumn("position", "integer") .addColumn("addedAt", "text", (col) => col.notNull()) .addColumn("indexedAt", "text", (col) => col.notNull()) .addColumn("embeddedTitle", "text") .addColumn("embeddedAuthor", "text") .addColumn("embeddedCoverUrl", "text") .addColumn("identifiers", "text") .execute();
await db.schema .createIndex("idx_book_list_item_list") .on("book_list_item") .column("listUri") .execute(); await db.schema .createIndex("idx_book_list_item_hive") .on("book_list_item") .column("hiveId") .execute(); }, async down(db: Kysely<unknown>) { await db.schema.dropTable("book_list_item").ifExists().execute(); await db.schema.dropTable("book_list").ifExists().execute(); await db.schema.dropTable("hive_book_genre").ifExists().execute(); await db.schema.dropTable("book_id_map").ifExists().execute(); await db.schema.dropTable("user_follows").ifExists().execute(); await db.schema.dropTable("buzz").ifExists().execute(); await db.schema.dropTable("hive_book").ifExists().execute(); await db.schema.dropTable("user_book").ifExists().execute(); },};
// APIs
export const createDb = (connectionString: string): { db: Database; pool: pg.Pool } => { const pool = new pg.Pool({ connectionString, min: 2, max: 10, });
const db = new Kysely<DatabaseSchema>({ dialect: new PostgresDialect({ pool, }), });
return { db, pool };};
export const migrateToLatest = async (db: Database) => { const migrator = new Migrator({ db, provider: migrationProvider }); const { error, results } = await migrator.migrateToLatest(); if (error) throw error; return results ?? [];};
export type Database = Kysely<DatabaseSchema>;
/** Replace hive_book_genre rows for a book from a scraped genre list (used by enrichBookData). */export async function syncHiveBookGenres( db: Database, hiveId: HiveId, genresJson: string | null,): Promise<void> { await db.deleteFrom("hive_book_genre").where("hiveId", "=", hiveId).execute(); if (!genresJson) return; const genres: string[] = JSON.parse(genresJson); if (genres.length === 0) return; await db .insertInto("hive_book_genre") .values(genres.map((genre) => ({ hiveId, genre }))) .execute();}