Action Plan: Asset Identification System Refactoring
====================================================
Executive Summary
-----------------
**Objective**: Migrate from symbol-based asset identification to a UUID-based system with multi-provider symbol mapping and ISIN support.
**Key Goals**:
* Preserve existing data and UUIDs where possible
* Support multiple symbols per asset (Yahoo, EODHD, FMP, etc.)
* Enable ISIN-based deduplication and matching
* Integrate OpenFIGI API for automated symbol mapping
* Prioritize Yahoo, then free providers with rich data
* * *
Phase 1: Database Schema Evolution
----------------------------------
### 1.1 Create Asset Identifiers Table
**File**: src-core/migrations/YYYY-MM-DD-HHMMSS_create_asset_identifiers/up.sql
```sql
-- New table for multi-provider identifier mapping
CREATE TABLE asset_identifiers (
id TEXT PRIMARY KEY,
asset_id TEXT NOT NULL,
identifier_type TEXT NOT NULL, -- 'SYMBOL', 'ISIN', 'CUSIP', 'SEDOL', 'AMF', 'FIGI'
identifier_value TEXT NOT NULL,
data_source TEXT, -- 'YAHOO', 'EODHD', 'FMP', 'MANUAL', NULL for universal IDs
is_primary BOOLEAN DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
UNIQUE (identifier_type, identifier_value, data_source)
);
CREATE INDEX idx_asset_identifiers_lookup
ON asset_identifiers(identifier_type, identifier_value, data_source);
CREATE INDEX idx_asset_identifiers_asset
ON asset_identifiers(asset_id);
CREATE INDEX idx_asset_identifiers_value
ON asset_identifiers(identifier_value);
```
### 1.2 Migrate Existing Data
```sql
-- Migrate existing symbols to asset_identifiers
INSERT INTO asset_identifiers (
id,
asset_id,
identifier_type,
identifier_value,
data_source,
is_primary
)
SELECT
lower(hex(randomblob(16))) as id,
a.id as asset_id,
'SYMBOL' as identifier_type,
a.symbol as identifier_value,
a.data_source,
true as is_primary
FROM assets a;
-- Migrate existing ISINs
INSERT INTO asset_identifiers (
id,
asset_id,
identifier_type,
identifier_value,
data_source,
is_primary
)
SELECT
lower(hex(randomblob(16))) as id,
a.id as asset_id,
'ISIN' as identifier_type,
a.isin as identifier_value,
NULL as data_source, -- ISIN is universal
false as is_primary
FROM assets a
WHERE a.isin IS NOT NULL AND a.isin != '';
```
### 1.3 Update Assets Table
```sql
-- Add composite ISIN field if missing
-- Keep symbol for backwards compatibility (can deprecate later)
-- Add index on ISIN
CREATE INDEX idx_assets_isin ON assets(isin) WHERE isin IS NOT NULL;
```
### 1.4 Update Quotes Table
```sql
-- Add asset_id foreign key (keep symbol for now)
ALTER TABLE quotes ADD COLUMN asset_id TEXT;
-- Populate asset_id from existing symbol relationships
UPDATE quotes
SET asset_id = (
SELECT a.id
FROM assets a
WHERE a.symbol = quotes.symbol
LIMIT 1
)
WHERE asset_id IS NULL;
-- Add index
CREATE INDEX idx_quotes_asset_id ON quotes(asset_id);
-- Note: We keep the symbol column for backwards compatibility
-- and because market data providers return quotes by symbol
```
* * *
Phase 2: Rust Backend Implementation
------------------------------------
### 2.1 New Models
**File**: src-core/src/assets/asset_identifier_model.rs
```rust
use chrono::NaiveDateTime;
use diesel::prelude::*;
use serde::{Deserialize, Serialize};
#[derive(Debug, Clone, Serialize, Deserialize, PartialEq)]
#[serde(rename_all = "SCREAMING_SNAKE_CASE")]
pub enum IdentifierType {
Symbol,
Isin,
Cusip,
Sedol,
Amf,
Figi,
CompositeFigi,
ShareClassFigi,
}
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct AssetIdentifier {
pub id: String,
pub asset_id: String,
pub identifier_type: IdentifierType,
pub identifier_value: String,
pub data_source: Option<String>,
pub is_primary: bool,
pub created_at: NaiveDateTime,
pub updated_at: NaiveDateTime,
}
#[derive(Debug, Clone, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct NewAssetIdentifier {
pub asset_id: String,
pub identifier_type: IdentifierType,
pub identifier_value: String,
pub data_source: Option<String>,
pub is_primary: bool,
}
#[derive(Queryable, Insertable, AsChangeset, Selectable, Debug, Clone)]
#[diesel(table_name = crate::schema::asset_identifiers)]
pub struct AssetIdentifierDB {
pub id: String,
pub asset_id: String,
pub identifier_type: String,
pub identifier_value: String,
pub data_source: Option<String>,
pub is_primary: bool,
pub created_at: NaiveDateTime,
pub updated_at: NaiveDateTime,
}
```
### 2.2 Asset Identifier Repository
**File**: src-core/src/assets/asset_identifier_repository.rs
```rust
use async_trait::async_trait;
use diesel::prelude::*;
use log::debug;
pub struct AssetIdentifierRepository {
pool: Arc<Pool<ConnectionManager<SqliteConnection>>>,
writer: WriteHandle,
}
#[async_trait]
pub trait AssetIdentifierRepositoryTrait: Send + Sync {
// Find asset by identifier
fn find_asset_by_identifier(
&self,
identifier_value: &str,
identifier_type: IdentifierType,
data_source: Option<&str>,
) -> Result<Option<String>>; // Returns asset_id
// Get all identifiers for an asset
fn get_identifiers_for_asset(&self, asset_id: &str) -> Result<Vec<AssetIdentifier>>;
// Add identifier to asset
async fn add_identifier(
&self,
new_identifier: NewAssetIdentifier,
) -> Result<AssetIdentifier>;
// Update identifier
async fn update_identifier(
&self,
identifier_id: &str,
is_primary: bool,
) -> Result<AssetIdentifier>;
// Delete identifier
async fn delete_identifier(&self, identifier_id: &str) -> Result<()>;
// Find all assets with a given ISIN
fn find_assets_by_isin(&self, isin: &str) -> Result<Vec<String>>; // Returns asset_ids
// Get primary symbol for asset and data source
fn get_primary_symbol(
&self,
asset_id: &str,
data_source: &str,
) -> Result<Option<String>>;
}
```
### 2.3 Asset Resolution Service
**File**: src-core/src/assets/asset_resolution_service.rs
```rust
/// Service for resolving and managing asset identifiers across providers
pub struct AssetResolutionService {
asset_repository: Arc<dyn AssetRepositoryTrait>,
identifier_repository: Arc<dyn AssetIdentifierRepositoryTrait>,
market_data_service: Arc<dyn MarketDataServiceTrait>,
openfigi_client: Option<OpenFigiClient>,
}
impl AssetResolutionService {
/// Resolve an asset from any identifier (symbol, ISIN, CUSIP, etc.)
/// Returns existing asset or creates new one with enriched data
pub async fn resolve_asset(
&self,
identifier: &str,
identifier_type: IdentifierType,
data_source: Option<&str>,
context_currency: Option<String>,
) -> Result<Asset> {
// 1. Try to find existing asset by identifier
if let Some(asset_id) = self.identifier_repository
.find_asset_by_identifier(identifier, identifier_type, data_source)?
{
return self.asset_repository.get_by_id(&asset_id);
}
// 2. If ISIN provided, check for duplicates
if matches!(identifier_type, IdentifierType::Isin) {
let existing_assets = self.identifier_repository
.find_assets_by_isin(identifier)?;
if !existing_assets.is_empty() {
// Asset exists with this ISIN
let asset_id = &existing_assets[0];
// Add new symbol mapping if data_source provided
if let Some(ds) = data_source {
// Enrich with symbol for this provider
self.enrich_asset_identifiers(asset_id, identifier, ds).await?;
}
return self.asset_repository.get_by_id(asset_id);
}
}
// 3. Create new asset with enrichment
self.create_asset_with_identifiers(
identifier,
identifier_type,
data_source,
context_currency,
).await
}
/// Enrich asset with identifiers from all available sources
async fn enrich_asset_identifiers(
&self,
asset_id: &str,
base_identifier: &str,
data_source: &str,
) -> Result<()> {
// 1. Use OpenFIGI for mapping if available
if let Some(figi_client) = &self.openfigi_client {
if let Ok(mappings) = figi_client.map_identifiers(base_identifier).await {
for mapping in mappings {
self.add_identifier_if_not_exists(asset_id, mapping).await?;
}
}
}
// 2. Query each enabled provider for their symbol
for provider in self.get_enabled_providers_by_priority() {
if let Ok(profile) = provider.get_asset_profile(base_identifier).await {
self.add_identifier_from_profile(asset_id, &profile, &provider.id()).await?;
}
}
Ok(())
}
/// Get providers in priority order: Yahoo > Free with rich data > Others
fn get_enabled_providers_by_priority(&self) -> Vec<Arc<dyn MarketDataProvider>> {
let mut providers = self.market_data_service.get_enabled_providers();
providers.sort_by_key(|p| match p.id().as_str() {
"YAHOO" => 0,
"EODHD" => 1,
"FMP" => 2,
"MARKETDATA_APP" => 3,
_ => 99,
});
providers
}
/// Merge duplicate assets (same ISIN, different symbols)
pub async fn merge_assets(
&self,
primary_asset_id: &str,
duplicate_asset_ids: Vec<String>,
) -> Result<()> {
// 1. Move all identifiers to primary asset
for dup_id in &duplicate_asset_ids {
let identifiers = self.identifier_repository
.get_identifiers_for_asset(dup_id)?;
for identifier in identifiers {
self.identifier_repository.add_identifier(NewAssetIdentifier {
asset_id: primary_asset_id.to_string(),
identifier_type: identifier.identifier_type,
identifier_value: identifier.identifier_value,
data_source: identifier.data_source,
is_primary: false, // Don't duplicate primary flags
}).await.ok(); // Ignore duplicates
}
}
// 2. Update activities to point to primary asset
self.update_activities_asset_id(&duplicate_asset_ids, primary_asset_id).await?;
// 3. Update quotes to point to primary asset
self.update_quotes_asset_id(&duplicate_asset_ids, primary_asset_id).await?;
// 4. Delete duplicate assets
for dup_id in duplicate_asset_ids {
self.asset_repository.delete(&dup_id).await?;
}
Ok(())
}
}
```
### 2.4 OpenFIGI Integration
**File**: src-core/src/market_data/openfigi_client.rs
```rust
use reqwest::Client;
use serde::{Deserialize, Serialize};
pub struct OpenFigiClient {
client: Client,
api_key: Option<String>,
base_url: String,
}
#[derive(Serialize)]
struct MappingRequest {
#[serde(rename = "idType")]
id_type: String,
#[serde(rename = "idValue")]
id_value: String,
#[serde(rename = "exchCode", skip_serializing_if = "Option::is_none")]
exch_code: Option<String>,
}
#[derive(Deserialize)]
struct MappingResponse {
data: Vec<FigiMapping>,
}
#[derive(Deserialize, Debug)]
pub struct FigiMapping {
pub figi: String,
#[serde(rename = "compositeFIGI")]
pub composite_figi: Option<String>,
#[serde(rename = "shareClassFIGI")]
pub share_class_figi: Option<String>,
pub ticker: Option<String>,
pub name: Option<String>,
#[serde(rename = "exchCode")]
pub exch_code: Option<String>,
#[serde(rename = "marketSector")]
pub market_sector: Option<String>,
}
impl OpenFigiClient {
pub fn new(api_key: Option<String>) -> Self {
Self {
client: Client::new(),
api_key,
base_url: "https://api.openfigi.com/v3".to_string(),
}
}
/// Map an identifier (ISIN, ticker, etc.) to FIGI and other identifiers
pub async fn map_identifiers(&self, identifier: &str) -> Result<Vec<FigiMapping>> {
let request = vec![MappingRequest {
id_type: self.detect_identifier_type(identifier),
id_value: identifier.to_string(),
exch_code: None,
}];
let mut req = self.client
.post(format!("{}/mapping", self.base_url))
.json(&request);
if let Some(key) = &self.api_key {
req = req.header("X-OPENFIGI-APIKEY", key);
}
let response = req.send().await?;
let mappings: Vec<MappingResponse> = response.json().await?;
Ok(mappings.into_iter()
.flat_map(|m| m.data)
.collect())
}
fn detect_identifier_type(&self, identifier: &str) -> String {
if identifier.len() == 12 && identifier.chars().all(|c| c.is_alphanumeric()) {
"ID_ISIN".to_string()
} else if identifier.len() == 9 && identifier.chars().all(|c| c.is_alphanumeric()) {
"ID_CUSIP".to_string()
} else {
"TICKER".to_string()
}
}
}
```
* * *
Phase 3: Frontend Implementation
--------------------------------
### 3.1 New API Commands
**File**: src-tauri/src/commands/asset_commands.rs
```rust
#[tauri::command]
pub async fn get_asset_identifiers(
asset_id: String,
state: tauri::State<'_, AppState>,
) -> Result<Vec<AssetIdentifier>, String> {
// Implementation
}
#[tauri::command]
pub async fn add_asset_identifier(
payload: NewAssetIdentifier,
state: tauri::State<'_, AppState>,
) -> Result<AssetIdentifier, String> {
// Implementation
}
#[tauri::command]
pub async fn delete_asset_identifier(
identifier_id: String,
state: tauri::State<'_, AppState>,
) -> Result<(), String> {
// Implementation
}
#[tauri::command]
pub async fn enrich_asset_identifiers(
asset_id: String,
state: tauri::State<'_, AppState>,
) -> Result<Vec<AssetIdentifier>, String> {
// Uses OpenFIGI + providers to enrich
}
#[tauri::command]
pub async fn find_duplicate_assets(
state: tauri::State<'_, AppState>,
) -> Result<Vec<DuplicateGroup>, String> {
// Groups assets by ISIN
}
#[tauri::command]
pub async fn merge_duplicate_assets(
primary_asset_id: String,
duplicate_ids: Vec<String>,
state: tauri::State<'_, AppState>,
) -> Result<(), String> {
// Implementation
}
```
### 3.2 New UI Page: Asset Identifiers Management
**File**: src/pages/asset-identifiers/index.tsx
**Features**:
* List all assets with their identifiers
* View/edit identifiers per asset
* See which providers have symbols for each asset
* Enrich button to fetch missing identifiers via OpenFIGI
* Detect and merge duplicate assets (same ISIN)
* Set primary symbol per provider
* Add manual identifiers (AMF codes, local IDs)
**Layout**:
┌─────────────────────────────────────────────────┐
│ Asset Identifiers Management │
├─────────────────────────────────────────────────┤
│ │
│ [Search: ISIN, Symbol, Name] [Find Duplicates] │
│ │
│ Asset: Air Liquide SA │
│ ├─ ISIN: FR0000120073 │
│ ├─ Symbols: │
│ │ ├─ Yahoo: AI.PA ⭐ [primary] │
│ │ ├─ EODHD: AI.XPAR ⭐ │
│ │ └─ FMP: (missing) [Enrich] │
│ ├─ FIGI: BBG000BVVND0 │
│ └─ [+ Add Identifier] [Enrich All] │
│ │
│ Potential Duplicates Found: 2 groups │
│ ├─ Group 1: ISIN FR0000120073 │
│ │ ├─ Asset ID 1: AI.PA (Yahoo) │
│ │ └─ Asset ID 2: AI.XPAR (EODHD) │
│ │ └─ [Merge] Keep ID 1 as primary │
└─────────────────────────────────────────────────┘
### 3.3 Settings for OpenFIGI API Key
**File**: src/pages/settings/market-data.tsx
Add section:
```html
<div>
<Label>OpenFIGI API Key (Optional)</Label>
<Input
type="password"
value={openFigiApiKey}
onChange={(e) => setOpenFigiApiKey(e.target.value)}
placeholder="Enter your OpenFIGI API key"
/>
<p className="text-sm text-muted-foreground">
Used for automatic symbol mapping. Free tier: 25 requests/day.
Get your key at <a href="https://www.openfigi.com/api">openfigi.com/api</a>
</p>
</div>
```
Store in secrets via Tauri commands.
* * *
Phase 4: Data Migration & Enrichment
------------------------------------
### 4.1 Background Job: Enrich Existing Assets
**File**: src-core/src/assets/enrichment_job.rs
```rust
/// Background job to enrich existing assets with identifiers
pub async fn enrich_all_assets_job(
resolution_service: Arc<AssetResolutionService>,
) -> Result<EnrichmentReport> {
let assets = resolution_service.asset_repository.list()?;
let mut report = EnrichmentReport::default();
for asset in assets {
// Skip cash and manual assets
if asset.asset_type == Some("CASH".to_string())
|| asset.data_source == "MANUAL" {
continue;
}
match resolution_service
.enrich_asset_identifiers(&asset.id, &asset.symbol, &asset.data_source)
.await
{
Ok(_) => report.enriched += 1,
Err(e) => {
report.failed += 1;
report.errors.push(format!("{}: {}", asset.symbol, e));
}
}
// Rate limiting for OpenFIGI free tier
tokio::time::sleep(tokio::time::Duration::from_secs(5)).await;
}
Ok(report)
}
#[derive(Default)]
pub struct EnrichmentReport {
pub enriched: usize,
pub failed: usize,
pub errors: Vec<String>,
}
```
### 4.2 Duplicate Detection Job
```rust
/// Find all assets with the same ISIN
pub fn find_duplicate_assets(
identifier_repository: Arc<dyn AssetIdentifierRepositoryTrait>,
) -> Result<Vec<DuplicateGroup>> {
// Query all ISINs
let isins = identifier_repository.get_all_isins()?;
let mut duplicates = Vec::new();
for isin in isins {
let asset_ids = identifier_repository.find_assets_by_isin(&isin)?;
if asset_ids.len() > 1 {
duplicates.push(DuplicateGroup {
isin: isin.clone(),
asset_ids,
});
}
}
Ok(duplicates)
}
pub struct DuplicateGroup {
pub isin: String,
pub asset_ids: Vec<String>,
}
```
* * *
Phase 5: Testing Strategy
-------------------------
### 5.1 Unit Tests
**Test Coverage**:
* Asset resolution by symbol, ISIN, CUSIP
* Duplicate detection
* Merge logic preserves data integrity
* OpenFIGI client with mocked responses
* Provider priority ordering
### 5.2 Integration Tests
**Scenarios**:
1. Import activity with ISIN → resolves to existing asset
2. Import activity with new symbol → creates asset with enrichment
3. Sync quotes from multiple providers → all linked to same asset
4. Merge two duplicate assets → activities and quotes preserved
### 5.3 Manual Testing Checklist
* [ ] Create asset with ISIN via UI
* [ ] Enrich asset identifiers (OpenFIGI)
* [ ] Import activity using ISIN (matches existing)
* [ ] Import activity using different symbol (creates new identifier)
* [ ] Detect duplicates
* [ ] Merge duplicates
* [ ] Sync market data from Yahoo and EODHD (same asset, different symbols)
* [ ] View asset identifiers page
* [ ] Edit identifiers manually
* * *
Implementation Order (Tonight)
------------------------------
### Session 1: Database Foundation (30-45 min)
1. Create migration for asset_identifiers table
2. Write data migration SQL for existing symbols/ISINs
3. Test migration on copy of DB
### Session 2: Rust Models (45-60 min)
4. Create asset_identifier_model.rs
5. Update schema.rs with new table
6. Create asset_identifier_repository.rs trait + implementation
### Session 3: Core Logic (60-90 min)
7. Implement AssetResolutionService
8. Add merge logic
9. Write unit tests for resolution service
### Session 4: OpenFIGI (30-45 min)
10. Implement OpenFigiClient
11. Add API key storage in app settings/secrets
12. Write tests with mocked responses
### Session 5: Commands (30 min)
13. Add Tauri commands for identifier management
14. Expose in src-tauri/src/lib.rs
### Pause Point (Can continue another day)
### Session 6: Frontend (90-120 min)
15. Create Asset Identifiers page
16. Add duplicate detection UI
17. Add merge confirmation dialog
18. Add enrichment button with progress indicator
### Session 7: Integration (30-45 min)
19. Update activity import to use resolution service
20. Update market data sync to create identifiers
21. Run full integration test
* * *
Configuration
-------------
### Environment Variables
Add to .env:
# OpenFIGI API Key (optional, for enhanced identifier mapping)
OPENFIGI_API_KEY=your_api_key_here
### Settings Storage
// Store in app_settings table
pub const OPENFIGI_API_KEY: &str = "openfigi_api_key";
// Or in keyring for security
secrets::set("openfigi_api_key", api_key)?;
* * *
Rollback Plan
-------------
If issues arise:
1. **Database**: Keep migration down.sql that drops asset_identifiers table
2. **Code**: Feature flag for new resolution service
3. **Data**: Backup DB before running enrichment jobs
4. **Queries remain functional**: Old code still works via assets.symbol
* * *
Success Criteria
----------------
✅ All existing assets have at least one identifier (migrated symbols)
✅ Assets with ISIN are enriched with multi-provider symbols
✅ OpenFIGI integration works with API key
✅ Duplicate assets can be detected and merged
✅ UI page displays and manages identifiers
✅ Activity import resolves assets by ISIN or symbol
✅ Market data sync creates quotes with correct asset_id
✅ No data loss during migration
* * *
Future Enhancements (Post-MVP)
------------------------------
* Automatic enrichment on asset creation
* Bulk import of ISIN mappings
* Asset reconciliation reports
* Symbol history tracking (ticker changes over time)
* Integration with broker APIs using ISIN
