Data Models Reference

Overview

This page summarizes the core database tables and models used in the Portfolio Analytics System.
Fields, types, and key relationships are included for quick reference and troubleshooting.

---

1. portfolios

Field	Type	Notes
id	int	PK, autoincrement
portfolio_id	string	Unique business key
base_currency	string(3)
open_date	date
close_date	date	Nullable
risk_exposure	string
investment_time_horizon	string
portfolio_type	string
objective	string	Nullable
booking_center	string
cif_id	string	Client/customer grouping
is_leverage_allowed	bool
advisor_id	string	Nullable
status	string	e.g., ACTIVE, CLOSED
created_at	datetime
updated_at	datetime

---

2. instruments

Field	Type	Notes
id	int	PK
security_id	string	Unique, indexed
name	string
isin	string	Unique, indexed
currency	string
product_type	string	e.g., Equity, Bond
created_at	datetime
updated_at	datetime

---

3. transactions

Field	Type	Notes
id	int	PK
transaction_id	string	Unique, indexed
portfolio_id	string	FK to portfolios
instrument_id	string
security_id	string	FK to instruments
transaction_type	string	BUY, SELL, DIVIDEND, etc.
quantity	numeric
price	numeric
gross_transaction_amount	numeric
trade_currency	string
currency	string
transaction_date	datetime
settlement_date	datetime	Nullable
trade_fee	numeric	Nullable
gross_cost	numeric	Nullable (calc)
net_cost	numeric	Nullable (calc)
realized_gain_loss	numeric	Nullable (calc)
created_at	datetime
updated_at	datetime

---

4. cashflows

Field	Type	Notes
id	int	PK
transaction_id	string	FK to transactions, unique
portfolio_id	string	FK to portfolios
security_id	string	FK to instruments, nullable
cashflow_date	date
amount	numeric
currency	string(3)
classification	string	INVESTMENT_OUTFLOW/INFLOW/etc.
timing	string	BOD/EOD
level	string	POSITION/PORTFOLIO
calculation_type	string	NET/GROSS/OTHER
created_at	datetime
updated_at	datetime

---

5. position_history

Field	Type	Notes
id	int	PK
portfolio_id	string	FK to portfolios
security_id	string	FK to instruments
transaction_id	string	FK to transactions
position_date	date
quantity	numeric
cost_basis	numeric
created_at	datetime
updated_at	datetime

---

6. market_prices

Field	Type	Notes
id	int	PK
security_id	string	FK to instruments
price_date	date
price	numeric
currency	string
created_at	datetime
updated_at	datetime

---

7. portfolio_timeseries / position_timeseries

Field	Type	Notes
(portfolio_id)	string	(FK, PK part)
(security_id)	string	(FK, PK part, position only)
date	date	(PK part)
bod_market_value	numeric	Beginning of day value
bod_cashflow	numeric
eod_cashflow	numeric
eod_market_value	numeric	End of day value
fees	numeric
quantity	numeric	position_timeseries only
cost	numeric	position_timeseries only
created_at	datetime
updated_at	datetime

---

8. processed_events

Field	Type	Notes
id	int	PK
event_id	string	Unique per service
portfolio_id	string	FK to portfolios
service_name	string	"cost-calculator", etc.
correlation_id	string	For tracing
processed_at	datetime

---

9. outbox_events

Field	Type	Notes
id	int	PK
aggregate_type	string	"Transaction", etc.
aggregate_id	string
event_type	string
payload	JSON	Event data
topic	string	Kafka topic
status	string	pending/sent/failed
correlation_id	string	Traceability
retry_count	int
last_attempted_at	datetime
created_at	datetime
processed_at	datetime

---

10. fx_rates

Field	Type	Notes
id	int	PK
from_currency	string(3)
to_currency	string(3)
rate_date	date
rate	numeric
created_at	datetime
updated_at	datetime

---

11. transaction_costs

Field	Type	Notes
id	int	PK
transaction_id	string	FK to transactions
fee_type	string
amount	numeric
currency	string
created_at	datetime
updated_at	datetime

---

Notes

• All tables have created_at and updated_at fields for audit/history.
• All foreign key constraints are enforced at the DB level.
• For precise field types and constraints, see libs/portfolio-common/portfolio_common/database_models.py.

---

Related Pages

• Persistence Service
• Database Migrations
• API Service

---