Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• apps/backend/src/users/entities/user.entity.ts
• apps/backend/src/signals/entities/signal.entity.ts
• apps/backend/src/forum_module/entities/thread.entity.ts
• apps/backend/src/reputation/entities/reputation.entity.ts
• apps/backend/src/notification/entities/notification.entity.ts
• apps/backend/src/forum_module/entities/post.entity.ts
• apps/backend/src/forum_module/entities/reply.entity.ts
• apps/backend/src/forum_module/entities/report.entity.ts
• apps/backend/src/analytics/entities/analytics-summary.entity.ts

The OnChainSage backend uses a PostgreSQL database managed with TypeORM. The schema is designed to be modular, supporting core application features such as user management, trading signals, a community forum, reputation tracking, and notifications. Each module corresponds to a distinct set of database tables, defined as entities within the NestJS application.

This document provides an overview of the main database entities and their relationships, which form the data persistence layer of the application.

Entity Relationship Diagram

The following diagram illustrates the high-level relationships between the primary entities in the OnChainSage database.

Core Entities

User Entity

The User entity represents a user of the platform. Authentication is primarily handled via a Starknet wallet address.

Sources: apps/backend/src/users/entities/user.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the user.
walletAddress	string	Unique, Not Null	The user's Starknet wallet address.
username	string	Unique, Nullable	The user's chosen display name.
email	string	Unique, Nullable	The user's email address.
role	string	Default: 'user'	User role (e.g., 'user', 'admin').
createdAt	timestamp	Not Null	Timestamp of user creation.
updatedAt	timestamp	Not Null	Timestamp of the last update.

Relationships:

• Has one Reputation record.
• Has many Notification records.
• Authors many Post, Reply, and Report records in the forum.

Signal Entity

The Signal entity stores trading signals generated by the system. These signals provide actionable insights for crypto traders.

Sources: apps/backend/src/signals/entities/signal.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the signal.
asset	string	Not Null	The crypto asset or ticker.
type	enum	'buy' or 'sell'	The type of trading signal.
confidence	float	Default: 0	A score indicating the signal's reliability.
source	string	Not Null	The data source of the signal (e.g., 'Twitter', 'OnChain').
targetPrice	decimal	Nullable	The predicted target price.
stopLoss	decimal	Nullable	The recommended stop-loss price.
status	string	Default: 'active'	The current status of the signal (e.g., 'active', 'expired').
createdAt	timestamp	Not Null	Timestamp of signal creation.
updatedAt	timestamp	Not Null	Timestamp of the last update.

Relationships:

• Has many Vote records (from reputation module).
• Has many ReputationAdjustment records.

Reputation Entity

The Reputation entity tracks a user's reputation score, which can be influenced by their activity and the accuracy of their validations on the platform.

Sources: apps/backend/src/reputation/entities/reputation.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the reputation record.
user	User	One-to-One	The user associated with this reputation score.
score	integer	Default: 0	The user's numerical reputation score.
level	string	Default: 'novice'	The user's reputation level (e.g., 'novice', 'expert').
last_updated	timestamp	Not Null	Timestamp of the last reputation update.

Relationships:

• Belongs to one User.

Notification Entity

The Notification entity stores notifications to be delivered to users through various channels.

Sources: apps/backend/src/notification/entities/notification.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the notification.
user	User	Many-to-One	The recipient of the notification.
title	string	Not Null	The title of the notification.
content	text	Not Null	The main body of the notification.
status	enum	'sent', 'read', 'failed'	The delivery status of the notification.
channels	NotificationChannel[]	Array	Channels for delivery (e.g., 'email', 'in_app').
readAt	timestamp	Nullable	Timestamp when the user read the notification.
createdAt	timestamp	Not Null	Timestamp of notification creation.

Relationships:

• Belongs to one User.

Analytics Summary Entity

The AnalyticsSummary entity stores aggregated platform-wide statistics.

Sources: apps/backend/src/analytics/entities/analytics-summary.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the summary record.
totalUsers	integer	Default: 0	Total number of registered users.
totalSignals	integer	Default: 0	Total number of trading signals created.
totalVotes	integer	Default: 0	Total number of votes cast.
totalStaked	decimal	Default: 0	Total amount of currency staked on signals.
lastUpdated	timestamp	Nullable	Timestamp of the last summary update.
createdAt	timestamp	Not Null	Timestamp of record creation.
updatedAt	timestamp	Not Null	Timestamp of the last update.

Forum Module

The forum module enables community discussion and is composed of several related entities.

The diagram below shows the relationships within the forum module.

Thread Entity

A Thread is the top-level container for a discussion topic.

Sources: apps/backend/src/forum_module/entities/thread.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the thread.
title	string	Not Null	The title of the discussion thread.
posts	Post[]	One-to-Many	A collection of posts within this thread.
createdAt	timestamp	Not Null	Timestamp of thread creation.

Post Entity

A Post is an individual entry within a Thread, created by a user.

Sources: apps/backend/src/forum_module/entities/post.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the post.
thread	Thread	Many-to-One, onDelete: 'CASCADE'	The parent thread.
author	User	Many-to-One	The user who created the post.
content	text	Not Null	The content of the post.
spamScore	float	Default: 0	A score indicating the likelihood of being spam.
hidden	boolean	Default: false	Whether the post is hidden from view.
replies	Reply[]	One-to-Many	A collection of replies to this post.
reports	Report[]	One-to-Many	Reports made against this post.
createdAt	timestamp	Not Null	Timestamp of post creation.

Reply Entity

A Reply is a comment made in response to a Post.

Sources: apps/backend/src/forum_module/entities/reply.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the reply.
post	Post	Many-to-One, onDelete: 'CASCADE'	The parent post.
author	User	Many-to-One	The user who created the reply.
content	text	Not Null	The content of the reply.
reports	Report[]	One-to-Many	Reports made against this reply.
createdAt	timestamp	Not Null	Timestamp of reply creation.

Report Entity

A Report is created by a user to flag a Post or Reply for moderation.

Sources: apps/backend/src/forum_module/entities/report.entity.ts

Field	Type	Constraints	Description
id	uuid	Primary Key	Unique identifier for the report.
post	Post	Many-to-One, Nullable, onDelete: 'CASCADE'	The post being reported.
reply	Reply	Many-to-One, Nullable, onDelete: 'CASCADE'	The reply being reported.
reporter	User	Many-to-One	The user who created the report.
reason	text	Not Null	The reason for the report.
createdAt	timestamp	Not Null	Timestamp of report creation.