Database Schema

Data Schema 🛢️

This schema is only for the Localbase database, since user can create their own database in their own project, which is dynamically created

Note: Changes to this document might be often, since table might need to be changed

Main Database (PostgresSQL) 🐘

User

• id (uint, PK, Auto-increment)
• email (varchar, unique, not null)
• password (varchar, not null)
• username (varchar, not null)
• role (varchar, not null)
• created_at (timestamp, not null)
• updated_at (timestamp, not null)

Project

• id (uint, PK, Auto-increment)
• name (varchar, not null)
• description (varchar)
• user_id(uint, FK)
• db_prefix (varchar, unique, not null)
• created_at (timestamp, not null)

Project User

• id (uint, PK, Auto-increment)
• project_id (uint, FK)
• user_id (uint, FK)
• role (varchar, not null)
• created_at (timestamp, not null)

Project Keys

• id (uint, PK, Auto-increment)
• project_id (uint, FK)
• name (varchar, not null)
• key_hash (varchar, unique, not null)
• key_prefix (varchar, not null)
• created_at (timestamp, not null)

Audit Database (SQLite) 🪶

Block

• hash (TEXT, PK)
• previous_hash (TEXT, not null)
• index (int, not null)
• timestamp (TEXT, not null)
• project_id (uint, FK)
• actor (TEXT, not null) (Note: take the user_id from user table)
• action (TEXT, not null) (Note: insert, update, delete)
• entity (TEXT, not null) (Note: take the table name from the table)
• record_id (TEXT, not null) (Note: take the row id from the table)
• data_hash (TEXT, not null) (Note: take the data from the table and hash it)