# Stock Cockpit — Database Schema V1

## Purpose

V1 database for the personal Stock Cockpit application.

Target production environment:

- Host: `bibit.temanmotor.com`
- Linux + LiteSpeed/cPanel
- PHP 8.4.24
- MariaDB 10.11.18
- PDO MySQL available
- Application timezone: `Asia/Jakarta`
- Development: Laragon
- Frontend: HTML/CSS/vanilla JS
- Backend: native PHP
- No framework

The same database name and table structure will be used in production and local Laragon.

## Design principles

1. **Transactions are the source of truth for ownership.**
   Current portfolio positions are calculated from transactions, not manually stored as a second source of truth.
2. **Market data is separate from portfolio data.**
   Price updates never modify average cost or transaction history.
3. **Pending orders are not holdings.**
   Only matched transactions affect the portfolio.
4. **Market data can be stale.**
   Every price snapshot records capture time, source and quality.
5. **Analysis is derived.**
   Rule-based analysis is stored as a result/history, not as a replacement for the underlying data.
6. **Sharia status is explicit.**
   Current status/source/check date are stored so the UI can flag non-sharia positions without changing transaction history.
7. **Bibit is execution-only.**
   V1 has no Bibit API, login integration, scraping, or paste importer.
8. **No automatic trading.**
   The app can flag items for review but never executes BUY/SELL.

## Tables

### `users`
Application login/account.

### `app_settings`
Small application-level settings such as timezone, market update settings, and data-provider configuration.

### `advisor_rules`
Versioned human-readable rules used by the offline analysis engine and included in Advisor Snapshot.

### `stocks`
Instrument master. Supports both individual stocks and ETF/reksa dana instruments.

Important fields:
- `ticker`
- `company_name`
- `asset_type`
- `sharia_status`
- `sharia_source`
- `sharia_checked_at`

### `transactions`
Actual executed investment transactions.

Important fields:
- BUY / SELL
- transaction date
- shares/lots
- price
- fees
- cost basis
- realized P/L
- broker/reference

For V1, realized P/L uses the cost basis recorded for the SELL transaction. The UI/calculation layer will maintain the running average-cost position.

### `pending_orders`
Orders submitted but not necessarily matched.

Statuses:
- PENDING
- PARTIAL
- MATCHED
- CANCELLED
- EXPIRED

A pending order is deliberately separate from `transactions`.

### `price_snapshots`
Periodic market observations.

Can hold:
- price
- OHLC
- previous close
- volume
- traded value
- daily change
- source
- data quality

This supports twice-daily updates and historical charts without requiring live data.

### `watchlist`
Stocks that are being monitored but are not necessarily owned.

### `stock_thesis`
The human/user thesis for a stock:
- thesis status
- thesis
- catalyst
- risk notes
- next review date

### `analysis_results`
Output of the offline rule engine.

Examples:
- PROFIT_REVIEW
- LOSS_REVIEW
- THESIS_REVIEW
- SHARIA_REVIEW
- DATA_STALE
- WATCH

The result is an observation/flag, not an automatic trade instruction.

## Intentionally NOT included in V1

- Bibit API/integration
- Bibit paste parser
- automatic BUY/SELL
- live streaming prices
- AI/LLM API integration
- broker credentials
- complex accounting/lots matching engine
- social/community features

These can be reconsidered later only if a real need appears.

## Current advisor rules to seed

The first active ruleset should reflect the agreed strategy:

- Maximum 100 individual stocks is acceptable.
- Many small positions are intentional.
- New BUY candidates are allowed if they fit the filters.
- Do not buy merely to reach a stock-count target.
- Around +10% is a TAKE PROFIT REVIEW trigger, not an automatic sell.
- 0% to -10% is a review zone; averaging down is selective and thesis-dependent.
- Below -10% requires thesis review before adding.
- No mechanical stop-loss rule; use a thesis stop.
- Avoid martingale averaging.
- Prefer active/volatile stocks with sufficient trading activity; avoid stagnant/illiquid names.
- Focus on sharia stocks.
- Practical sharia hierarchy: OJK/IDX Islamic information first, then Bibit confirmation, then secondary sources.
- If Bibit does not recognize a stock as sharia, treat it as excluded from future candidate searches unless explicitly revisited.
- Existing non-sharia positions are rotated gradually rather than force-sold solely because of classification.
- ChatGPT is advisor; user is final decision maker.
- The app is a cockpit/radar, not a trading bot.

## Deployment order

1. Create production database.
2. Import `schema.sql`.
3. Verify all 10 tables exist.
4. Create the first application user through a secure setup page/script.
5. Configure local Laragon with the same database name and schema.
6. Build application locally.
7. Test locally.
8. FTP deployment to production.
9. Run production smoke test.
10. Add market-data cron only after the market update script is implemented and tested.

## Important

Do not put production database passwords into source code committed or copied between environments. Keep credentials in a local/production configuration file outside the public web root where possible.