Database Schema

Each tenant’s PostgreSQL database is structured to balance isolation, reusability, and single source of truth principles.

Users

1. Admin User

   • Common across all tenants.
   • Credentials are securely stored in GKE Secrets.
   • Used internally by node service for provisioning, migrations, and monitoring.

2. Tenant-Specific User

   • Generated at tenant creation time.
   • Credentials are stored in Aventian Meta DB in encrypted format.
   • Used by the product application to connect to the tenant database.

Schema Layout

1. Public Schema (public)

   • Contains shared tables used by all Aventian products.
   • Holds commonly used foreign tables (via FDW from Aventian DB).
   • Includes key master tables:
     • Privilege Master Table
     • Role-Privilege Association Table
   • These are consumed as materialized views inside tenant DBs for faster access.

2. Product Schemas (cleversort, jotgo, etc.)

   • Each product has a dedicated schema named after the product.
   • Product teams manage:
     • Tables
     • Functions, Stored Procedures (SPs)
     • Views and materialized views
   • Example:
     • cleversort.tbl_tasks table
     • jotgo.tbl_projects table

3. Aventian DB Connection (FDW)

   • Each tenant DB connects to the Aventian DB in read-only mode using Postgres FDW extension.
   • Foreign tables are exposed from Aventian DB into tenant DB.
   • Master data resides in Aventian DB, and is imported into tenant product schemas for read-only access.

Master Data Handling

• Single Source of Truth:
  All master data is owned by Aventian DB.

• Tenant DB Usage:

  • Master data is imported into product schemas under each tenant DB.
  • Used in read-only mode.

• Materialized Views:

  • Foreign tables are not directly queried.
  • Instead, materialized views are created for optimized query performance.
  • Example:
    • public.mv_privilege_master
    • public.mv_role_privilege_assoc

Adding New Master Tables

• If a new master table is required for products:
  • Raise a request with the Aventian Team.
  • Table will be created in Aventian DB.
  • Exposed to tenant DBs via FDW and made available as read-only materialized views.

Mermaid Diagram

Key Points

• Admin User: Internal use only.

• Tenant User: Application-level connections.

• Public Schema: Common privilege/role data.

• Product Schema: Owned by product teams.

• FDW + Materialized Views: Ensures read-only, performant access to shared master data.