Database Schema #
This document outlines the database schema used in the BonsAI platform.
Overview #
The BonsAI platform uses a relational database to store all persistent data. The schema is designed to support the core features of the application while maintaining data integrity and performance.
Core Tables #
Users #
The users table stores information about user accounts.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| VARCHAR(255) | User’s email address (unique) | |
| password | VARCHAR(255) | Hashed password |
| name | VARCHAR(255) | User’s full name |
| created_at | TIMESTAMP | When the user was created |
| updated_at | TIMESTAMP | When the user was last updated |
| active | BOOLEAN | Whether the user account is active |
Entities #
The entities table stores information about entities in the system.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| name | VARCHAR(255) | Entity name |
| description | TEXT | Entity description |
| created_at | TIMESTAMP | When the entity was created |
| updated_at | TIMESTAMP | When the entity was last updated |
| owner_id | UUID | Reference to the owning user |
Documents #
The documents table stores information about uploaded documents.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| entity_id | UUID | Reference to the owning entity |
| name | VARCHAR(255) | Document name |
| file_path | VARCHAR(255) | Path to the stored file |
| mime_type | VARCHAR(100) | MIME type of the document |
| size | BIGINT | Size of the document in bytes |
| created_at | TIMESTAMP | When the document was created |
| updated_at | TIMESTAMP | When the document was last updated |
| processed | BOOLEAN | Whether the document was processed |
Invoices #
The invoices table stores information about processed invoices.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| document_id | UUID | Reference to the source document |
| entity_id | UUID | Reference to the owning entity |
| invoice_number | VARCHAR(100) | Invoice number |
| invoice_date | DATE | Invoice date |
| due_date | DATE | Payment due date |
| total_amount | DECIMAL | Total invoice amount |
| currency | VARCHAR(3) | Currency code (e.g., USD) |
| vendor_name | VARCHAR(255) | Name of the vendor |
| status | VARCHAR(50) | Processing status |
| created_at | TIMESTAMP | When the invoice was created |
| updated_at | TIMESTAMP | When the invoice was last updated |
Relationship Tables #
UserEntities #
The user_entities table manages the many-to-many relationship between users and entities.
| Column | Type | Description |
|---|---|---|
| user_id | UUID | Reference to the user |
| entity_id | UUID | Reference to the entity |
| role | VARCHAR(50) | User’s role within the entity |
| created_at | TIMESTAMP | When the relationship was created |
Integrations #
The integrations table stores information about third-party integrations.
| Column | Type | Description |
|---|---|---|
| id | UUID | Primary key |
| entity_id | UUID | Reference to the owning entity |
| type | VARCHAR(100) | Integration type |
| config | JSONB | Integration configuration |
| active | BOOLEAN | Whether the integration is active |
| created_at | TIMESTAMP | When the integration was created |
| updated_at | TIMESTAMP | When the integration was last updated |
Indexes #
The database uses the following indexes to optimize query performance:
- Users: email (unique)
- Documents: entity_id
- Invoices: entity_id, document_id
- UserEntities: (user_id, entity_id) (unique)
Database Migrations #
Database migrations are managed using a migration tool and versioned in the codebase. Each migration is applied in order to ensure database consistency.