# Architecture Guide — Asset Management Database

> A pedagogical explanation of the database design. Written for someone with solid SQL skills who hasn't worked with bitemporal databases before. Explains the "why" behind the "what."

---

## 1. The Core Idea: Point-in-Time Everything

### The Problem

You manage money for clients. At any moment, someone — a client, a regulator, an auditor, yourself — might ask:

- "What was this portfolio worth on June 15th?"
- "What was the management fee rate on this account last March?"
- "What did the investment universe look like when we rebalanced in Q1?"
- "Why did we hold this security on that date?"

To answer these questions, you need to know what was true **on a specific past date** — not just what's true today.

A normal database stores the *current* state. When you update a client's fee rate from 0.8% to 0.6%, the old rate disappears. You can answer "what's the fee now?" but not "what was the fee on March 10th?"

### The Solution: SCD Type 2

**Slowly Changing Dimension Type 2** (SCD2) is a pattern that preserves history. Instead of overwriting a value, you:

1. **Close** the old record — set its end date
2. **Insert** a new record with the new value and an open-ended end date

Every piece of data that can change over time gets two date columns:

| Column | Meaning |
|---|---|
| `valid_from` | The date this value became effective (inclusive) |
| `valid_to` | The date this value stopped being effective (inclusive). `9999-12-31` means "still current" |

A row represents a **time interval**: "this value was true from `valid_from` through `valid_to`."

### The Query That Makes It All Work

Every bitemporal table in this database answers "what was the state as of date X?" with the **same predicate**:

```sql
WHERE :as_of BETWEEN valid_from AND valid_to
```

This is the architectural keystone. Whether you're asking about a security's price, a client's fee rate, or an FX rate, the query shape is identical. This is not a coincidence — it's a design choice that makes the system composable. The reconstruction engine, fee calculator, and compliance reporter all use this one pattern.

---

## 2. The Interval Model in Detail

### How Intervals Work

Consider Apple's daily price observations over a weekend:

```
Mon Jan 8:  $185.56
Tue Jan 9:  $186.19
Wed Jan 10: $187.15
Thu Jan 11: $185.59
Fri Jan 12: $185.92  ← last observation before weekend
                               Sat Jan 13: (no trading)
                               Sun Jan 14: (no trading)
Mon Jan 15: $183.63  ← first observation after weekend
```

In the database, these become **overlapping intervals** with no gaps:

```
valid_from  valid_to    last_price   Meaning
----------  ----------  -----------   ---------------------------------
2024-01-08  2024-01-08  $185.56       Active for Jan 8 only
2024-01-09  2024-01-09  $186.19       Active for Jan 9 only
2024-01-10  2024-01-10  $187.15       Active for Jan 10 only
2024-01-11  2024-01-11  $185.59       Active for Jan 11 only
2024-01-12  2024-01-14  $185.92       Active Jan 12–14 (spans weekend!)
2024-01-15  9999-12-31  $183.63       Active from Jan 15 onward (current)
```

The Friday row's `valid_to` is Sunday (Jan 14), not Friday — because Friday's price **holds through the weekend** until Monday's observation replaces it. The close-then-insert procedure handles this automatically: when Monday's observation arrives, it sets Friday's `valid_to` to `Monday - 1 day = Sunday`.

### The Close-Then-Insert Write Pattern

When a new observation arrives for date D:

```sql
-- Step 1: Close the current open interval
UPDATE security_metric_history
   SET valid_to = D - INTERVAL '1 day'
 WHERE security_id = :sid
   AND valid_to = '9999-12-31';

-- Step 2: Insert the new open interval
INSERT INTO security_metric_history (security_id, valid_from, valid_to, ...)
VALUES (:sid, D, '9999-12-31', ...);
```

This is the **only** write pattern that maintains the interval invariant. It's implemented as a stored procedure (`insert_security_metric_observation`) that handles:
- **Normal case**: close the old interval, insert the new one
- **Idempotent re-load**: if data for date D already exists, update in place (don't create a duplicate)
- **Concurrent writes**: uses `FOR UPDATE` locking to prevent race conditions
- **Partial updates**: uses `COALESCE` so a technicals-only re-load doesn't wipe the price, and vice versa

### Why Not Just Append Rows?

An alternative would be append-only fact rows with a single `as_of_date` column. Query: `WHERE as_of_date <= X ORDER BY as_of_date DESC LIMIT 1`. Simpler? Yes. But:

1. The query shape differs from every other bitemporal table (client fees, constraints, mandate type all use intervals)
2. The reconstruction engine, fee calculator, and compliance reporter would each need custom logic per table type
3. "No row before first observation" is harder to express cleanly

By using intervals everywhere, one predicate (`:as_of BETWEEN valid_from AND valid_to`) works on **every** bitemporal table. This is the unified query model that the entire system is built on.

---

## 3. Master Table vs. History Table

### The Pattern

The database separates **identity** (what something IS) from **history** (what something WAS over time):

| Master Table | History Table | What Changes |
|---|---|---|
| `security` | `security_metric_history` | Prices, market cap, volume, technical scores |
| `security` | `security_fundamental_history` | Revenue, earnings, balance sheet data |
| `security` | `security_recommendation_override` | Manual score overrides |
| `portfolio` | `portfolio_fee_history` | Management fee rate |
| `portfolio` | `portfolio_mandate_history` | Discretionary vs advisory |
| `portfolio` | `portfolio_strategy_history` | Which strategy is assigned |
| `portfolio` | `portfolio_position_history` | Holdings (quantity, value) |
| `portfolio` | `portfolio_delivery_preference_history` | What the client wants to receive |
| `strategy` | (self-bitemporal) | Strategy parameters change over time |
| `portfolio_constraint` | (self-bitemporal) | Constraints change over time |

### Why Separate?

Identity rarely changes. Apple is still Apple whether the price is $185 or $240. If we put the price on the master table and used SCD2, we'd duplicate Apple's name, ISIN, sector, and domicile on every daily row — hundreds of copies of data that never changes.

By separating:
- The master table holds stable identity (one row per security)
- The history table holds changing values (many rows per security, one per time interval)
- A JOIN brings them together when needed

### The `strategy` Table — Self-Bitemporal

The `strategy` table is both master AND history in one — it uses SCD2 directly. Each row has `valid_from`/`valid_to`. When a strategy's parameters change, the old row is closed and a new one is opened. This lets the reconstruction engine ask "which version of this strategy was active on date X?"

Similarly, `portfolio_constraint` is self-bitemporal: each constraint row has `valid_from`/`valid_to`, so you can ask "what constraints applied to this portfolio on date X?"

---

## 4. JSONB for Extensible Metrics, Typed Columns for Stable Metrics

### The Rule

| Use a typed column when... | Use JSONB when... |
|---|---|
| The field is stable and well-defined | The field is new or evolving |
| It's queried in WHERE / JOIN / indexed | It's sparsely populated |
| It's shared across most securities | It's type-specific (bonds vs equities) |

### In Practice

**Typed columns** (stable, frequently queried):
- `security_metric_history.last_price` — NUMERIC(20,6)
- `security_metric_history.market_cap` — NUMERIC(20,2)
- `security_metric_history.volume` — BIGINT
- `security.isin`, `security.ticker`, `security.reporting_currency`

**JSONB** (evolving, sparse):
- `security_metric_history.extra_metrics` — holds adjustment_factor, technical_score, technical_indicators, pe_ratio, roe, roic, etc.
- `security_fundamental_history.fundamentals` — revenue, ebitda, net_income, total_debt, etc.
- `security.attributes` — ETF expense_ratio, bond coupon, etc.
- `portfolio_constraint.value` — `{"max_pct_adv": 0.10, "days_to_liquidate": 5}`

### The Promotion Path

When a JSONB metric becomes "hot" (heavily queried, needs an index, used in WHERE filters), promote it:

```sql
-- 1. Add the typed column
ALTER TABLE security_metric_history ADD COLUMN pe_ratio NUMERIC(10,2);

-- 2. Backfill from JSONB
UPDATE security_metric_history
   SET pe_ratio = (extra_metrics->>'pe_ratio')::numeric
 WHERE extra_metrics ? 'pe_ratio';

-- 3. Add an index if needed
CREATE INDEX idx_metric_pe_ratio ON security_metric_history (pe_ratio);
```

Existing queries (including the JSONB fallback) keep working. This is non-breaking — the JSONB key can stay or be removed later.

### Querying JSONB

```sql
-- Get a value from JSONB (returns text, needs cast for numbers)
SELECT (extra_metrics->>'pe_ratio')::numeric AS pe_ratio
FROM security_metric_history
WHERE security_id = 8
  AND DATE '2025-08-26' BETWEEN valid_from AND valid_to;

-- Filter on a JSONB value
SELECT s.name
FROM security s
JOIN security_metric_history m ON m.security_id = s.security_id
  AND DATE '2025-08-26' BETWEEN m.valid_from AND m.valid_to
WHERE (m.extra_metrics->>'technical_score')::int >= 3;
```

---

## 5. The Constraint Model

### How Constraints Are Stored

The `portfolio_constraint` table stores **both** strategy-level and client-level constraints in one table, using `constraint_scope`:

| `constraint_scope` | What it means | Linked to |
|---|---|---|
| `STRATEGY` | Applies to all portfolios using this strategy | `strategy_id` |
| `PORTFOLIO` | Applies to one specific portfolio | `portfolio_id` |

Each constraint has:
- `constraint_type`: EXCLUSION, QUALITY_FILTER, ALLOCATION_BAND, POSITION_SIZE_LIMIT, EXPOSURE_LIMIT, LIQUIDITY_FILTER
- `dimension`: What the constraint acts on (ASSET_CLASS, COUNTRY, CURRENCY, MARKET_CAP, ADTV, etc.)
- `operator`: How the constraint is applied (EXCLUDE, MIN_VALUE, MAX_VALUE, TARGET_PCT, RANGE, MAX_PCT)
- `value`: JSONB with the constraint parameters (e.g., `{"max_value": 30}` for max P/E of 30)

### Example: Balanced Strategy Constraints

```sql
SELECT constraint_type, dimension, operator, value
FROM portfolio_constraint
WHERE strategy_id = 8
  AND DATE '2025-08-26' BETWEEN valid_from AND valid_to
ORDER BY constraint_type, dimension;
```

This returns all constraints active for the USD Balanced strategy as of that date — allocation bands (5 rows for asset class targets), position size limits, exclusions, exposure limits, and quality filters.

### The Two-Pass Reconstruction

The reconstruction engine processes constraints in two passes:

**Pass 1 — Strategy Fill:**
1. Fetch the strategy version + strategy constraints as-of the target date
2. Rank securities by technical score within each asset class
3. Fill the portfolio by allocation bands, highest-ranked first
4. Check strategy constraints (exclusions, quality filters, position sizes) for each candidate

**Pass 2 — Client Constraints:**
1. Fetch client constraints as-of the target date
2. Evict any portfolio positions that violate client constraints (e.g., "no US-domiciled securities")
3. Backfill from the extended candidate list (replacements must pass ALL client constraints)
4. If no valid replacement exists, the slot stays underweight (cash residual)

**Final check:** Re-evaluate ALL constraints on the finished portfolio. Classify as COMPLIANT, INFO (client-driven reallocation), or BREACH.

---

## 6. The FX Layer

### How FX Rates Are Stored

The `fx_rate_history` table uses the same SCD2 interval pattern as everything else. The rate is stored as **quote per base** — `USD/CHF = 0.88` means 1 USD = 0.88 CHF.

To convert USD to CHF: `amount_usd * rate`
To convert CHF to USD: `amount_chf / rate`

Only one physical row per pair per date. The inverse is derived arithmetically (never stored separately) to avoid inconsistency.

### Combined Metric + FX Query

This is the canonical query from the spec — it joins a security's metrics with the FX rate to produce CHF-converted values:

```sql
SELECT
    s.name,
    m.last_price        AS price_usd,
    fx.rate             AS usd_chf_rate,
    m.last_price * fx.rate  AS price_chf,
    m.market_cap * fx.rate  AS market_cap_chf
FROM security s
JOIN security_metric_history m
    ON  m.security_id = s.security_id
    AND DATE '2025-08-26' BETWEEN m.valid_from AND m.valid_to
JOIN fx_rate_history fx
    ON  fx.base_currency = 'USD'
    AND fx.quote_currency = 'CHF'
    AND DATE '2025-08-26' BETWEEN fx.valid_from AND fx.valid_to
WHERE s.isin = 'US0378331005';
```

Note: **the same predicate** (`:as_of BETWEEN valid_from AND valid_to`) is used for both the metric table and the FX table. This is the unified query model in action.

---

## 7. Client Data Architecture

### PII Isolation

The main database stores **no client-identifying information**. Clients and portfolios use obfuscated codes:
- `C-00001` — a client
- `P-00001`, `P-00002` — that client's portfolios

A separate air-gapped mapping database (not yet built) maps codes to real names. Only principals (the user and brother) can access it. Local on-prem AI performs de-anonymization as the final step before human delivery.

### Client Data Tables

| Table | What it tracks | SCD2? |
|---|---|---|
| `client` | Client identity (code, type) | No (master) |
| `portfolio` | Portfolio identity (code, mandate type) | No (master) |
| `portfolio_fee_history` | Management fee rate, hard floor | Yes |
| `portfolio_mandate_history` | Discretionary vs advisory | Yes |
| `portfolio_strategy_history` | Which strategy is assigned | Yes |
| `portfolio_position_history` | Holdings (quantity, value) | Yes |
| `portfolio_transaction` | Buy/sell/income transactions | No (append-only) |
| `portfolio_delivery_preference_history` | What the client receives | Yes |
| `portfolio_custodian` | Which custodian holds the assets | No (master) |
| `delivery_log` | When things were delivered | No (append-only) |

### Fee Pro-Rata Example

A client's fee changes mid-quarter from 0.8% to 0.6% on March 15:

```
valid_from  valid_to    management_fee_rate
2025-01-01  2025-03-14  0.0080   (0.8%)
2025-03-15  9999-12-31  0.0060   (0.6%)
```

Q1 fee calculation (Jan 1 – Mar 31):
- Jan 1 – Mar 14: 73 days at 0.8%
- Mar 15 – Mar 31: 17 days at 0.6%
- Pro-rata: `(73/90 * 0.008 + 17/90 * 0.006) * portfolio_value`

The SCD2 intervals make this exact — you query the fee rate as-of any date in the quarter and get the right rate. The Python fee engine handles the pro-rata math.

---

## 8. The Reconstruction Engine (High Level)

Model portfolios are **not stored** — they are **reconstructed on demand** at any point in time. This is a deliberate choice:

### Why Not Store Portfolios?

If you store 100 model portfolios and a strategy parameter changes, you'd need to update all 100. With reconstruction, you define the rules once and regenerate any portfolio at any date — deterministically (same inputs always produce the same output).

### How It Works

1. **Fetch inputs as-of date D** (all using the same SCD2 predicate):
   - Strategy version active on D
   - Strategy constraints active on D
   - Client constraints active on D
   - Security metrics (price, market cap) as-of D
   - Technical scores as-of D
   - FX rates as-of D

2. **Pass 1 — Strategy fill**: Rank securities by technical score, fill allocation bands, check strategy constraints

3. **Pass 2 — Client constraints**: Evict violations, backfill from extended list

4. **Final compliance check**: Re-evaluate all constraints, classify result

### Snapshots as Safety Net

Each quarterly rebalance saves a **snapshot** (in `portfolio_rebalance_snapshot` + `portfolio_rebalance_snapshot_holding`). This is concrete audit evidence — "here's what we said on June 30" — immune to data restatements or bug fixes in the engine. If reconstruction and snapshot disagree, investigate why.

---

## 9. Key Design Decisions

### 1. SCD2 as the DEFAULT for all changing data
Every table that can change over time uses `valid_from`/`valid_to`. This was the first architectural decision, made before any tables were created. The alternative (append-only fact tables with `as_of_date`) was rejected because it fragments the query model.

### 2. JSONB for evolving metrics, typed columns for stable ones
New metrics start in JSONB (zero schema change, zero downtime). When they become "hot," they get promoted to typed columns with an `ALTER TABLE` + backfill. This is non-breaking — existing queries keep working.

### 3. Master vs. history separation
Identity (one row) is separate from metrics (many rows). This avoids duplicating stable data across every time interval.

### 4. Unified point-in-time predicate
Every bitemporal table answers "as of date X" with `:as_of BETWEEN valid_from AND valid_to`. This makes the system composable — the reconstruction engine joins 6+ bitemporal tables with the same predicate shape.

### 5. Close-then-insert as the only write pattern
The interval invariant (no gaps, no overlaps, one open interval per entity) is maintained by stored procedures. No ad-hoc INSERTs or UPDATEs are allowed on SCD2 tables.

### 6. Strategy + constraints are self-bitemporal
Strategy parameters and constraints carry their own `valid_from`/`valid_to`, so "which version of the strategy was active on date X?" is a direct query — no separate version table needed.

### 7. Database stores data, not logic
Complex calculations (model portfolio construction, fee pro-rata, constraint evaluation) happen in the Python application layer. The database does basic screening/filtering (SQL views, materialized views) but not business logic.

### 8. PII isolation
The main DB stores obfuscated codes, not real names. A separate air-gapped mapping DB handles de-anonymization. This keeps the main DB on-prem in Switzerland while allowing cloud AI to assist with non-PII analysis.

---

## 10. Table Reference

### Universe (Investment) Tables

| Table | Purpose | SCD2? | Rows |
|---|---|---|---|
| `security` | Master identity for each security | No | 102 |
| `security_metric_history` | Daily price, market cap, volume, technical scores | Yes | 156 |
| `security_fundamental_history` | Weekly fundamentals (revenue, EBITDA, etc.) | Yes | 50 |
| `security_listing` | Exchange listings (multi-listed securities) | No | 1 |
| `security_recommendation_override` | Manual score overrides with rationale | Yes | 1 |
| `fx_rate_history` | Daily FX rates (USD/CHF, EUR/CHF, etc.) | Yes | 20 |

### Client Tables

| Table | Purpose | SCD2? | Rows |
|---|---|---|---|
| `client` | Client master (obfuscated code, type) | No | 1 |
| `portfolio` | Portfolio master (code, mandate type) | No | 2 |
| `portfolio_fee_history` | Management fee rate + hard floor | Yes | 2 |
| `portfolio_mandate_history` | Discretionary vs advisory | Yes | 2 |
| `portfolio_strategy_history` | Strategy assignment | Yes | 1 |
| `portfolio_position_history` | Holdings (quantity, value) | Yes | 8 |
| `portfolio_transaction` | Buy/sell/income transactions | No | 10 |
| `portfolio_custodian` | Which custodian holds the assets | No | 2 |
| `portfolio_delivery_preference_history` | Recommendation delivery prefs | Yes | 1 |
| `delivery_log` | Delivery audit trail | No | 1 |
| `recommendation_issue` | Investment recommendations issued | No | 1 |

### Strategy & Constraint Tables

| Table | Purpose | SCD2? | Rows |
|---|---|---|---|
| `strategy` | Strategy parameters (self-bitemporal) | Yes | 1 |
| `portfolio_constraint` | Strategy + client constraints (self-bitemporal) | Yes | 16 |

### Snapshot Tables

| Table | Purpose | SCD2? | Rows |
|---|---|---|---|
| `portfolio_rebalance_snapshot` | Snapshot metadata (date, strategy, mode) | No | 0 |
| `portfolio_rebalance_snapshot_holding` | Holdings in each snapshot | No | 0 |

### Infrastructure Tables

| Table | Purpose |
|---|---|
| `schema_migrations` | Tracks applied migrations |
| `_migrations` | Alternative migration tracker |

---

*This document is living documentation. As the schema evolves, update it. The ERD at `docs/erd/schema.html` (open in a browser) and `docs/erd/schema.er` (eralchemy text format) are auto-generated from the live schema.*