Database Schema & Models

The Gova backend utilizes PostgreSQL with the PgVector extension to handle relational data and high-dimensional vector embeddings for content detection. The data layer is managed using SQLAlchemy (Async) with a focus on tracking user behavior and automated moderation history.

Data Architecture

The schema is designed around four primary entities:

1. Users: Account management and platform connections.
2. Moderators: AI-driven agents configured for specific community platforms.
3. EvaluationEvents: Records of AI content analysis and severity scoring.
4. ActionEvents: Automated or manual escalations (e.g., kicks, timeouts) resulting from evaluations.

Core Models

Users

The Users table stores authentication data, subscription status via Stripe, and encrypted OAuth tokens for third-party platforms like Discord.

| Field | Type | Description | | :--- | :--- | :--- | | user_id | UUID | Primary key (generated via UUIDv7). | | username | String | Unique display name. | | pricing_tier | String | User's subscription level (FREE, PRO). | | discord_oauth_payload | String | Encrypted OAuth tokens for Discord API access. | | stripe_customer_id | String | Reference for payment processing. |

Moderators

A Moderator represents an instance of a Gova agent active on a specific server or guild. It contains the logic configuration (conf) that dictates how the AI should behave.

| Field | Type | Description | | :--- | :--- | :--- | | moderator_id | UUID | Primary key. | | user_id | UUID | Foreign key to the owner (Users). | | platform | Enum | The targeted platform (e.g., DISCORD). | | platform_server_id| String | The ID of the server/guild being moderated. | | conf | JSONB | Crucial: AI configuration, including guidelines and personality. | | status | Enum | Current agent state (ONLINE, OFFLINE). |

EvaluationEvents

Every message processed by the system generates an EvaluationEvent. This model stores the AI's analysis of the content, including its severity and the calculated behavior score of the message author.

| Field | Type | Description | | :--- | :--- | :--- | | event_id | UUID | Primary key. | | moderator_id | UUID | Foreign key to the active Moderator. | | platform_user_id | String | The platform-specific ID of the person who sent the message. | | severity_score | Float | AI-generated score between 0.0 (Safe) and 1.0 (Critical). | | behaviour_score | Float | Cumulative impact on the user's reputation. | | context | JSONB | A snapshot of the original message content and metadata. |

ActionEvents

When an evaluation triggers a threshold, or a human moderator intervenes, an ActionEvent is created. This tracks the execution of moderation tools (kicks, bans, replies).

| Field | Type | Description | | :--- | :--- | :--- | | action_id | UUID | Primary key. | | event_id | UUID | Foreign key to the triggering EvaluationEvent. | | action_type | String | The action performed (e.g., kick, timeout, reply). | | status | Enum | Status of the action (AWAITING_APPROVAL, COMPLETED, FAILED, REJECTED). | | action_params | JSONB | Parameters for the action (e.g., timeout duration). |

Constants & Enums

The system uses several enumerations to maintain data integrity across the platform:

# enums.py (Summary)
class MessagePlatform(str, Enum):
    DISCORD = "discord"

class ActionStatus(str, Enum):
    AWAITING_APPROVAL = "awaiting_approval"
    COMPLETED = "completed"
    FAILED = "failed"
    REJECTED = "rejected"

class ModeratorStatus(str, Enum):
    ONLINE = "online"
    OFFLINE = "offline"

Usage Example: Querying Data

To interact with the database, use the AsyncSession provided by the API dependencies. Below is an example of fetching a moderator and its recent evaluations.

from sqlalchemy import select
from sqlalchemy.orm import selectinload
from db_models import Moderators, EvaluationEvents

async def get_moderator_activity(db_sess: AsyncSession, mod_id: UUID):
    query = (
        select(Moderators)
        .where(Moderators.moderator_id == mod_id)
        .options(selectinload(Moderators.evaluations)) # Eager load evaluations
    )
    result = await db_sess.execute(query)
    moderator = result.scalar_one_or_none()
    
    return moderator.evaluations

Vector Search (PgVector)

While standard relational queries handle management, the EvaluationEvents are indexed using PgVector for similarity searches. This allows Gova to identify "Trend Clusters"—recurring toxic themes or coordinated spam across different channels by comparing the high-dimensional embeddings of message contexts.