# Write Operations - Implementation Guide
## Overview
The Wealthfolio MCP Server now supports **write operations**, enabling Claude to create and modify data in your Wealthfolio database through natural language commands.
## Available Write Tools
### 1. `create_activity` - Create Transactions
Creates new portfolio activities (transactions).
**Supported Activity Types:**
- `BUY` - Purchase of securities
- `SELL` - Sale of securities
- `DIVIDEND` - Dividend payments
- `INTEREST` - Interest income
- `FEE` - Fees and charges
- `TRANSFER_IN` - Transfer into account
- `TRANSFER_OUT` - Transfer out of account
**Parameters:**
- `account_id` (required): Account ID from accounts table
- `asset_id` (required): Asset ID/symbol from assets table
- `activity_type` (required): One of the types above
- `activity_date` (required): Date in YYYY-MM-DD format
- `quantity` (required): Number of shares/units
- `unit_price` (required): Price per share/unit
- `currency` (required): Currency code (USD, EUR, etc.)
- `fee` (optional): Transaction fee (default: 0)
- `comment` (optional): Descriptive comment
**Example Usage with Claude:**
```
"Create a BUY activity for 100 shares of AAPL at $150 on 2025-01-27 in my brokerage account"
```
**Validation:**
- Verifies account exists
- Verifies asset exists (creates helpful error if not)
- Validates date format
- Calculates total amount automatically
- Generates unique activity ID
---
### 2. `create_asset` - Add Securities
Creates new assets in the database before tracking them.
**Parameters:**
- `symbol` (required): Trading symbol (e.g., AAPL, MSFT, DCAM.PA)
- `name` (required): Full name (e.g., "Apple Inc.")
- `asset_type` (required): Stock, ETF, Bond, Cryptocurrency, etc.
- `currency` (required): Currency code (USD, EUR, etc.)
- `isin` (optional): International Securities Identification Number
- `asset_class` (optional): Asset classification
**Example Usage with Claude:**
```
"Add a new asset: symbol NEWCO, name 'New Company Inc', type Stock, currency USD"
```
**Validation:**
- Checks if asset already exists
- Validates required fields
- Sets data_source to "MANUAL"
- Generates timestamps automatically
---
### 3. `execute_write_query` - Advanced SQL Operations
Executes validated INSERT, UPDATE, or DELETE queries.
**Parameters:**
- `sql` (required): SQL statement to execute
- `confirm` (required): Must be `true` to proceed
**Security Restrictions:**
- Only allows INSERT, UPDATE, DELETE
- Blocks DROP and TRUNCATE commands
- Requires explicit confirmation
- Returns count of affected rows
**Example Usage with Claude:**
```
"Execute this UPDATE query: UPDATE activities SET comment = 'Opening position' WHERE id = '123abc', confirm: true"
```
**Use Cases:**
- Bulk updates to existing data
- Complex data corrections
- Advanced scenarios not covered by specific tools
---
## Security & Safety
### Built-in Protections
1. **Validation Before Write**
- All inputs validated for correct format
- Foreign key checks (account/asset existence)
- Date format validation
- SQL injection prevention through parameterized queries
2. **Forbidden Operations**
- DROP commands blocked
- TRUNCATE commands blocked
- Schema modifications not allowed
3. **Confirmation Requirements**
- `execute_write_query` requires explicit confirm parameter
- Claude will ask for user confirmation on destructive operations
4. **Error Handling**
- Failed operations roll back automatically
- Detailed error messages
- No partial data left on failure
### User Responsibilities
1. **Backup Your Database**
```bash
# Windows PowerShell
Copy-Item "C:\Users\<username>\AppData\Roaming\com.teymz.wealthfolio\app.db" "C:\Backups\app.db.backup"
```
2. **Test Before Bulk Operations**
- Create one activity/asset first
- Verify in Wealthfolio app
- Then proceed with bulk operations
3. **Review Changes**
- Check Wealthfolio app after creating data
- Verify calculations are correct
- Ensure positions match expectations
---
## Common Use Cases
### Use Case 1: Create Opening Positions
**Scenario**: You have 50 holdings and want to start tracking without entering historical transactions.
**Steps:**
1. List your accounts: `"Show me all my accounts"`
2. For each holding, ask Claude:
```
"Create a BUY activity for my opening position:
- Account: <account_id>
- Asset: <symbol> (if doesn't exist, create it first)
- Quantity: <shares>
- Price: <average_cost>
- Date: 2025-01-01
- Comment: Opening position"
```
3. Claude will use `create_asset` if needed, then `create_activity`
**Tips:**
- Use a consistent date for all opening positions (e.g., portfolio start date)
- Add "Opening position" in comments for tracking
- Start with one position to test the process
---
### Use Case 2: Import from CSV
**Scenario**: You have a CSV with current positions from your broker.
**Steps:**
1. Prepare CSV with columns: symbol, name, quantity, avg_cost, currency, account_name
2. Share CSV content with Claude
3. Ask: `"Import these positions as opening balances dated 2025-01-01"`
Claude will:
- Create assets that don't exist
- Map account names to account IDs
- Generate BUY activities for each position
- Provide summary of what was created
---
### Use Case 3: Reconcile Positions
**Scenario**: Your broker shows different quantities than Wealthfolio.
**Steps:**
1. Export positions from broker
2. Ask Claude: `"Compare these positions with my Wealthfolio data"`
3. Claude identifies discrepancies
4. Ask: `"Create adjustment activities to match broker positions"`
Claude will:
- Calculate differences
- Create appropriate BUY/SELL activities to reconcile
- Add comments explaining the adjustment
---
### Use Case 4: Record Dividends
**Scenario**: Record dividend payments for multiple holdings.
**Steps:**
```
"Create DIVIDEND activities:
- AAPL: $1.50 per share, 100 shares, on 2025-01-15
- MSFT: $2.00 per share, 50 shares, on 2025-01-20"
```
Claude will:
- Create one activity per dividend
- Set activity_type to DIVIDEND
- Calculate total amounts
- Associate with correct accounts
---
## Testing
### Unit Tests
Run the test suite to verify write operations:
```bash
# Read-only tests (safe to run anytime)
uv run python tests/test_database.py
# Write operations tests (modifies database temporarily)
uv run python tests/test_write_operations.py
```
The write tests:
- Create temporary test data
- Verify operations succeed
- Clean up all test data automatically
- Require confirmation before running
### Manual Testing
1. **Test with a sandbox database** (recommended):
```bash
# Copy your database to a test location
Copy-Item app.db app_test.db
# Update DB_PATH in Claude config to point to test database
# Test operations there first
```
2. **Verify in Wealthfolio**:
- After creating activities, open Wealthfolio
- Check that positions appear correctly
- Verify values match expectations
- Check that holdings_snapshots are updated
---
## Troubleshooting
### Error: "Asset doesn't exist"
**Problem**: Trying to create activity for unknown asset.
**Solution**:
```
"First create the asset: symbol XYZ, name 'XYZ Corp', type Stock, currency USD"
"Then create the activity for XYZ"
```
---
### Error: "Account doesn't exist"
**Problem**: Invalid account_id.
**Solution**:
```
"Show me all my accounts"
# Use the correct account ID from the list
```
---
### Error: "NOT NULL constraint failed"
**Problem**: Missing required field in database.
**Solution**: This indicates a database schema issue. Check that your Wealthfolio database is up-to-date. Required fields:
- assets: id, symbol, currency, data_source, created_at, updated_at
- activities: id, account_id, asset_id, activity_type, activity_date, quantity, unit_price, currency, created_at, updated_at
---
### Error: "Invalid date format"
**Problem**: Date not in YYYY-MM-DD format.
**Solution**:
```
# Wrong: 01/27/2025, 27-01-2025, 2025.01.27
# Correct: 2025-01-27
```
---
## Best Practices
### 1. Start Small
- Test with 1-2 positions first
- Verify results in Wealthfolio
- Then scale up to bulk operations
### 2. Use Consistent Dates
- Use same date for all opening positions
- Makes it easier to identify bootstrap data
- Simplifies performance tracking
### 3. Add Comments
- Use comment field to document purpose
- Examples: "Opening position", "Reconciliation adjustment", "Dividend from Q4 2024"
- Helps with future auditing
### 4. Backup First
- Always backup before bulk operations
- Keep backups for at least 30 days
- Test restore procedure before you need it
### 5. Verify After Import
- Check total portfolio value matches expected
- Verify positions against broker statements
- Confirm all assets created correctly
---
## Advanced Topics
### Handling Corporate Actions
**Stock Splits:**
- Wealthfolio may handle automatically via data provider
- Or manually: create SELL at old price, BUY at new price
- Adjust quantities to reflect split ratio
**Mergers/Acquisitions:**
- Create SELL for acquired company
- Create BUY for acquiring company
- Use market prices on merger date
### Bulk Operations
For importing many positions:
1. Prepare data in structured format (CSV, JSON)
2. Share with Claude
3. Use `execute_write_query` for bulk inserts if needed
4. Or let Claude iterate using create_activity
### Database Triggers
Note: Wealthfolio may have application-level logic that recalculates:
- holdings_snapshots
- daily_account_valuation
- Performance metrics
After creating activities, Wealthfolio may need to be:
1. Restarted to pick up changes
2. Manually recalculated (if such feature exists)
3. Or changes may appear automatically on next sync
---
## Roadmap
Future enhancements planned:
- Dry-run mode for preview before execution
- Batch import from CSV files
- Automated reconciliation workflows
- Undo/rollback capabilities
- Transaction templates for common operations
See [Backlog.md](../Backlog.md) for full feature list.
---
## Support
For issues or questions:
- Check [CLAUDE.md](../CLAUDE.md) for technical details
- Review [HISTORY_RECONSTRUCTION.md](HISTORY_RECONSTRUCTION.md) for design rationale
- Test with [test_write_operations.py](../tests/test_write_operations.py)
- Create GitHub issue with reproduction steps
---
**Last Updated**: 2025-01-27
**Server Version**: 0.1.0 with write operations
