Ponder

Database Schema

TablesDB schema, permissions, constraints, and indexes declared in appwrite.json.

Database

users

Purpose: user profile, account metadata, profile images, notification preference, premium flag, and feed preference compatibility fields.

Permissions: create("guests"), read("guests"), update("guests"), create("users"), read("users"), update("users"), delete("users")

Row security: false

Column Type Required Default Constraints
username string Yes null size 150
email string No null format email
isVerified boolean No null None declared
dateOfBirth datetime No null None declared
profilePictureUrl string No null format url
accountStatus string No null enum: active, inactive, suspended, pending
firstName text No null None declared
lastName text No null None declared
interests text[] No null array
boostedTags text[] No null array
suppressedTags text[] No null array
feedPreferences text No null None declared
notificationsEnabled boolean No null None declared
isPremium boolean No null None declared

Indexes:

content

Purpose: article/feed/search dataset, including source metadata, categorization, optional fact-check flag, and denormalized like count.

Permissions: read("any")

Row security: false

Column Type Required Default Constraints
title string Yes null size 255
body text Yes null None declared
body_text text No null None declared
category string Yes null enum: news, article, opinion, review, discover, business, science, technology, health, politics, culture, sports
publishedAt datetime No 2025-01-01T00:00:00+00:00 None declared
isFactChecked boolean No null None declared
language string No null enum: en, es, fr, de, cn
summary text No null None declared
subcategory text No null None declared
source text No null None declared
author text No null None declared
confidenceScore double No null range 0..100
readTimeMinutes integer No null range -9223372036854776000..9223372036854776000
isFeatured boolean No null None declared
sourceUrl string No null format url
imageUrl string No null format url
tags text[] No null array
likeCount integer No 0 range 0..9223372036854776000

Indexes:

saved_items

Purpose: per-user saved article rows and private notes.

Permissions: create("users"), read("users"), update("users"), delete("users")

Row security: false

Column Type Required Default Constraints
itemId string Yes null size 64
userId string Yes null size 64
saveDate datetime Yes null None declared
notes string No null size 500

Indexes:

content_likes

Purpose: one like row per user/content pair; like_sync maintains content.likeCount.

Permissions: create("users"), read("any"), delete("users")

Row security: false

Column Type Required Default Constraints
userId string Yes null size 64
contentId string Yes null size 64
likedAt datetime Yes null None declared

Indexes:

user_fact_check_history

Purpose: private signed-in user history of fact checks triggered from the app.

Permissions: create("users"), read("users"), delete("users")

Row security: false

Column Type Required Default Constraints
userId string Yes null size 64
factCheckId string Yes null size 64
triggeredAt datetime Yes null None declared
sourceContentId string No null size 64

Indexes:

fact_checks

Purpose: public fact-check cache and lookup rows, with legacy and newer fields both present.

Permissions: create("guests"), read("guests"), update("guests"), create("users"), read("users"), update("users")

Row security: false

Column Type Required Default Constraints
claim string Yes null size 1000
verdict string Yes null enum: true, false, uncertain, TRUE, FALSE, UNVERIFIABLE
confidenceLevel string Yes null size 20
confidenceScore double Yes null range 0..1
source string Yes null size 255
sourceUrl string Yes null format url
contextBlock string Yes null size 2000
checkedAt datetime No null None declared
relatedContentId text No null None declared
language text No null None declared
confidence double No null range 0..100
explanation string No null size 200
citations text No null None declared
userId string No null size 64
createdAt datetime No null None declared

Indexes:

friendships

Purpose: friend request and friendship status rows between two users.

Permissions: create("users"), read("users"), update("users"), delete("users")

Row security: false

Column Type Required Default Constraints
requesterId string Yes null size 64
addresseeId string Yes null size 64
status string Yes null enum: pending, accepted, rejected, blocked
createdAt datetime Yes null None declared
updatedAt datetime No null None declared

Indexes:

thread_messages

Purpose: row-secured direct-message rows for text and article-share messages.

Permissions: create("users")

Row security: true

Column Type Required Default Constraints
threadId string Yes null size 64
authorId string Yes null size 64
body string Yes null size 4096
sentAt datetime Yes null None declared
isEdited boolean No false None declared
type string No text enum: text, article
contentId string No null size 64

Indexes:

shares

Purpose: article-share activity rows between friends.

Permissions: create("users")

Row security: true

Column Type Required Default Constraints
senderId string Yes null size 64
recipientId string Yes null size 64
contentId string Yes null size 64
message string No null size 1000
sentAt datetime Yes null None declared
isRead boolean No false None declared

Indexes:

threads

Purpose: row-secured 1:1 direct-message thread metadata.

Permissions: create("users")

Row security: true

Column Type Required Default Constraints
contentId string No null size 64
pairKey string No null size 129
participantIds string[] Yes null size 64; array
createdAt datetime Yes null None declared
lastMessageAt datetime No null None declared
messageCount integer No 0 range 0..9223372036854776000

Indexes: