Module 3: The Data Model — Portfolio for Dummies

The Big Four

Every tenant schema revolves around four core entities. All calculations, reports, and dashboards ultimately read from these tables. Everything else in the system exists to support, describe, or connect them.

Click on any entity below to explore its fields and relationships:

Core

Investors

LPs, people and entities who commit capital

Core

Assets

Funds, deals, investment vehicles

Core

Transactions

Grouping containers for related cash flows

Core

CashFlows

The actual financial records — all calculations read here

Investors

The people and entities who commit capital to assets. Soft-deletable.

id bigint PK

client_reference string, unique

name string

email string, nullable

guid UUID, auto-generated

source string, nullable

source_id string, nullable

title string, nullable

forename string, nullable

surname string, nullable

deleted_at timestamp, soft delete

Relationships

↔ Assets — many-to-many via investor_asset pivot
→ CashFlows — one investor has many cash flows
→ AssetValuations — per-investor valuations (optional)
→ InvestorAssetUnits — unit holdings per asset over time

Assets

Investment vehicles: funds, direct investments, sub-positions. Supports parent/child hierarchy.

id bigint PK

client_reference string, unique

name string

uid DE{9}X format, auto

guid UUID, auto-generated

class AssetClass enum

structure DIRECT | PRIVATE_EQUITY_FUND

currency string (ISO 4217)

parent_asset_id FK, self-ref, nullable

Asset Class enum values:

CASH DEBT EQUITY HYBRID MULTI_ASSET REAL_ASSETS REAL_ESTATE COLLECTIBLE MEZZANINE_FINANCE CONVERTIBLE_LOAN

Relationships

↔ Investors — many-to-many via investor_asset pivot
→ CashFlows — one asset has many cash flows
→ AssetValuations — point-in-time NAV records
→ AssetData — EAV metadata (vintage, style, sector, etc.)
↔ Assets (self) — parent/child via parent_asset_id
↔ Assets (sub-pos) — many-to-many via asset_sub_positions

Transactions

A grouping container. A transaction has a type and groups its related cash flows together.

id bigint PK

guid UUID, auto-generated

type TransactionType enum

TransactionType enum (17 values):

SUBSCRIPTION CAPITAL_CALL CAPITAL_REPAYMENT DISTRIBUTION FUND_DISTRIBUTION REDEMPTION INCOME DIVIDEND OTHER DISPOSAL FUND_INVESTMENT INVESTMENT FEE FUND_CASH_CREDIT FUND_CASH_DEBIT FUND_CASH_MOVEMENT TRANSFER

Relationships

→ CashFlows — one transaction groups many cash flows

CashFlows

The most important table. Every financial record is a cash flow. All calculations read from here.

id bigint PK

type CashFlowType enum

investor_id FK → investors

asset_id FK → assets

transaction_id FK → transactions

value decimal(28,14)

units decimal(28,14)

total decimal(28,14)

currency string (ISO 4217)

invoice_date date

settlement_date date

paid boolean

cash_routing string, nullable

CashFlowType enum (23+ values) — a subset:

DRAWDOWN UNITS_ASSIGNED FULLY_PAID_UP CAPITAL_CALL CAPITAL_REPAYMENT DISTRIBUTION INVESTMENT FUND_INVESTMENT REDEMPTION DIVIDEND OTHER_INCOME FEE TRANSFER_IN TRANSFER_OUT FUND_CASH_CREDIT FUND_CASH_DEBIT FUND_CASH_MOVEMENT

Relationships

← Investor — belongs to one investor
← Asset — belongs to one asset
← Transaction — belongs to one transaction
→ Fees — one cash flow can have many fees
→ Taxes — one cash flow can have many taxes
→ DistributionComponents — breakdown of distribution amounts
→ CashFlowData — EAV metadata

The mental model

Think of it like a bookshelf. Investors are the people. Assets are the shelves. Transactions are folders on those shelves. CashFlows are the individual receipts inside those folders. Every calculation in Portfolio opens those folders and reads the receipts.

Decimal precision: decimal(28,14)

All monetary values (value, units, total) use decimal(28,14) — that's 28 total digits with 14 after the decimal point. This is deliberate: private equity calculations involve pro-rata splits, unit-price calculations, and multi-currency conversions that compound rounding errors if you don't carry enough precision. Never truncate or round intermediate values.

How They Connect: The Relationship Map

The four core entities don't exist in isolation. Here's the full relationship picture:

Investor

M:N via investor_asset

↔

Asset

↓ 1:N

1:N ↓

Transaction

1:N groups

→

CashFlow

↓ 1:N children

Fee

Tax

DistributionComponent

CashFlowData

Asset → Asset self-referencing via parent_asset_id
+ M:N via asset_sub_positions

The critical takeaway: CashFlow sits at the intersection of everything. It's the junction table between Investors, Assets, and Transactions. When the Go calc service needs to compute DPI for investor X in asset Y, it's querying cash_flows filtered by investor_id and asset_id.

Supporting Entities

Beyond the Big Four, a number of supporting entities handle specialized concerns. Expand each to learn what it does:

Records the net asset value of an asset (or a specific investor's holding) at a point in time. This is how the system knows what things are worth, as opposed to what was paid.

asset_id FK → assets

investor_id FK, nullable (per-investor)

value decimal(28,14)

date date

currency string

creation_type enum

MANUAL CALCULATED SUBSCRIPTION

When investor_id is null, the valuation is for the asset as a whole. When set, it's a per-investor holding valuation.

Tracks how many units an investor holds in an asset at a specific point in time. Used for pro-rata calculations, VPU (value per unit) calculations, and unit-based distribution splitting.

investor_id FK → investors

asset_id FK → assets

units decimal(28,14)

held_at timestamp

Fee records are linked to a CashFlow and describe charges applied to a transaction. There are 13 fee types covering management fees, performance fees, organizational expenses, and more.

cash_flow_id FK → cash_flows

type FeeType enum (13 values)

total decimal(28,14)

percentage decimal, nullable

Tax follows the same pattern — linked to a cash flow with a type and total.

When money flows back to investors (distributions), it needs to be categorized for tax and reporting purposes. Each distribution cash flow can be broken into components:

RETURNED_CAPITAL CAPITAL_GAIN CAPITAL_GAIN_TAX DIVIDENDS DIVIDEND_TAX OTHER OTHER_TAX

This matters for investor reporting — LPs need to know whether they're getting their original capital back, earning gains, or receiving dividends, as each has different tax implications.

AssetTransfer records transfers between investors — when an LP sells their position to another LP. Tracks transferor_id, transferee_id, commitment_transferred, and commitment_percentage.

AdjustmentGroup / Adjustment handles adjustments to open committed capital. Each adjustment is either an INCREASE or DECREASE and modifies the effective commitment amount.

FundCash / FundCashPot manages fund-level cash — separate from investor cash flows. A fund can have multiple cash pots (e.g., general account, reserve account), each tracking inflows, outflows, and running balance.

When an investor requests a redemption or transfer through the portal, it doesn't happen immediately. Instead, a RequestedTransaction is created and follows a state machine:

Requested

→

Pending

→

Approved

Rejected

Types: REDEMPTION, ASSET_TRANSFER, ASSET_TRANSFER_PIT (point-in-time transfer)

The EAV Pattern

Some data in Portfolio doesn't live in regular columns. Instead, it uses an Entity-Attribute-Value (EAV) pattern — key-value pairs stored in separate tables with a JSON value column.

AssetData stores flexible metadata about an asset. Each row is a key-value pair where type is the key and value is a JSON column.

Type (key)	Example Value	Used For
`VINTAGE`	`"2019"`	Allocation charts, filtering
`STYLE`	`"Buyout"`	Allocation by style
`STAGE`	`"Growth"`	Investment stage classification
`GEOGRAPHIC_ALLOCATION`	`{"North America": 60, "Europe": 40}`	Geography pie charts
`SECTOR_ALLOCATION`	`{"Tech": 45, "Healthcare": 30, "Energy": 25}`	Sector pie charts
`SUBSCRIPTION_TYPE`	`"drawdown"`, `"units_assigned"`, or `"fully_paid_up"`	Determines subscription model
`FUND_TYPE`	`"OPEN_ENDED"` or `"CLOSED_ENDED"`	Determines available operations

CashFlowData attaches metadata to individual cash flows, following the same pattern.

Type (key)	Example Value	Used For
`TYPE`	`"MANAGEMENT_FEE"`	Sub-classification of the cash flow
`INVESTMENT_TYPE`	`"CO_INVESTMENT"`	Categorizing investments
`FEE_TYPE`	`"ORGANIZATIONAL_EXPENSE"`	Fee sub-classification

Why use EAV? Flexibility. Portfolio's clients can have very different metadata requirements. One fund manager needs vintage and style; another needs custom sector breakdowns and geographic allocations that don't fit a fixed schema. EAV lets the system store arbitrary metadata without database migrations.

The tradeoff: You can't query these fields with normal SQL column filters, you can't enforce types at the database level, and the data doesn't show up in schema introspection tools. It's a pragmatic choice for metadata that varies per client, but it adds complexity.

Gotcha: Grep won't find "vintage" in a migration

If you're searching the codebase for where "vintage" or "geographic_allocation" is stored, you won't find a database column. These values live in the asset_data table as EAV rows with type = 'VINTAGE'. Look for the enum class AssetDataType and the AssetData model, not for column definitions in migrations.

Gotcha: Soft deletes affect exists validation

Investors use soft deletes (deleted_at timestamp). When Laravel's exists validation rule checks whether an investor ID is valid, it automatically scopes the query to exclude soft-deleted records. This means an investor can appear to "not exist" even though their data is still in the database — and their cash flows still reference them. Watch for this when debugging validation errors on import.

The Dependency Chain

When onboarding a new client, data must be created in a specific order. Each entity depends on entities created before it. Violate this order and you'll get foreign key errors or broken references.

Tenant

The client's PostgreSQL schema must exist before anything else. Created via the Landlord API.

No dependencies

Investors

Create all investor records. These have no foreign key dependencies — they stand alone.

Depends on: Tenant

Assets

Create asset records. Parent assets must be created before children (due to parent_asset_id FK).

Depends on: Tenant. Parents before children.

FundCash / FundCashPots

If the asset has fund-level cash management, create the fund cash record and its pots now.

Depends on: Assets

Subscriptions

Link investors to assets via subscriptions. This creates the investor_asset pivot entry and the initial cash flow (DRAWDOWN, UNITS_ASSIGNED, or FULLY_PAID_UP depending on the asset's subscription type).

Depends on: Investors + Assets

Capital Calls

Call capital from committed investors. Requires subscriptions to exist so the system knows who committed what.

Depends on: Subscriptions

Valuations

Set NAV values for assets and/or specific investor holdings. Can be asset-level or per-investor.

Depends on: Assets (and optionally Investors)

Distributions

Return capital to investors. Requires investors to hold units in the asset (from subscriptions/capital calls).

Depends on: Investors holding units in Assets

Fees, Adjustments, Transfers

All remaining transaction types. Fees need cash flows to attach to. Adjustments modify commitments. Transfers move positions between investors.

Depends on: Various above entities

Import order = dependency order

The XLSX import pipeline and the MCP import tools both follow this exact order. When bulk importing data for a new client, you upload Investors first, then Assets, then Subscriptions, and so on. The system will reject rows that reference entities that don't exist yet.

The subscription step is the bridge

Step 5 (Subscriptions) is the critical bridge. Before a subscription exists, an investor and an asset are unrelated — they're just records in separate tables. The subscription is what links them together by creating both the investor_asset pivot entry and the first cash flow record. Without it, you can't create capital calls, distributions, or any other investor-asset transaction.

Knowledge Check

Question 1

You need to find out which "vintage year" an asset belongs to. Where do you look?

The vintage column on the assets table

The asset_data table — find the row where type = 'VINTAGE' for that asset_id

The asset_valuations table, using the earliest valuation date as the vintage

Correct! Vintage is stored as an EAV row in the asset_data table with type = 'VINTAGE'. It's not a column on the assets table. This is the EAV pattern in action — flexible metadata stored as key-value pairs rather than fixed columns.

Not quite. Vintage year isn't a column on any table. It's stored in the asset_data table as an EAV (Entity-Attribute-Value) row with type = 'VINTAGE'. This is one of the gotchas of the EAV pattern — you won't find it by looking at the schema.

Question 2

You're importing data for a new client and try to create a capital call, but the system rejects it. What's the most likely reason?

The asset doesn't exist yet — assets must be created before capital calls

Subscriptions haven't been created yet — investors must be linked to assets before capital can be called

Valuations need to exist before capital calls can be issued

The tenant schema hasn't been migrated

Correct! Capital calls depend on subscriptions. A subscription is what links an investor to an asset and records their commitment. Without a commitment, the system has no basis for calculating how much capital to call from whom. This is step 5 in the dependency chain — the bridge between investors/assets and transactional activity.

Not quite. While assets (step 3) do need to exist, the most likely missing step is subscriptions (step 5). A subscription links an investor to an asset with a commitment amount. Without it, there's no commitment to call against. The dependency chain is: Investors → Assets → Subscriptions → Capital Calls.

What's Next

You now understand the data model — the Big Four entities, the supporting cast, the EAV pattern, and the dependency chain. Next, we'll go deeper on asset structures — how parent/child hierarchies work, the difference between DIRECT and PRIVATE_EQUITY_FUND structures, sub-positions, and how these structures affect every calculation and report in the system.