# Data Layer & Database Tooling

# Data Layer & Database Tooling

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [emailsender/src/db/pool.ts](emailsender/src/db/pool.ts)
- [emailsender/src/db/schema-types.ts](emailsender/src/db/schema-types.ts)

</details>



The Primebrick v3 Data Layer is a specialized, lightweight ORM-like system designed for PostgreSQL. It avoids the overhead of heavy third-party ORMs by using a custom TypeScript decorator-based metadata registry to map code entities to database tables. The system is built around a centralized connection pool and a robust schema migration pipeline that ensures the physical database schema remains in sync with the TypeScript entity definitions.

### Database Connectivity

Connectivity is managed through a singleton PostgreSQL connection pool. The system utilizes the `pg` library and enforces a specific schema context for all operations within a microservice.

*   **Connection Management**: The `getPool()` function [emailsender/src/db/pool.ts:6-21]() initializes a `Pool` instance using the `DATABASE_URL` environment variable.
*   **Schema Scoping**: Upon connection, the pool automatically executes `SET search_path TO ...` [emailsender/src/db/pool.ts:16-18]() using the `DB_SCHEMA` environment variable (defaulting to `emailsender`). This ensures that all queries are scoped to the correct namespace without requiring explicit schema prefixes in SQL strings.

### Data Layer Architecture

The relationship between the TypeScript code and the PostgreSQL database is mediated by a shared metadata structure defined in `SchemaSnapshot`.

#### Natural Language to Code Entity Mapping

| Concept | Code Entity / Symbol | Description |
| :--- | :--- | :--- |
| **Entity Registry** | `ENTITY_REGISTRY` | A WeakMap-based store for metadata collected from decorators. |
| **Table Metadata** | `SchemaTableMeta` [emailsender/src/db/schema-types.ts:34-44]() | Represents the configuration of a table, including its audit status and columns. |
| **Column Metadata** | `SchemaColumnMeta` [emailsender/src/db/schema-types.ts:5-32]() | Defines column properties like types, primary keys, and identity status. |
| **Database Pool** | `getPool()` [emailsender/src/db/pool.ts:6-21]() | The entry point for obtaining a managed PostgreSQL client. |

#### System Interaction Flow

The following diagram illustrates how the system bridges the gap between TypeScript Entity definitions and the live PostgreSQL Schema.

```mermaid
graph TD
    subgraph "Code Entity Space"
        A["@Entity Decorators"] -- "Registers" --> B["ENTITY_REGISTRY"]
        B -- "Snapshot Generation" --> C["SchemaSnapshot (source: entities)"]
    end

    subgraph "Database Space"
        D["PostgreSQL Instance"] -- "Introspection" --> E["SchemaSnapshot (source: database)"]
        F["getPool()"] -- "Connects to" --> D
    end

    C -- "Compare" --> G["Migration Pipeline"]
    E -- "Compare" --> G
    G -- "Generates" --> H["SQL Patch Files"]
    H -- "Applies to" --> D
```
**Sources:** [emailsender/src/db/schema-types.ts:46-51](), [emailsender/src/db/pool.ts:6-21]()

### Subsystems

The Data Layer is divided into two primary functional areas: modeling and migrations.

#### Entity Model & Decorators
The system uses TypeScript decorators to define the database schema directly on class definitions. This "Code-First" approach allows developers to specify primary keys, unique constraints, and special behaviors (like auditing or soft-deletes) using metadata.

*   **Metadata Storage**: Entity definitions are stored in a central registry using `SchemaTableMeta` [emailsender/src/db/schema-types.ts:34-44]().
*   **Special Fields**: Support for identity columns (`isPostgresIdentity`) and audit trails (`isAuditable`) is baked into the metadata types [emailsender/src/db/schema-types.ts:26-40]().

For details on available decorators and how to model entities, see **[Entity Model & Decorators (#3.1)]**.

#### Schema Snapshot & Migration Pipeline
The migration pipeline is responsible for keeping the database in sync with the code. It operates by comparing two `SchemaSnapshot` objects: one generated from the code entities and one generated by introspecting the live database.

*   **Heuristics**: The system uses `SchemaColumnMeta` [emailsender/src/db/schema-types.ts:5-32]() to track data types, nullability, and default values.
*   **Patch Workflow**: When a discrepancy is found, the pipeline generates a SQL patch file to resolve the difference.

For details on the migration scripts and the patch generation process, see **[Schema Snapshot & Migration Pipeline (#3.2)]**.

---
**Sources:**
- [emailsender/src/db/pool.ts:6-21]()
- [emailsender/src/db/schema-types.ts:5-32]()
- [emailsender/src/db/schema-types.ts:34-44]()
- [emailsender/src/db/schema-types.ts:46-51]()

---