Database Schema - CricTalk

Database Overview

Database Name: cric_talk
Database ID: 695761d00008fd927f78
Platform: Appwrite TablesDB
Status: Enabled CricTalk uses Appwrite’s TablesDB with row-level security for access control. The schema consists of 7 tables handling posts, comments, chat rooms, users, notifications, and rate limiting.

Schema Relationships

Table: `posts`

Table ID: posts
Row Security: Enabled
Description: User-generated posts with engagement tracking

Columns

content

string

required

Post content text

Max Size: 512 characters
Indexed: Full-text search enabled

image

string[]

Array of image URLs or file IDs

Max Size: 4 items
Element Size: Standard string

authorId

string

required

Reference to the post creator’s user ID

Max Size: 36 characters (UUID)
Indexed: Ascending index for author queries
Relationship: users.$id

authorName

string

required

Denormalized author username for display

Max Size: 36 characters
Purpose: Avoid joins on user table for list views

likes

integer

required

Total like count

Range: -9223372036854775808 to 9223372036854775807
Default: 0
Indexed: Descending for popularity sorting

likedBy

string[]

Array of user IDs who liked the post

Max Size: 1024 items
Element Size: Standard string
Purpose: Track like status per user, prevent duplicate likes

views

integer

required

Total view count

Range: -9223372036854775808 to 9223372036854775807
Default: 0
Indexed: Descending for trending posts

viewedBy

string[]

Array of user IDs who viewed the post

Max Size: 1024 items
Element Size: Standard string
Purpose: Ensure unique view counting

commentCount

integer

required

Total number of comments

Range: -9223372036854775808 to 9223372036854775807
Default: 0
Indexed: Ascending
Updated: Incremented via comments-guard function

Indexes

authorId_index

Type: Key Index
Column: authorId
Order: ASC
Use Case: Fetch all posts by specific user

likes_index

Type: Key Index
Column: likes
Order: DESC
Use Case: Sort posts by popularity (most liked first)

views_index

Type: Key Index
Column: views
Order: DESC
Use Case: Trending posts, most viewed content

commentCount_index

Type: Key Index
Column: commentCount
Order: ASC
Use Case: Filter posts by engagement level

createdAt_index

Type: Key Index
Column: $createdAt (system field)
Order: DESC
Use Case: Chronological feed (newest first)

updatedAt_index

Type: Key Index
Column: $updatedAt (system field)
Order: DESC
Use Case: Recently edited posts

index_7 (Full-Text)

Type: Full-Text Index
Column: content
Order: ASC
Use Case: Search posts by content keywords

Row-Level Security

Permissions:

Read: Role.users() - Any authenticated user can read
Update: Role.user(authorId) - Only post author can update
Delete: Role.user(authorId) - Only post author can delete

Table: `comments`

Table ID: comments
Row Security: Enabled
Description: Comments on posts

Columns

postId

string

required

ID of the parent post

Max Size: 36 characters (UUID)
Indexed: Ascending for post-comment lookups
Relationship: posts.$id

authorId

string

required

ID of the comment author

Max Size: 36 characters (UUID)
Relationship: users.$id

content

string

required

Comment text content

Max Size: 512 characters

isEdited

boolean

required

Flag indicating if comment has been modified

Default: false
Purpose: Show “edited” indicator in UI

Indexes

index_postId

Type: Key Index
Column: postId
Order: ASC
Use Case: Fetch all comments for a specific post

index_createdAt

Type: Key Index
Column: $createdAt (system field)
Order: DESC
Use Case: Display comments chronologically

Row-Level Security

Permissions:

Read: Role.users() - Any authenticated user
Update: Role.user(authorId) - Only comment author
Delete: Role.user(authorId) - Only comment author

Table: `rooms`

Table ID: rooms
Row Security: Enabled
Description: Live chat rooms for cricket match discussions

Columns

teams

string[]

Array of team names playing in the match

Max Size: Variable
Element Size: 36 characters
Example: ["India", "Australia"]

startTime

datetime

required

Match start date and time

Format: ISO 8601 datetime
Purpose: Schedule room availability

endTime

datetime

Match end date and time

Format: ISO 8601 datetime
Purpose: Auto-close rooms after match

matchType

string

required

Type of cricket match

Max Size: 32 characters
Examples: “T20”, “ODI”, “Test”

isLocked

boolean

required

Whether the room is locked (no new messages)

Default: false
Purpose: Moderate or close rooms

authorName

string

required

Username of room creator

Max Size: 36 characters

authorId

string

required

User ID of room creator

Max Size: 36 characters (UUID)
Relationship: users.$id

Indexes

No custom indexes defined (relies on system indexes).

Row-Level Security

Permissions:

Read: Role.users() - Any authenticated user
Update: Role.user(authorId) - Only room creator
Delete: Role.user(authorId) - Only room creator

Table: `roomMessaage`

Table ID: roommessaage (note: typo in original schema)
Row Security: Enabled
Description: Messages within chat rooms

The table ID contains a typo: roommessaage instead of roomMessage. This should be noted when querying.

Columns

roomId

string

required

ID of the parent room

Max Size: 36 characters (UUID)
Relationship: rooms.$id

authorId

string

required

ID of the message sender

Max Size: 36 characters (UUID)
Relationship: users.$id

authorName

string

required

Username of message sender (denormalized)

Max Size: 36 characters

content

string

required

Message text content

Max Size: 512 characters

isEdited

boolean

required

Whether the message has been edited

Default: false

Indexes

No custom indexes defined.

Row-Level Security

Permissions:

Read: Role.users() - Any authenticated user in room
Update: Role.user(authorId) - Only message author
Delete: Role.user(authorId) - Only message author

Table: `users`

Table ID: users
Row Security: Disabled
Description: Extended user profile data (supplements Appwrite auth users)

This table stores additional user data beyond what Appwrite’s built-in auth system provides. It does NOT have row security enabled, allowing global read access for user lookups.

Columns

username

string

required

User’s display name

Max Size: 36 characters
Source: From Appwrite user.name or email prefix

messageCount

integer

required

Total number of messages sent by user

Range: -9223372036854775808 to 9223372036854775807
Default: 0
Purpose: Leaderboard and activity tracking

pushTokens

string[]

Array of Expo push notification tokens

Max Size: Variable
Element Size: 120 characters (Expo token format)
Purpose: Support multiple devices per user
Example: ["ExponentPushToken[xxxxx]", "ExponentPushToken[yyyyy]"]

Indexes

No custom indexes defined.

Row-Level Security

Status: Disabled
Reasoning: User profiles are publicly readable for displaying usernames, message counts, etc. Push tokens are managed server-side only.

Table: `notifications`

Table ID: notifications
Row Security: Enabled
Description: In-app notification records

Columns

userId

string

required

ID of the user receiving the notification

Max Size: 36 characters (UUID)
Indexed: Ascending for user notification queries
Relationship: users.$id

title

string

required

Notification title/heading

Max Size: 32 characters
Examples: “New Comment”, “Post Liked”

content

string

required

Notification body text

Max Size: 512 characters
Examples: “John commented on your post”, “Your post received 10 likes”

Indexes

userId_index

Type: Key Index
Column: userId
Order: ASC
Use Case: Fetch all notifications for a user

createdAt_index

Type: Key Index
Column: $createdAt (system field)
Order: DESC
Use Case: Display notifications in chronological order (newest first)

Row-Level Security

Permissions:

Read: Role.user(userId) - Only the recipient can read their notifications
Update: None (notifications are read-only after creation)
Delete: Role.user(userId) - User can clear their notifications

Table: `rateLimit`

Table ID: ratelimit
Row Security: Disabled
Description: Rate limiting tracking using sliding window algorithm

Columns

userId

string

required

ID of the user being rate limited

Max Size: 36 characters (UUID)
Indexed: Ascending for user lookups
Relationship: users.$id

activity

string

required

Type of activity being rate limited

Max Size: 36 characters
Indexed: Ascending for activity-specific queries
Values: create_post, update_post, delete_post, create_comment, send_message, etc.

windowKey

integer

required

Time window identifier (Unix timestamp / 60000)

Range: -9223372036854775808 to 9223372036854775807
Indexed: Ascending for window lookups
Calculation: Math.floor(Date.now() / 60000)
Purpose: Groups requests into 1-minute windows

activityCount

integer

required

Number of requests in current window

Range: 1 to 60
Default: 1
Limit: 10 for most activities
Purpose: Track request count per window

Indexes

userid_index

Type: Key Index
Column: userId
Order: ASC
Use Case: Lookup user’s rate limit records

activity_index

Type: Key Index
Column: activity
Order: ASC
Use Case: Filter by activity type

windowKey_index

Type: Key Index
Column: windowKey
Order: ASC
Use Case: Query current time window

createdAt_index

Type: Key Index
Column: $createdAt (system field)
Order: DESC
Use Case: Clean up old rate limit records

Row-Level Security

Status: Disabled
Reasoning: Rate limit checks are performed server-side via guard functions. Users don’t directly access this table.

Algorithm Details

Window Calculation

Current window key: Math.floor(Date.now() / 60000)This creates 1-minute time windows that reset every 60 seconds.

Lookup

Query for existing record matching: userId + activity + windowKey

First Request

If no record exists, create new row with activityCount = 1

Subsequent Requests

If record exists:

Check if activityCount >= 10
If yes, reject with rate limit error
If no, increment activityCount

Window Expiry

When the minute changes, windowKey changes, creating a new window. Old records naturally expire.

System Fields

All tables include Appwrite’s standard system fields:

$id

string

Unique document ID (UUID format)

$createdAt

datetime

ISO 8601 timestamp of document creation

$updatedAt

datetime

ISO 8601 timestamp of last update

$permissions

string[]

Array of permission strings for row-level access control

Migration & Backup

Appwrite TablesDB does not have built-in migration tooling. Schema changes must be performed carefully through the Appwrite console or CLI.

Best Practices

Schema Changes: Use Appwrite CLI to version control schema changes
Backups: Export table data regularly using Appwrite’s export functionality
Indexes: Add indexes before launching features that query large datasets
Data Integrity: Use serverless functions to enforce foreign key relationships

Performance Considerations

Denormalization Strategy: CricTalk denormalizes authorName in posts, comments, and room messages to avoid expensive joins when displaying lists. This trades storage for query performance.

Optimization Tips

Use Indexes: All query filters should use indexed columns
Limit Results: Always use Query.limit() for list views
Pagination: Use Query.offset() or cursor-based pagination for large datasets
Full-Text Search: The posts.content full-text index enables fast keyword search
Caching: Client-side Zustand stores cache frequently accessed data

Query Examples

await tablesDB.listRows({
  databaseId: 'cric_talk',
  tableId: 'posts',
  queries: [
    Query.equal('authorId', userId),
    Query.orderDesc('$createdAt'),
    Query.limit(20)
  ]
});

Get Started

Core Features

User Guide

Technical

​Database Overview

​Schema Relationships

​Table: posts

​Columns

​Indexes

​Row-Level Security

​Table: comments

​Columns

​Indexes

​Row-Level Security

​Table: rooms

​Columns

​Indexes

​Row-Level Security

​Table: roomMessaage

​Columns

​Indexes

​Row-Level Security

​Table: users

​Columns

​Indexes

​Row-Level Security

​Table: notifications

​Columns

​Indexes

​Row-Level Security

​Table: rateLimit

​Columns

​Indexes

​Row-Level Security

​Algorithm Details

​System Fields

​Migration & Backup

​Best Practices

​Performance Considerations

​Optimization Tips

​Query Examples

Build docs developers (and LLMs) love

Database Overview

Schema Relationships

Table: `posts`

Columns

Indexes

Row-Level Security

Table: `comments`

Columns

Indexes

Row-Level Security

Table: `rooms`

Columns

Indexes

Row-Level Security

Table: `roomMessaage`

Columns

Indexes

Row-Level Security

Table: `users`

Columns

Indexes

Row-Level Security

Table: `notifications`

Columns

Indexes

Row-Level Security

Table: `rateLimit`

Columns

Indexes

Row-Level Security

Algorithm Details

System Fields

Migration & Backup

Best Practices

Performance Considerations

Optimization Tips

Query Examples