Introduction to the Database

This document provides comprehensive details about the MS internal database. It covers the core schemas, tables, relationships, and the API endpoints used to interact with the data.

Our database is designed for high performance, scalability, and data integrity. It leverages advanced features to ensure efficient data retrieval and manipulation.

Database Schemas

The database is organized into logical schemas to segregate different functional areas.

public Schema

Contains core application data and common utilities.

  • Tables: users, products, orders
  • Views: active_users

audit Schema

Stores audit logs and historical data for compliance and analysis.

  • Tables: login_history, data_changes

Key Tables

Here are some of the primary tables within the database:

users Table

Stores information about registered users.

Column Name Data Type Constraints Description
user_id UUID PRIMARY KEY Unique identifier for the user.
username VARCHAR(255) NOT NULL, UNIQUE User's chosen username.
email VARCHAR(255) NOT NULL, UNIQUE User's email address.
password_hash VARCHAR(255) NOT NULL Hashed password for security.
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP Timestamp when the user was created.
is_active BOOLEAN DEFAULT TRUE Indicates if the user account is active.

products Table

Contains details about available products.

Column Name Data Type Constraints Description
product_id UUID PRIMARY KEY Unique identifier for the product.
name VARCHAR(255) NOT NULL Name of the product.
description TEXT Detailed description of the product.
price DECIMAL(10, 2) NOT NULL Price of the product.
stock_quantity INTEGER NOT NULL, DEFAULT 0 Current quantity in stock.

Database Indexes

Indexes are crucial for optimizing query performance. Key indexes include:

  • users table: Indexes on username and email for fast lookups.
  • products table: Indexes on name for product searches and stock_quantity for stock management.

Database Views

Views provide a simplified and focused perspective on the data.

active_users View

This view selects active users from the users table.

CREATE VIEW public.active_users AS
SELECT user_id, username, email, created_at
FROM public.users
WHERE is_active = TRUE;

Stored Procedures

Custom stored procedures can be used for complex operations or business logic.

create_order Procedure

A procedure to create a new order, ensuring stock updates.

(Details of the stored procedure logic can be complex and are often simplified in documentation. Refer to actual database scripts for full implementation.)

Database API Endpoints

The following RESTful API endpoints allow programmatic access to the database.

GET /users

Retrieves a list of all users. Supports filtering and pagination.

Query Parameters:
  • page (integer): Page number for pagination.
  • limit (integer): Number of items per page.
  • sortBy (string): Field to sort by (e.g., username, created_at).
  • sortOrder (string): asc or desc.
GET /users?page=1&limit=20&sortBy=created_at&sortOrder=desc
Response:
{
    "data": [
        {
            "user_id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
            "username": "johndoe",
            "email": "john.doe@example.com",
            "created_at": "2023-01-15T10:00:00Z",
            "is_active": true
        },
        // ... more users
    ],
    "pagination": {
        "currentPage": 1,
        "totalPages": 5,
        "totalItems": 95
    }
}

GET /users/{user_id}

Retrieves details for a specific user by their ID.

Path Parameters:
  • user_id (UUID): The ID of the user to retrieve.
Response:
{
    "user_id": "a1b2c3d4-e5f6-7890-1234-567890abcdef",
    "username": "johndoe",
    "email": "john.doe@example.com",
    "created_at": "2023-01-15T10:00:00Z",
    "is_active": true
}

POST /users

Creates a new user.

Request Body:
{
    "username": "newuser",
    "email": "new.user@example.com",
    "password": "securepassword123"
}
Response (201 Created):
{
    "message": "User created successfully",
    "user_id": "f9e8d7c6-b5a4-3210-fedc-ba9876543210"
}

PUT /users/{user_id}

Updates an existing user's information.

Path Parameters:
  • user_id (UUID): The ID of the user to update.
Request Body:
{
    "email": "john.doe.updated@example.com",
    "is_active": false
}
Response:
{
    "message": "User updated successfully"
}

DELETE /users/{user_id}

Deactivates or deletes a user.

Path Parameters:
  • user_id (UUID): The ID of the user to delete.
Response:
{
    "message": "User deactivated successfully"
}

Details for products, orders, and other resources follow a similar pattern.