A complete MariaDB schema that sits between a portfolio manager and the OACT trade-execution platform. It handles order intake, execution routing, transaction settlement, daily NAV valuation, and fee management — end to end, fully auditable.
Important: portfolio_core_v6 is not intended to be a complete investment platform. It is built with AI, the sole use-case being to provide a realistic test-scenario for OACT. Apart from OACT-integration, Only ad-hoc testing has been carried out.
In any production scenario, it should be viewed as a good head-start, to be further developed and adapted it to a real-life business model, interfaces, and regulatory environment.
portfolio_core_v6 is 100% SQL (MariaDB) and can be downloaded and used freely.
portfolio_core_v6 manages the complete lifecycle of portfolio orders — from initial order-intake, through trade execution, to back-office settlement and daily valuations. Everything is tracked, auditable, and safe to re-run.
Operators/interfaces submit order requests. The system registers, validates, and loads every order line into a staging table — idempotently.
→ See flowNew securities, channels, and order types found in the order requests are automatically surfaced as candidates and promoted to live reference tables.
→ MasterdataApproved orders are batched and submitted to a platform (e.g. OACT, or directly to a trading venue), which executes the actual trades.
→ Order lifecycleEach day the system aggregates transactions, market prices, and FX rates to compute each portfolio's total value (NAV) in its base currency.
→ Snapshot detailportfolio_core_v6sp_create_orders) in portfolio_core_v6.
The order is validated (portfolio, security, channel, measurements, transaction type, and other necessary masterdata exists and is configured).
portfolio_core_v6 creates a row in container_order and records an initial lifecycle event (CREATED).
Idempotency checks ensure the same request cannot create duplicates.
sp_prepare_order_batch groups approved orders into an order_batch ready for market execution.
This stage is commonly known as "cut-off" (from the front-ends perspective).
sp_write_oact_order_file exports the batch. Where OACT is used, a CSV order file arrives to the OACT operator directory.
Orders are marked EXPORTED. Native transfers to/from portfolio_core_v6 are CSV files, but any format or protocol can be added (API, JSON, etc.).
portfolio_core_v6sp_run_oact_transaction_import loads the transactions.
Rows are matched to orders via external_id.
portfolio_core_v6 creates:
container_transactioncontainer_transaction_componentsp_run_snapshot) combines:
container_security_position_daily,
container_cash_balance_daily, and
container_valuation_daily.
From order-intake to portfolio valuation — everything happens inside the database via stored procedures.
inbound_order_request_stage.(source_system, request_id). Re-submitting the same delivery is safe — the system recognises and skips duplicates at every step.exists_in_masterdata flag.security, config_channel, security_channel_map, etc. Existing records are updated non-destructively.Each order line from staging becomes a container_order row, passing through a clear status progression before being passed to OACT or other recipient.
external_id used to match OACT's confirmation back./var/OACT/…/orders/) for execution.order_lifecycle_event. A BEFORE UPDATE trigger auto-stamps approved_utc when an order is approved./var/OACT/…/transactions/. Contains 29 columns including prices, units, fees, and taxes.oact_transaction_import_stage. No data is discarded.external_id. Broken into components: money, fees, taxes, rounding.security_price_history, FX rates from fx_rate_history.container_transaction table and update affected containers (but consider performance implications first).Three business objects sit at the heart of the system.
A container is an investment portfolio or account, denominated in a base currency. It holds securities (positions) and cash, on behalf of one or more holders. Most of the system revolves around containers — orders, transactions, snapshots, and charges are all linked to a container.
A holder is a person or legal entity. Holders are attached to containers via container_holder_role — a temporal table (with valid_from / valid_to) that tracks ownership over time. One container can have multiple holders with different roles.
A security is a tradable instrument. Each security has a unique code, a base currency, and can carry additional identifiers (ISIN, Bloomberg, etc.). The link between a security and an execution channel is stored in security_channel_map — this is how the system knows which OACT item code to use when exporting an order.
An operator (e.g. fundagg) is a registered administrator or aggregator authorised to submit files. The operator code ties inbound files, order batches, and transaction imports together and controls file-system path routing.
The system can automatically apply recurring fees, taxes, bonuses, and kickbacks to containers on a schedule.
valid_from / valid_to).container_transaction row, so fees flow through the same settlement and NAV pipeline as trades.Every automated operation is wrapped in an audit record. Nothing runs silently.
One row per orchestration call (ingest, export, snapshot). Records run_type_code, start time, finish time, status (STARTED / SUCCESS / ERROR), and a human-readable message.
Exceptions captured by EXIT HANDLER blocks inside each orchestration procedure. Linked to the integration_run that failed. Carries the entity name, key, and full error message.
Append-only log for every status change on every order. Events: CREATED, APPROVED, PREPARED_FOR_EXPORT, EXPORTED, BATCH_RESET. Indexed by (order_id, event_utc) for per-order timelines.
trg_container_order_approved_utc fires BEFORE UPDATE on container_order. When the status transitions to APPROVED and approved_utc is NULL, it auto-stamps the timestamp — no application logic required.
MariaDB 10.11 · Definer: oact_admin@localhost · All text stored as utf8mb4. This section lists every stored procedure, view, and key table grouped by purpose.
All orchestration procedures wrap their work in an integration_run / integration_error audit record and use EXIT HANDLER for error capture. All ingest procedures are idempotent.
| Procedure | What it does | Business step |
|---|---|---|
| sp_register_inbound_file | Registers an inbound file (any type) into inbound_file. Idempotent via (source_system, request_id) or (file_type_code, file_path). Returns the file ID as an OUT parameter. |
① Intake |
| sp_process_inbound_file | Reads an inbound_file record and dispatches it — for order-request files, creates the inbound_order_request_file metadata record and bulk-loads rows into staging via LOAD DATA INFILE. |
① Intake |
| sp_discover_masterdata_candidates | Scans staged rows for a given file, writes candidate records to five candidate tables, and sets exists_in_masterdata flags. |
② Masterdata |
| sp_upsert_masterdata_from_candidates | Promotes candidates to live config/security tables. Inserts new records, updates existing ones. Marks the masterdata_candidate_run as PROMOTED. |
② Masterdata |
| sp_create_orders | Creates one or more orders from a JSON array payload. Validates all reference data, resolves or creates the container_order_set, writes a CREATED lifecycle event. Idempotent on (source_system, request_id, request_item_no). |
③ Orders |
| sp_create_order | Single-order convenience wrapper around sp_create_orders. Accepts individual fields instead of a JSON array. |
③ Orders |
| sp_create_orders_from_stage | Reads all rows from inbound_order_request_stage for a given file and calls sp_create_orders for each. Resolves security via security_channel_map.oact_item. |
③ Orders |
| sp_approve_container_orders | Transitions DRAFT orders to APPROVED for one container (or all if NULL). Writes APPROVED lifecycle events. The trigger stamps approved_utc automatically. |
③ Orders |
| sp_prepare_order_batch | Creates an order_batch record for an operator, links all APPROVED orders to it, and transitions them to BATCHED. |
③ Orders |
| sp_write_oact_order_file | Writes the OACT-format CSV export for a batch using SELECT … INTO OUTFILE. Marks the batch EXPORTED and stamps exported_utc on all included orders. |
③ Orders |
| sp_reset_order_batch | Rolls back a batch: unlinks orders (sets batch_id = NULL), resets status to APPROVED, writes BATCH_RESET events. Allows re-export after an error. |
③ Orders |
| sp_register_oact_transaction_file | Registers an OACT transaction confirmation CSV into inbound_file and oact_transaction_import_file. |
④ Transactions |
| sp_load_oact_transaction_stage | Bulk-loads the raw OACT confirmation CSV into oact_transaction_import_stage (all 29 columns preserved). |
④ Transactions |
| sp_process_oact_transactions | Normalises staged rows into container_transaction + container_transaction_component. Joins via external_id to resolve container and cash account. Inserts component rows for money, fees, taxes, costs, and rounding. |
④ Transactions |
| sp_snapshot_container_day | The daily NAV engine. Upserts security positions, cash balances, and total valuation for all active containers on a given date. Safe to re-run. | ⑤ NAV |
| sp_apply_container_charges | Applies FIXED_AMOUNT charge events to every active scheme whose validity window covers the process date. Skips schemes already processed for that date. | Charges |
| Function | Returns | What it does |
|---|---|---|
| fn_get_latest_fx_rate | DECIMAL(28,12) | Most recent FX rate from fx_rate_history for a currency pair on or before a given date. Returns NULL if not found. Used by the snapshot engine. |
| fn_get_container_id | BIGINT | Resolves a container_code string to its internal container_id. Raises SQLSTATE 45000 if not found — prevents silent NULL propagation. |
| fn_get_security_id | BIGINT | Resolves a security_code string to its internal security_id. Same error-raising pattern as above. |
All views are read-only surfaces over the snapshot and transaction tables. MariaDB does not support COMMENT= on views — descriptions live here and in the SQL script header.
| View | What it shows | Business section |
|---|---|---|
| vw_container_total_value | Most recent NAV per container: securities value + cash value + total, all in base currency. | ⑤ NAV |
| vw_container_nav_timeseries | Full history of daily NAV per container. Ready for time-series charting. | ⑤ NAV |
| vw_container_security_market_value | Latest security positions per container: units, market price, and market value. | ⑤ NAV |
| vw_container_current_cash_positions | Running cash balance per container and cash account (SUM of all transaction cash amounts). | ④ Transactions |
| vw_container_current_security_positions | Running unit balance per container and security (SUM of all transaction units). | ④ Transactions |
| vw_orders | Wide order view joining container, security, channel. Adds a draft_order_count window aggregate for pending-order dashboards. |
③ Orders |
The 37 tables fall into a few clear groups. Reference/config tables are seeded once and rarely change.
container · holder · security
container_cash_account
container_holder_role
security_channel_map
security_identifier
container_order
container_order_set
order_batch
order_lifecycle_event
container_transaction
container_transaction_component
oact_transaction_import_file
oact_transaction_import_stage
container_security_position_daily
container_cash_balance_daily
container_valuation_daily
security_price_history
fx_rate_history
container_charge_scheme
container_charge_event
inbound_file
inbound_order_request_file
inbound_order_request_stage
integration_run
integration_error
config_currency · config_channel · config_measurement · config_aggregatable_type · config_order_status · config_batch_status · config_charge_type · config_charge_calc_method · config_transaction_event_type · config_txn_component_type · config_role_type · operator_ref · masterdata_candidate_run + 4 candidate detail tables