skywatch.blue
A tool for tailing a labelers' firehose, rehydrating, and storing records for future analysis of moderation decisions.
1import { describe, test, expect, beforeAll, afterAll } from "bun:test";
2import { Database } from "duckdb";
3import { initializeSchema } from "../../src/database/schema.js";
4import { LabelsRepository } from "../../src/database/labels.repository.js";
5import { PostsRepository } from "../../src/database/posts.repository.js";
6import { ProfilesRepository } from "../../src/database/profiles.repository.js";
7import { BlobsRepository } from "../../src/database/blobs.repository.js";
8
9describe("Database Integration Tests", () => {
10 let db: Database;
11 let labelsRepo: LabelsRepository;
12 let postsRepo: PostsRepository;
13 let profilesRepo: ProfilesRepository;
14 let blobsRepo: BlobsRepository;
15
16 beforeAll(async () => {
17 db = new Database(":memory:");
18
19 await new Promise<void>((resolve, reject) => {
20 db.exec(
21 `
22 CREATE SEQUENCE IF NOT EXISTS labels_id_seq;
23 CREATE TABLE IF NOT EXISTS labels (
24 id INTEGER PRIMARY KEY DEFAULT nextval('labels_id_seq'),
25 uri TEXT NOT NULL,
26 cid TEXT,
27 val TEXT NOT NULL,
28 neg BOOLEAN DEFAULT FALSE,
29 cts TIMESTAMP NOT NULL,
30 exp TIMESTAMP,
31 src TEXT NOT NULL,
32 UNIQUE(uri, val, cts)
33 );
34
35 CREATE TABLE IF NOT EXISTS posts (
36 uri TEXT PRIMARY KEY,
37 did TEXT NOT NULL,
38 text TEXT,
39 facets JSON,
40 embeds JSON,
41 langs JSON,
42 tags JSON,
43 created_at TIMESTAMP NOT NULL,
44 is_reply BOOLEAN DEFAULT FALSE
45 );
46
47 CREATE TABLE IF NOT EXISTS profiles (
48 did TEXT PRIMARY KEY,
49 handle TEXT,
50 display_name TEXT,
51 description TEXT,
52 avatar_cid TEXT,
53 banner_cid TEXT
54 );
55
56 CREATE TABLE IF NOT EXISTS blobs (
57 post_uri TEXT NOT NULL,
58 blob_cid TEXT NOT NULL,
59 sha256 TEXT NOT NULL,
60 phash TEXT,
61 storage_path TEXT,
62 mimetype TEXT,
63 PRIMARY KEY (post_uri, blob_cid)
64 );
65 `,
66 (err) => {
67 if (err) reject(err);
68 else resolve();
69 }
70 );
71 });
72
73 labelsRepo = new LabelsRepository(db);
74 postsRepo = new PostsRepository(db);
75 profilesRepo = new ProfilesRepository(db);
76 blobsRepo = new BlobsRepository(db);
77 });
78
79 afterAll(async () => {
80 await new Promise<void>((resolve) => {
81 db.close(() => resolve());
82 });
83 });
84
85 describe("LabelsRepository", () => {
86 test("should insert and retrieve a label", async () => {
87 const label = {
88 uri: "at://did:plc:test/app.bsky.feed.post/123",
89 val: "spam",
90 cts: "2025-01-15T12:00:00Z",
91 src: "did:plc:labeler",
92 };
93
94 await labelsRepo.insert(label);
95 const found = await labelsRepo.findByUri(label.uri);
96
97 expect(found.length).toBe(1);
98 expect(found[0].val).toBe("spam");
99 });
100
101 test("should find labels by value", async () => {
102 const labels = await labelsRepo.findByValue("spam");
103 expect(labels.length).toBeGreaterThan(0);
104 });
105 });
106
107 describe("PostsRepository", () => {
108 test("should insert and retrieve a post", async () => {
109 const post = {
110 uri: "at://did:plc:user/app.bsky.feed.post/abc123",
111 did: "did:plc:user",
112 text: "test post",
113 created_at: "2025-01-15T12:00:00Z",
114 is_reply: false,
115 };
116
117 await postsRepo.insert(post);
118 const found = await postsRepo.findByUri(post.uri);
119
120 expect(found).not.toBeNull();
121 expect(found?.text).toBe("test post");
122 });
123
124 test("should find posts by DID", async () => {
125 const posts = await postsRepo.findByDid("did:plc:user");
126 expect(posts.length).toBeGreaterThan(0);
127 });
128 });
129
130 describe("ProfilesRepository", () => {
131 test("should insert and retrieve a profile", async () => {
132 const profile = {
133 did: "did:plc:testuser",
134 handle: "testuser.bsky.social",
135 display_name: "Test User",
136 description: "A test user",
137 };
138
139 await profilesRepo.insert(profile);
140 const found = await profilesRepo.findByDid(profile.did);
141
142 expect(found).not.toBeNull();
143 expect(found?.handle).toBe("testuser.bsky.social");
144 });
145
146 test("should find profile by handle", async () => {
147 const found = await profilesRepo.findByHandle("testuser.bsky.social");
148 expect(found).not.toBeNull();
149 expect(found?.did).toBe("did:plc:testuser");
150 });
151
152 test("should insert and retrieve profile with avatar and banner", async () => {
153 const profile = {
154 did: "did:plc:testuser2",
155 handle: "testuser2.bsky.social",
156 display_name: "Test User 2",
157 description: "A test user with avatar",
158 avatar_cid: "bafyavatartest",
159 banner_cid: "bafybannertest",
160 };
161
162 await profilesRepo.insert(profile);
163 const found = await profilesRepo.findByDid(profile.did);
164
165 expect(found).not.toBeNull();
166 expect(found?.avatar_cid).toBe("bafyavatartest");
167 expect(found?.banner_cid).toBe("bafybannertest");
168 });
169 });
170
171 describe("BlobsRepository", () => {
172 test("should insert and retrieve a blob", async () => {
173 const blob = {
174 post_uri: "at://did:plc:user/app.bsky.feed.post/abc123",
175 blob_cid: "bafytest123",
176 sha256: "abc123def456",
177 phash: "deadbeef",
178 mimetype: "image/jpeg",
179 };
180
181 await blobsRepo.insert(blob);
182 const found = await blobsRepo.findByPostUri(blob.post_uri);
183
184 expect(found.length).toBe(1);
185 expect(found[0].sha256).toBe("abc123def456");
186 });
187
188 test("should find blob by SHA256", async () => {
189 const found = await blobsRepo.findBySha256("abc123def456");
190 expect(found).not.toBeNull();
191 expect(found?.blob_cid).toBe("bafytest123");
192 });
193
194 test("should find blobs by pHash", async () => {
195 const found = await blobsRepo.findByPhash("deadbeef");
196 expect(found.length).toBeGreaterThan(0);
197 });
198
199 test("should find blob by CID", async () => {
200 const found = await blobsRepo.findByCid("bafytest123");
201 expect(found).not.toBeNull();
202 expect(found?.sha256).toBe("abc123def456");
203 });
204 });
205});