Purpose
This document defines the design of the primary relational database for the My One True Ally application.
Scope This document outlines the schema, fields, and relationships for all core RDB tables that support the application's key functionalities, including user management, AI personas, conversation history, and the list feature.
Background
My One True Ally is an AI companion application that provides personalized responses based on a user's past conversations and behavioral history. This database is intended to persist the data that supports this AI.
Technology Stack The database used is PostgreSQL, with Prisma serving as the ORM.
Manages user account information.
| Field Name | Type | PostgreSQL Type | Description |
|---|---|---|---|
id |
Int |
SERIAL |
Unique identifier for the user. (Primary Key) |
email |
String |
VARCHAR(255) |
The user's email address. (Unique) |
password_hash |
String |
VARCHAR(255) |
Hashed password. |
points |
Int |
INTEGER |
The user's current points balance. |
user_status |
Enum |
VARCHAR(255) |
The current status of the user account ('active', 'suspended', 'deleted'). |
last_name |
String? |
VARCHAR(255) |
The user's last name. (Nullable) |
first_name |
String? |
VARCHAR(255) |
The user's first name. (Nullable) |
middle_name |
String? |
VARCHAR(255) |
The user's middle name. (Nullable) |
age |
Int? |
INTEGER |
The user's age. (Nullable) |
gender_category |
Enum? |
VARCHAR(255) |
The user's gender category ('Male', 'Female', 'Other'). (Nullable) |
last_login_at |
DateTime? |
TIMESTAMP WITH TIME ZONE |
Timestamp of the user's last successful login. (Nullable) |
deleted_at |
DateTime? |
TIMESTAMP WITH TIME ZONE |
Timestamp of the account deletion request. (Nullable) |
created_at |
DateTime |
TIMESTAMP WITH TIME ZONE |
Timestamp when the user account was created. |
updated_at |
DateTime |
TIMESTAMP WITH TIME ZONE |
Timestamp of the last user account update. |
Manages AI persona information.
| Field Name | Type | PostgreSQL Type | Description |
|---|---|---|---|
id |
Int |
SERIAL |
Unique identifier for the persona. (Primary Key) |
name |
String |
VARCHAR(255) |
Persona's name (e.g., "Luke"). |
description |
String |
TEXT |
Overview of the persona's personality and tone. |
system_prompt |
String |
TEXT |
Detailed instructions for the AI persona. |
image_url |
String? |
VARCHAR(255) |
URL of the persona's image. (Nullable) |
user_id |
Int |
INTEGER |
ID of the user who created the persona. (Foreign Key) |
is_official |
Boolean |
BOOLEAN |
Flag indicating if it's an official persona. |
created_at |
DateTime |
TIMESTAMP WITH TIME ZONE |
Timestamp of persona creation. |
updated_at |
DateTime |
TIMESTAMP WITH TIME ZONE |
Timestamp of last persona update. |
Manages a collection of messages between a user and an AI.
| Field Name | Type | PostgreSQL Type | Description |
|---|---|---|---|
id |
Int | SERIAL | Unique identifier for the conversation. (Primary Key) |
user_id |
Int | INTEGER | ID of the user in the conversation. (Foreign Key) |
title |
String? | VARCHAR(255) | Title of the conversation. (Nullable) |
last_persona_id |
Int? | INTEGER | ID of the persona last used in this conversation. (Foreign Key, Nullable) |
last_user_summary |
String? | VARCHAR(50) | A 50-character summary of the last user message. (Nullable) |
last_user_keywords |
String[]? | TEXT[] | An array of important keywords from the last user message. (Nullable) |
created_at |
DateTime | TIMESTAMP WITH TIME ZONE | Timestamp when the conversation began. |
updated_at |
DateTime | TIMESTAMP WITH TIME ZONE | Timestamp of the last conversation update. |