Professional database schema and stored procedures for a modern SaaS ERP system
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.
- 🏗️ 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
- PostgreSQL - Advanced relational database with JSONB support
- PL/pgSQL - Stored procedures and functions
- SQL - Database schema and queries
- PostgreSQL 12+ installed and running
- Database user with CREATE privileges
- Access to a PostgreSQL database instance
-
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...
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
The database consists of the following main 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
- 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
- permissions - CRUD permissions per role and table
- notifications - User notifications and reminders
- scheduled_notifications - Scheduled notification system
- views - Custom views for data display
- view_columns - Column visibility and filters per view
- view_sorts - Sorting configuration for views
- record_assigned_users - Users assigned to records
- record_comments - Comments on records
- record_subscriptions - User subscriptions to record notifications
- table_collaborators - Collaborators for tables
- record_changes - Audit log for record changes
- files - File storage metadata and binary data
SELECT sp_crear_modulo(
'Sales',
'Sales and customer management module',
'https://example.com/icons/sales.svg',
1 -- created_by user_id
);SELECT crear_tabla_logica(
1, -- module_id
'Customers',
'Customer information table',
NULL, -- original_table_id
NULL, -- foreign_table_id
0 -- position_num
);SELECT sp_registrar_usuario(
'John Doe',
'john.doe@example.com',
'$2b$10$hashedpassword...' -- bcrypt hash
);- 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
- 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
- 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
Proprietary License
Copyright (c) 2025 Steven Morales Fallas
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
Steven Morales Fallas
Full Stack Developer specializing in SaaS ERP systems, database architecture, and scalable backend solutions.
This is a proprietary project. Contributions are by invitation only. Please contact the author for collaboration opportunities.
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.