Introduction

Logical isolation is a key concept in software architecture, especially when dealing with modular monoliths. A modular monolith is an architectural style where an application is divided into distinct, loosely-coupled modules but still deployed as a single unit.

This approach allows for independent development and management of modules while keeping the simplicity of a single deployment. One way to achieve this is by using separate schemas within the same relational database. This means different modules have their own isolated data storage while being part of the same physical database system.

Why Logical Isolation?

In the simplest setup, all tables for all modules live inside one database without any logical separation. While this approach might work for small applications, as the application grows, it becomes very difficult to manage and maintain the database.

Logical isolation can:

• Define Boundaries: Each module has its own schema, making it easy to see which tables belong to which module.

• Reduce Risk: Isolating data for each module minimizes the risk of accidental data access or modification.

• Improve Maintainability: It simplifies database management by grouping related data together.

• Scale: You can easily add new modules by creating new schemas, ensuring that the database structure remains organized and scalable.

Key Concepts

• Separate Schema: Different modules (e.g., orders, users, finance) each have their own schema within the same relational database. Each schema contains tables and other database objects specific to the module it represents.

• Same Database: All schemas reside within the same database instance, simplifying database management and infrastructure.

• Same DB Type: All schemas use the same database management system (DBMS), ensuring consistency in terms of database features and capabilities.

Building The Schemas

Let's creat separate schemas for different modules. Imagine an e-commerce application with modules for orders, users, and finance.

First, create the schemas:

CREATE SCHEMA orders;
CREATE SCHEMA users;
CREATE SCHEMA finance;

Now, let's create the tables within each schema.

Orders Schema:

CREATE TABLE orders.orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    status VARCHAR(20)
);

CREATE TABLE orders.order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

Users Schema:

CREATE TABLE users.users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE users.user_profiles (
    profile_id INT PRIMARY KEY,
    user_id INT,
    address VARCHAR(255),
    phone_number VARCHAR(20)
);

Finance Schema:

CREATE TABLE finance.transactions (
    transaction_id INT PRIMARY KEY,
    order_id INT,
    amount DECIMAL(10, 2),
    transaction_date DATE
);

CREATE TABLE finance.invoices (
    invoice_id INT PRIMARY KEY,
    transaction_id INT,
    invoice_date DATE,
    total_amount DECIMAL(10, 2)
);

Let's insert now some data into each schema.

Orders Schema:

INSERT INTO orders.orders (order_id, user_id, order_date, status)
VALUES (1, 101, '2023-01-01', 'Pending'), (2, 102, '2023-01-02', 'Shipped');

INSERT INTO orders.order_items (item_id, order_id, product_id, quantity, price)
VALUES (1, 1, 201, 2, 250.00), (2, 2, 202, 1, 500.00);

Users Schema:

INSERT INTO users.users (user_id, username, email)
VALUES (101, 'johndoe', 'johndoe@example.com'), (102, 'janedoe', 'janedoe@example.com');

INSERT INTO users.user_profiles (profile_id, user_id, address, phone_number)
VALUES (1, 101, '123 Main St, Anytown, USA', '555-1234'), (2, 102, '456 Oak St, Anytown, USA', '555-5678');

Finance Schema:

INSERT INTO finance.transactions (transaction_id, order_id, amount, transaction_date)
VALUES (1, 1, 500.00, '2023-01-03'), (2, 2, 500.00, '2023-01-04');

INSERT INTO finance.invoices (invoice_id, transaction_id, invoice_date, total_amount)
VALUES (1, 1, '2023-01-05', 500.00), (2, 2, '2023-01-06', 500.00);

Now, let's see how we can query data within each schema.

Orders Schema:

SELECT * FROM orders.orders;
SELECT * FROM orders.order_items;

Users Schema:

SELECT * FROM users.users;
SELECT * FROM users.user_profiles;

Finance Schema:

SELECT * FROM finance.transactions;
SELECT * FROM finance.invoices;

A Word of Caution

While separate schemas provide logical isolation, nothing technically prevents cross-schema queries. This can lead to tight coupling if not managed carefully. Here’s an example to avoid:

SELECT o.order_id, u.username, f.amount
FROM orders.orders o
JOIN users.users u ON o.user_id = u.user_id
JOIN finance.transactions f ON o.order_id = f.order_id;

Cross-schema queries, like the one above, can undermine the logical isolation provided by separate schemas. While it might seem useful to join tables across different schemas, this practice can lead to coupling between modules, making the system harder to maintain and scale. It also increases the risk of data dependencies and inconsistencies.

Best Practices for Logical Isolation

• Enforce Module Boundaries: Establish clear guidelines to prevent unauthorized cross-schema querying. This can be enforced through database permissions and application-level checks.

• Database Roles and Permissions: Use database roles and permissions to restrict access to each schema, ensuring that only the relevant module has access to its own schema.

• Module-Specific APIs: Expose data access through module-specific APIs rather than direct database access. This further enforces separation and encapsulation of each module's data.

• Regular Reviews: Conduct regular reviews of database queries and access patterns to ensure compliance to the logical isolation principles.

• Document Schema Boundaries: Clearly document which schemas belong to which modules and enforce these boundaries in both code and practice.

Enforcing Isolation with API

To ensure that each module accesses only its own schema, you can create module-specific APIs that handle data operations. Here is an example of how you might do this using Flask and psycopg2 in Python.

API Endpoint for Orders Module:

from flask import Flask, jsonify
import psycopg2

app = Flask(__name__)

def get_db_connection():
    # Establishes a connection to the database
    conn = psycopg2.connect(
        dbname="your_database_name",
        user="your_db_user",
        password="your_db_password",
        host="your_db_host"
    )
    return conn

@app.route('/orders', methods=['GET'])
def get_orders():
    # Connect to the orders schema and fetch order data
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('SELECT * FROM orders.orders;')
    orders = cur.fetchall()
    cur.close()
    conn.close()
    # Returns the orders in JSON format
    return jsonify(orders)

if __name__ == '__main__':
    app.run()

API Endpoint for Users Module:

from flask import Flask, jsonify
import psycopg2

app = Flask(__name__)

def get_db_connection():
    # Establishes a connection to the database
    conn = psycopg2.connect(
        dbname="your_database_name",
        user="your_db_user",
        password="your_db_password",
        host="your_db_host"
    )
    return conn

@app.route('/users', methods=['GET'])
def get_users():
    # Connect to the users schema and fetch user data
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('SELECT * FROM users.users;')
    users = cur.fetchall()
    cur.close()
    conn.close()
    # Returns the users in JSON format
    return jsonify(users)

if __name__ == '__main__':
    app.run()

API Endpoint for Finance Module:

from flask import Flask, jsonify
import psycopg2

app = Flask(__name__)

def get_db_connection():
    # Establishes a connection to the database
    conn = psycopg2.connect(
        dbname="your_database_name",
        user="your_db_user",
        password="your_db_password",
        host="your_db_host"
    )
    return conn

@app.route('/transactions', methods=['GET'])
def get_transactions():
    # Connect to the finance schema and fetch transaction data
    conn = get_db_connection()
    cur = conn.cursor()
    cur.execute('SELECT * FROM finance.transactions;')
    transactions = cur.fetchall()
    cur.close()
    conn.close()
    # Returns the transactions in JSON format
    return jsonify(transactions)

if __name__ == '__main__':
    app.run()

• Database Connection: Each API function calls get_db_connection() to establish a connection to the database. This ensures that all database operations are performed within the context of a secure and controlled connection.

• Schema-Specific Queries: Each route (/orders, /users, /transactions) handles requests specific to its respective module. The SQL queries (e.g., SELECT * FROM orders.orders;) are scoped to their respective schemas, ensuring logical isolation.

• Returning JSON Responses: The results of the queries are fetched and returned as JSON responses using jsonify(). This allows for easy integration with front-end applications and other services.

• Preventing Cross-Schema Queries: By exposing data access through module-specific APIs, you prevent direct database access that could lead to cross-schema queries. This enforces logical isolation at the application level.

Application

In a real-world scenario, such APIs would be used by several parts of an organization to interact with the database. For instance:

Frontend Applications

A web or mobile app could call these APIs to display order details, user profiles, or financial transactions.

• Order Details Page: The frontend app can fetch and display user-specific order details by calling the /orders API.

• User Profile Page: The /users API can be used to fetch and display user profile information.

• Transaction History: Users can view their transaction history by fetching data from the /transactions API.

Microservices

Different microservices within the organization could use these APIs to interact with specific modules, ensuring that they only access the data they are supposed to.

• Order Processing Service: A microservice dedicated to order processing can use the /orders API to fetch and update order information.

• User Management Service: This service can call the /users API to manage user data.

• Financial Audit Service: For financial audits, the /transactions API can provide necessary transaction data.

Reporting Tools

Business intelligence tools could query these APIs to generate reports, ensuring that data remains consistent and isolated according to the schema boundaries.

• Sales Reports: Reporting tools can call the /orders API to generate detailed sales reports, track sales trends, and analyze order data.

• Customer Reports: By accessing the /users API, BI tools can create reports on customer demographics, user activity, and sign-up trends.

• Financial Reports: Financial performance and transaction analysis reports can be generated by querying the /transactions API.

Conclusion

Logical isolation through separate schemas within a modular monolith architecture provides a balanced approach to maintaining data separation while taking advantage from the simplicity of a single database instance.

By following this best practices and ensuring clear module boundaries, organizations can achieve a scalable, maintainable, and efficient data architecture. This approach not only improves data integrity and security but also independent module development and deployment, leading to more agile and reliable software solutions.

This strategy, combined with well-defined APIs, ensures that different parts of the organization can work independently yet balanced, maintaining a strong structure and reducing risks associated with data integration and management.