Database Schema & Models
Relevant source files
The following files were used as context for generating this wiki page:
The RateGuru database schema is designed to support a community-driven rating platform using an "SQLite first" approach. The architecture centers around a flexible voting system that decouples core entities (Users, Posts) from specific rating criteria (Rating Groups, Options). This allows the application to transition from a specialized food-rating tool to a generic rating platform where users can evaluate "Posts" based on various configurable attributes.
The model layer utilizes Laravel Eloquent to define relationships, attribute casting, and business logic scopes. Key features include support for soft deletes on primary entities, complex many-to-many relationships for tagging and saving content, and a comprehensive voting architecture that tracks upvotes, downvotes, and category-specific selections.
Sources: AGENTS.md:7, README.md:10, docs/domain/generic-rating-vocabulary.md, app/Models/Post.php:21-23
Core Entities
The system revolves around three primary entities: Users, Posts, and Tags. These form the foundation for content creation, attribution, and organization.
User Model
The User model manages authentication and authorization. It includes attributes for roles (User, Moderator, Admin) and status (Active, Limited, Banned, Shadowbanned). Users also possess a trust_level which determines if their posts are published immediately or sent to moderation.
Sources: app/Filament/Resources/Users/UserResource.php:57-89, database/seeders/DemoFillSeeder.php:127-142
Post Model
The Post model represents the primary unit of content. It tracks ownership (user_id), content metadata (title, description), and media references (image_path, image_url). It implements several local scopes for filtering content based on status and engagement metrics.
| Field | Type | Description |
|---|---|---|
status | PostStatus (Enum) | Current state (Pending, Published, Hidden) |
upvotes_count | Integer | Total upvotes received |
downvotes_count | Integer | Total downvotes received |
comments_count | Integer | Total comments count |
hot_score | Float | Calculated ranking score |
needs_review | Boolean | Flag for moderation |
Sources: app/Models/Post.php:25-42, database/seeders/DemoFillSeeder.php:164-184
Entity Relationships
The following diagram illustrates the primary relationships between Users, Posts, and their associated metadata.
The diagram shows the standard one-to-many relationships for authorship and interactions, alongside the many-to-many relationship for tagging.
Sources: app/Models/Post.php:74-110, database/seeders/DemoFillSeeder.php:394-428
Voting & Rating System
RateGuru implements a multi-layered voting architecture that separates simple engagement (upvotes/downvotes) from qualitative ratings.
Configurable Ratings
The system uses RatingGroups and RatingOptions to define evaluateable criteria. This allows the platform to support diverse rating types without modifying the schema.
- Rating Groups: Categorical buckets (e.g., "Source", "Quality").
- Rating Options: Selectable values within a group (e.g., "Homemade", "Restaurant").
- Rating Votes: The junction record linking a user's selection for a specific group on a specific post.
Sources: docs/rating/configurable-voting.md, tests/Feature/Database/RatingVotesTableTest.php:10-21
Database Integrity Constraints
To ensure data consistency, the rating_votes table enforces strict unique constraints and foreign key relationships.
// Constraint: One vote per user, post, and rating group
Schema::create('rating_votes', function (Blueprint $table) {
$table->id();
$table->foreignId('post_id')->constrained()->cascadeOnDelete();
$table->foreignId('user_id')->constrained()->cascadeOnDelete();
$table->foreignId('rating_group_id')->constrained();
$table->foreignId('rating_option_id')->constrained();
$table->unique(['user_id', 'post_id', 'rating_group_id']);
});
Sources: tests/Feature/Database/RatingVotesTableTest.php:11-33, docs/rating/configurable-voting.md
Taxonomy & Metadata
Content organization is handled through a flexible tagging system and localized truth attributes on posts.
Tagging System
Tags are linked to posts via a many-to-many relationship. This allows for horizontal discovery across different post authors and types.
Sources: app/Models/Post.php:110, tests/Feature/Filament/TagResourceTest.php:103-109
Image & Media Handling
Posts support both local storage paths and external URLs. The public_image_url attribute provides a unified interface for retrieving the correct media asset.
protected function publicImageUrl(): Attribute
{
return Attribute::make(
get: function (): ?string {
$path = trim((string) $this->image_path);
if ($path !== '') {
return '/storage/'.ltrim($path, '/');
}
$url = trim((string) $this->image_url);
return $url !== '' ? $url : null;
},
);
}
Sources: app/Models/Post.php:134-149
Engagement & Ranking Logic
The schema supports real-time engagement tracking through counter columns and calculated scores.
Counter Recalculation
Engagement metrics (votes, comments) are stored directly on the posts table to optimize read performance. These counters are periodically synchronized using specialized Action classes.
Sources: database/seeders/DemoFillSeeder.php:571-581, app/Models/Post.php:121-126
Ranking Flow
The following flow describes how user interactions influence post visibility via the hot_score.
Engagement data flows from individual vote and comment records into aggregated columns on the Post model, which then feeds the ranking algorithm.
Sources: app/Models/Post.php:63-66, database/seeders/DemoFillSeeder.php:571-581
The RateGuru database schema effectively balances normalized relational data for integrity with denormalized counters for performance, specifically tailored for a high-engagement community platform.