Novaera SaaS ERP - Database Repository

Professional database schema and stored procedures for a modern SaaS ERP system

✨ Introduction

Novaera SaaS ERP Database is a comprehensive PostgreSQL database repository designed for a flexible, modular Enterprise Resource Planning (ERP) system. This repository contains the complete database schema, stored procedures, and business logic for managing a dynamic SaaS platform that allows users to create custom modules, tables, and data structures on the fly.

The system features a metadata-driven architecture where modules, tables, columns, and records are stored as data, enabling dynamic schema creation and management without traditional database migrations.

Key Features

🏗️ Dynamic Schema Management: Create modules, tables, and columns programmatically
👥 User & Role Management: Comprehensive authentication and authorization system
🔐 Permission System: Fine-grained CRUD permissions per role and table
📊 Custom Views: User-defined views with sorting and filtering capabilities
📝 JSONB Records: Flexible data storage using PostgreSQL JSONB
🔔 Notification System: Real-time notifications and scheduled reminders
📁 File Management: Built-in file storage and management
🧪 Test Coverage: Comprehensive test stored procedures included

🚀 Technologies Used

PostgreSQL - Advanced relational database with JSONB support
PL/pgSQL - Stored procedures and functions
SQL - Database schema and queries

⚙️ Installation

Prerequisites

PostgreSQL 12+ installed and running
Database user with CREATE privileges
Access to a PostgreSQL database instance

Setup Instructions

Clone the repository

git clone https://github.com/yourusername/novaera-saas-erp-db.git
cd novaera-saas-erp-db

Configure environment variables

cp .env.example .env
# Edit .env with your database credentials

Create the database
```
CREATE DATABASE erp_api;
```

Run the database creation script

psql -U your_username -d erp_api -f Db_Creation.sql

Load sample data (optional, for testing):

psql -U your_username -d erp_api -f Db_Seed_SampleData.sql

Install stored procedures (run in order):

# Users module
psql -U your_username -d erp_api -f Users/Users_Sp's.sql

# Roles module
psql -U your_username -d erp_api -f Roles/Roles_Sp's.sql

# Permissions module
psql -U your_username -d erp_api -f Permissions/Permissions_Sp's.sql

# Modules module
psql -U your_username -d erp_api -f Modules/Modules_Sp's.sql

# Tables module
psql -U your_username -d erp_api -f Tables/Tables_Sp's.sql

# Columns module
psql -U your_username -d erp_api -f Columns/Columns_Sp's.sql

# Records module
psql -U your_username -d erp_api -f Records/Records_Sp's.sql

# Notifications module
psql -U your_username -d erp_api -f Notifications/Notifications_Sp's.sql

# Views module
psql -U your_username -d erp_api -f Views/Views_Sp's.sql

# View Sorts module
psql -U your_username -d erp_api -f View_Sorts/View_Sorts_Ps's.sql

Run test procedures (optional, for validation):

psql -U your_username -d erp_api -f Users/Users_Sp's_TESTS.sql
# Repeat for other modules...

🧩 Project Structure

novaera-saas-erp-db/
│
├── Db_Creation.sql              # Main database schema creation script
├── Db_Seed_SampleData.sql       # Sample data for testing and development
│
├── Columns/                     # Column management stored procedures
│   ├── Columns_Sp's.sql
│   └── Columns_Sp's_TESTS.sql
│
├── Modules/                     # Module management stored procedures
│   ├── Modules_Sp's.sql
│   └── Modules_SP's_TESTS.sql
│
├── Notifications/               # Notification system stored procedures
│   ├── Notifications_Sp's.sql
│   └── Notifications_Sp's_TESTS.sql
│
├── Permissions/                 # Permission management stored procedures
│   ├── Permissions_Sp's.sql
│   └── Permissions_Sp's_TESTS.sql
│
├── Records/                     # Record (data) management stored procedures
│   ├── Records_Sp's.sql
│   ├── Record_Comments_Sp's.sql
│   └── Records_Sp's_tets.sql
│
├── Roles/                       # Role management stored procedures
│   ├── Roles_Sp's.sql
│   └── Roles_Sp's_TESTS.sql
│
├── Tables/                      # Table management stored procedures
│   ├── Tables_Sp's.sql
│   └── Tables_Sp's_TESTS.sql
│
├── Users/                       # User management stored procedures
│   ├── Users_Sp's.sql
│   └── Users_Sp's_TESTS.sql
│
├── Views/                       # View management stored procedures
│   └── Views_Sp's.sql
│
├── View_Sorts/                  # View sorting stored procedures
│   └── View_Sorts_Ps's.sql
│
├── .env.example                 # Environment variables template
├── .gitignore                   # Git ignore rules
├── LICENSE                      # Proprietary license
└── README.md                    # This file

Database Schema Overview

The database consists of the following main tables:

Core Tables

modules - System modules (e.g., Sales, Inventory, HR)
tables - Logical tables within modules
columns - Column definitions for logical tables
column_options - Custom options for select-type columns
records - Actual data stored as JSONB

User & Authentication Tables

users - User accounts and authentication
user_login_history - Login history tracking
roles - User roles (Admin, Manager, etc.)
user_roles - Many-to-many relationship between users and roles

Permission & Access Control Tables

permissions - CRUD permissions per role and table

Notification Tables

notifications - User notifications and reminders
scheduled_notifications - Scheduled notification system

View & Display Tables

views - Custom views for data display
view_columns - Column visibility and filters per view
view_sorts - Sorting configuration for views

Collaboration Tables

record_assigned_users - Users assigned to records
record_comments - Comments on records
record_subscriptions - User subscriptions to record notifications
table_collaborators - Collaborators for tables

Audit & Tracking Tables

record_changes - Audit log for record changes

File Management Tables

files - File storage metadata and binary data

📖 Usage Examples

Creating a Module

SELECT sp_crear_modulo(
    'Sales',
    'Sales and customer management module',
    'https://example.com/icons/sales.svg',
    1  -- created_by user_id
);

Creating a Table within a Module

SELECT crear_tabla_logica(
    1,  -- module_id
    'Customers',
    'Customer information table',
    NULL,  -- original_table_id
    NULL,  -- foreign_table_id
    0      -- position_num
);

Registering a User

SELECT sp_registrar_usuario(
    'John Doe',
    'john.doe@example.com',
    '$2b$10$hashedpassword...'  -- bcrypt hash
);

🚢 Deployment

Recommended Platforms

Render - PostgreSQL hosting (as seen in connection strings)
AWS RDS - Managed PostgreSQL service
Google Cloud SQL - Fully managed database service
Azure Database for PostgreSQL - Enterprise-grade hosting
DigitalOcean Managed Databases - Simple and scalable
Heroku Postgres - Easy deployment for development

Production Considerations

Backup Strategy: Implement regular automated backups
Connection Pooling: Use PgBouncer or similar for connection management
Monitoring: Set up database monitoring and alerting
Security: Use SSL connections and strong authentication
Performance: Consider read replicas for scaling
Migration Strategy: Use version control for schema changes

🔒 Security Best Practices

Always use parameterized queries (stored procedures handle this)
Hash passwords using bcrypt or similar
Implement rate limiting on authentication endpoints
Use SSL/TLS for database connections
Regularly audit permissions and roles
Keep PostgreSQL updated to the latest stable version

📜 License

Proprietary License

All rights reserved. Redistribution, modification, reproduction, sublicensing, or any form of transaction (including commercial, educational, or promotional use) involving this repository, its source code, or derived works is strictly prohibited without the explicit and personal written authorization of the Lead Developer, Steven Morales Fallas.

Unauthorized commercial use, resale, or licensing of this repository or its contents is strictly forbidden and will be subject to applicable legal action.

For licensing inquiries, please contact: Steven Morales Fallas

👤 Author

Steven Morales Fallas

Full Stack Developer specializing in SaaS ERP systems, database architecture, and scalable backend solutions.

🤝 Contributing

This is a proprietary project. Contributions are by invitation only. Please contact the author for collaboration opportunities.

📞 Support

For questions, issues, or licensing inquiries, please contact the repository owner.

Note: This database repository is part of a larger SaaS ERP system. Ensure proper integration with the corresponding API and frontend applications.

stevenmoraleszeta/novaera-saas-erp-db