NovaTrade Database

A multi-currency brokerage data model for MySQL 8. Schema · deterministic demo data · trading controls · analytical views · invoice generation.

NovaTrade models customers, portfolios, assets, transactions, FX rates, market calendars, prices, and reviews. Database triggers enforce core trading rules, while views expose holdings, mark-to-market P&L, invoice data, and missing-price warnings.

Warning

The repository contains synthetic demo data. compacted.sql drops and recreates the NovaTrade schema; never run it against a production database.

Start here

Inspect the schema and example invoice first. The SQL script drops and recreates NovaTrade; use a disposable MySQL instance for exploration. Invoice generation requires that database and its Python dependencies.

Highlights

Area	Included capability
Trading	BUY/SELL transactions, fees, FX conversion, and audit logging
Controls	Insufficient-holdings checks and non-crypto market-day enforcement
Analytics	Holdings, position P&L, invoices, and data-quality warning views
Demo data	40 assets, 24 portfolios, 140 transactions, and multi-year prices
Reporting	Python utility that renders invoice views to PDF

Architecture

erDiagram
    Customer ||--o{ Portfolio : owns
    Portfolio ||--o{ Transaction : records
    Asset ||--o{ Transaction : traded
    Asset ||--o{ Asset_Price_History : priced
    Currency ||--o{ Exchange_Rate : converts
    Transaction ||--o{ Transaction_Log : audits

Loading

The complete implementation contains 12 tables, 5 analytical views, and 3 triggers. See compacted.sql for the authoritative definitions.

Project structure

Path	Purpose
compacted.sql	Full schema, seed data, views, triggers, and example queries
videotests.sql	Demonstration queries used during validation
gen_invoice.py	PDF invoice generator backed by invoice views
docs/	Example invoice output, README preview images, and logos
tests/	Artifact checks over the SQL deliverable and the Python utility

Quick start

Load the database

compacted.sql starts with DROP SCHEMA IF EXISTS NovaTrade. Importing it deletes any existing schema with that name before loading the demo.

git clone https://github.com/tiagoslantunes/novatrade-database.git
cd novatrade-database
mysql -u <user> -p < compacted.sql

Verify the imported objects:

USE NovaTrade;
SHOW TABLES;
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW TRIGGERS;

Explore the views

SELECT * FROM Portfolio_Holding ORDER BY portfolio_id, asset_id;
SELECT * FROM Portfolio_Position_PnL ORDER BY portfolio_id, symbol;
SELECT * FROM Invoice_Header_View ORDER BY invoice_date DESC;
SELECT * FROM Asset_Price_Missing_Warning ORDER BY trade_date DESC, symbol;

Business rules

• Quantities, prices, fees, and FX rates are rounded before insertion.
• A SELL is rejected when the portfolio lacks sufficient units.
• Non-crypto transactions require an open date in Trading_Day.
• Every transaction updates portfolio summary fields and creates an audit entry.
• Review update timestamps are maintained automatically.

Generate an invoice

python -m venv .venv
# macOS/Linux: source .venv/bin/activate
# PowerShell: .\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

# Omit --password to enter it at the secure prompt.
python gen_invoice.py \
  --user root \
  --database NovaTrade \
  --invoice-id INV-EXAMPLE \
  --out invoice.pdf

For automation, supply NOVATRADE_DB_PASSWORD through your environment or secret manager. For interactive use, omit it and --password to use the secure prompt.

A rendered example invoice is committed for reference:

Limitations

• The demo calendar must be extended before inserting non-crypto trades in new years.
• MySQL 8 is the tested target; MariaDB behavior can differ for checks, views, and triggers.
• FX values are stored on transactions for settlement reproducibility; the demo does not implement live FX ingestion.
• Authentication, authorization, KYC workflows, and production migrations are outside this schema demo.

Quality checks

Every push runs quality.yml on GitHub Actions: it validates Python syntax and confirms that the SQL artifact still contains the documented tables, views, and triggers. To run the same checks locally:

python -m pip install -r requirements.txt
python -m compileall -q gen_invoice.py tests
python -m unittest discover -s tests -v

Authors

• Tiago Antunes
• Alexandra Varela

License

Distributed under the MIT License.