Exosphere is a set of small, modular, self-hostable community tools built on the AT Protocol.
app.exosphere.site
1import { getDb } from "@exosphere/core/db";
2import { eq, and, inArray } from "@exosphere/core/db/drizzle";
3import { entityLabels } from "@exosphere/core/db/schema";
4import { nextEntryNumber } from "@exosphere/core/db/entry-number";
5import { tidToDate } from "@exosphere/core/pds";
6import type { ModerationHandler } from "@exosphere/core/sphere";
7import { getActiveMemberRole } from "@exosphere/core/sphere";
8import { checkPermission } from "@exosphere/core/permissions";
9import {
10 featureRequests,
11 featureRequestVotes,
12 featureRequestComments,
13 featureRequestCommentVotes,
14 featureRequestStatuses,
15} from "./schema.ts";
16import type { Status } from "../schemas/feature-request.ts";
17
18// ---- Feature Requests ----
19
20export function insertFeatureRequest(params: {
21 id: string;
22 sphereId: string;
23 authorDid: string;
24 title: string;
25 description: string;
26 pdsUri: string | null;
27}): typeof featureRequests.$inferSelect | undefined {
28 const db = getDb();
29 return db.transaction((tx) => {
30 const existing = tx
31 .select()
32 .from(featureRequests)
33 .where(eq(featureRequests.id, params.id))
34 .get();
35 if (existing) {
36 if (existing.authorDid !== params.authorDid) return existing;
37 if (existing.title !== params.title || existing.description !== params.description) {
38 const updatedAt = new Date().toISOString();
39 tx.update(featureRequests)
40 .set({
41 title: params.title,
42 description: params.description,
43 updatedAt,
44 })
45 .where(eq(featureRequests.id, params.id))
46 .run();
47 return { ...existing, title: params.title, description: params.description, updatedAt };
48 }
49 return existing;
50 }
51
52 const number = nextEntryNumber(tx, params.sphereId);
53
54 tx.insert(featureRequests)
55 .values({
56 id: params.id,
57 sphereId: params.sphereId,
58 number,
59 authorDid: params.authorDid,
60 title: params.title,
61 description: params.description,
62 pdsUri: params.pdsUri,
63 })
64 .run();
65
66 return tx.select().from(featureRequests).where(eq(featureRequests.id, params.id)).get();
67 });
68}
69
70export function deleteFeatureRequestCascade(id: string): void {
71 const db = getDb();
72 db.transaction((tx) => {
73 const commentIds = tx
74 .select({ id: featureRequestComments.id })
75 .from(featureRequestComments)
76 .where(eq(featureRequestComments.requestId, id))
77 .all()
78 .map((r) => r.id);
79 if (commentIds.length > 0) {
80 tx.delete(featureRequestCommentVotes)
81 .where(inArray(featureRequestCommentVotes.commentId, commentIds))
82 .run();
83 }
84 tx.delete(featureRequestComments).where(eq(featureRequestComments.requestId, id)).run();
85 tx.delete(featureRequestStatuses).where(eq(featureRequestStatuses.requestId, id)).run();
86 tx.delete(featureRequestVotes).where(eq(featureRequestVotes.requestId, id)).run();
87 tx.delete(entityLabels).where(eq(entityLabels.entityId, id)).run();
88 tx.delete(featureRequests).where(eq(featureRequests.id, id)).run();
89 });
90}
91
92// ---- Votes ----
93
94export function insertVote(requestId: string, authorDid: string, pdsUri: string | null): void {
95 getDb()
96 .insert(featureRequestVotes)
97 .values({ requestId, authorDid, pdsUri })
98 .onConflictDoNothing()
99 .run();
100}
101
102export function deleteVoteByAuthor(requestId: string, authorDid: string): void {
103 getDb()
104 .delete(featureRequestVotes)
105 .where(
106 and(
107 eq(featureRequestVotes.requestId, requestId),
108 eq(featureRequestVotes.authorDid, authorDid),
109 ),
110 )
111 .run();
112}
113
114// ---- Comments ----
115
116export function insertComment(params: {
117 id: string;
118 requestId: string;
119 authorDid: string;
120 content: string;
121 pdsUri: string | null;
122}): void {
123 const updatedAt = tidToDate(params.id);
124 getDb()
125 .insert(featureRequestComments)
126 .values({ ...params, updatedAt })
127 .onConflictDoUpdate({
128 target: featureRequestComments.id,
129 set: { content: params.content, updatedAt: new Date().toISOString() },
130 })
131 .run();
132}
133
134export function updateComment(id: string, content: string): void {
135 getDb()
136 .update(featureRequestComments)
137 .set({ content, updatedAt: new Date().toISOString() })
138 .where(eq(featureRequestComments.id, id))
139 .run();
140}
141
142export function deleteCommentCascade(id: string): void {
143 const db = getDb();
144 db.delete(featureRequestCommentVotes).where(eq(featureRequestCommentVotes.commentId, id)).run();
145 db.delete(featureRequestComments).where(eq(featureRequestComments.id, id)).run();
146}
147
148// ---- Comment Votes ----
149
150export function insertCommentVote(
151 commentId: string,
152 authorDid: string,
153 pdsUri: string | null,
154): void {
155 getDb()
156 .insert(featureRequestCommentVotes)
157 .values({ commentId, authorDid, pdsUri })
158 .onConflictDoNothing()
159 .run();
160}
161
162export function deleteCommentVoteByAuthor(commentId: string, authorDid: string): void {
163 getDb()
164 .delete(featureRequestCommentVotes)
165 .where(
166 and(
167 eq(featureRequestCommentVotes.commentId, commentId),
168 eq(featureRequestCommentVotes.authorDid, authorDid),
169 ),
170 )
171 .run();
172}
173
174// ---- Status ----
175
176export function insertStatusAndUpdateFR(params: {
177 id: string;
178 requestId: string;
179 authorDid: string;
180 status: Status;
181 pdsUri: string | null;
182 duplicateOfId?: string | null;
183 clearDuplicateOfId?: boolean;
184}): void {
185 const db = getDb();
186 db.insert(featureRequestStatuses)
187 .values({
188 id: params.id,
189 requestId: params.requestId,
190 authorDid: params.authorDid,
191 status: params.status,
192 pdsUri: params.pdsUri,
193 })
194 .onConflictDoNothing()
195 .run();
196
197 const updateFields: Record<string, unknown> = {
198 status: params.status,
199 updatedAt: new Date().toISOString(),
200 };
201 if (params.duplicateOfId !== undefined) {
202 updateFields.duplicateOfId = params.duplicateOfId;
203 }
204 if (params.clearDuplicateOfId) {
205 updateFields.duplicateOfId = null;
206 }
207
208 db.update(featureRequests)
209 .set(updateFields)
210 .where(eq(featureRequests.id, params.requestId))
211 .run();
212}
213
214// ---- Moderation ----
215
216export function hideFeatureRequest(id: string, moderatorDid: string): void {
217 getDb()
218 .update(featureRequests)
219 .set({ hiddenAt: new Date().toISOString(), moderatedBy: moderatorDid })
220 .where(eq(featureRequests.id, id))
221 .run();
222}
223
224export function unhideFeatureRequest(id: string): void {
225 getDb()
226 .update(featureRequests)
227 .set({ hiddenAt: null, moderatedBy: null })
228 .where(eq(featureRequests.id, id))
229 .run();
230}
231
232export function hideComment(id: string, moderatorDid: string): void {
233 getDb()
234 .update(featureRequestComments)
235 .set({ hiddenAt: new Date().toISOString(), moderatedBy: moderatorDid })
236 .where(eq(featureRequestComments.id, id))
237 .run();
238}
239
240export function unhideComment(id: string): void {
241 getDb()
242 .update(featureRequestComments)
243 .set({ hiddenAt: null, moderatedBy: null })
244 .where(eq(featureRequestComments.id, id))
245 .run();
246}
247
248/** Moderation handler for feature requests and comments. Returns true if it handled the subject. */
249export const handleFeatureRequestModeration: ModerationHandler = (
250 subjectUri,
251 moderatorDid,
252 sphereId,
253) => {
254 const role = getActiveMemberRole(sphereId, moderatorDid);
255 if (!checkPermission(sphereId, "feature-requests", "moderate", role)) return false;
256
257 const db = getDb();
258
259 const fr = db
260 .select({ id: featureRequests.id })
261 .from(featureRequests)
262 .where(eq(featureRequests.pdsUri, subjectUri))
263 .get();
264 if (fr) {
265 hideFeatureRequest(fr.id, moderatorDid);
266 return true;
267 }
268
269 const comment = db
270 .select({ id: featureRequestComments.id })
271 .from(featureRequestComments)
272 .where(eq(featureRequestComments.pdsUri, subjectUri))
273 .get();
274 if (comment) {
275 hideComment(comment.id, moderatorDid);
276 return true;
277 }
278
279 return false;
280};