# AI Database Online Structure

## Overview
This document outlines the database structure for the online Viamed database tables. These tables contain a subset of fields compared to the local database, representing a simplified version for cross-reference operations. The online database focuses on essential cross-reference data and basic stock information.

## Comparison to Local Database
The online tables have significantly fewer fields than their local counterparts:
- **stocklist_tarriffs**: Only 5 fields vs ~40 in local
- **viamed_xref**: Only 7 fields vs ~40 in local
- Other viamed_xref_* tables: Missing many audit, timestamp, and extended fields

This suggests the online database is optimized for read-only cross-reference lookups rather than full data management.

## stocklist_tarriffs (Online - Limited Fields)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| stock_ref | varchar(64) | NO |  |  |  |
| pricedesc | longtext | NO |  |  |  |
| hostcompany_id | int(11) | NO |  |  |  |
| SUPERSEDED | varchar(24) | NO |  |  |  |
| shopifyhandle | varchar(256) | NO |  |  |  |

### Field Descriptions
- **stock_ref**: Internal stock reference number (up to 64 characters)
- **pricedesc**: Product description and pricing information
- **hostcompany_id**: Host company identifier
- **SUPERSEDED**: Superseded status (up to 24 characters)
- **shopifyhandle**: Shopify product handle (up to 256 characters)

*Note: Missing ~35 fields from local version including OperaDescription, creation dates, cost fields, etc.*

## viamed_xref (Online - Limited Fields)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| stock_ref | varchar(124) | NO |  |  |  |
| oem | varchar(64) | NO |  |  |  |
| oem_pn | varchar(128) | NO |  |  |  |
| type | varchar(64) | NO |  |  |  |
| xrefcode | varchar(128) | NO |  |  |  |
| hideweb | varchar(7) | NO |  |  |  |
| imgurl | varchar(255) | YES |  |  |  |

### Field Descriptions
- **stock_ref**: Internal stock reference (up to 124 characters)
- **oem**: Original Equipment Manufacturer (up to 64 characters)
- **oem_pn**: OEM part number (up to 128 characters)
- **type**: Product type (up to 64 characters)
- **xrefcode**: Cross-reference category code (up to 128 characters)
- **hideweb**: Web visibility flag (up to 7 characters)
- **imgurl**: Image URL (up to 255 characters, nullable)

*Note: Missing ~33 fields from local version including id (primary key), timestamps, combined search fields, specsheet_id, etc.*

## viamed_xref_answer (Online - Full Structure)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int(11) | NO | PRI |  | auto_increment |
| answer | varchar(200) | NO |  |  |  |
| xrefcode | varchar(32) | NO |  |  |  |
| fieldid | int(11) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **answer**: Answer text (up to 200 characters)
- **xrefcode**: Associated cross-reference code (up to 32 characters)
- **fieldid**: Reference to field definition

*Note: Matches local structure - no missing fields.*

## viamed_xref_cat (Online - Limited Fields)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int(10) unsigned | NO | PRI |  | auto_increment |
| xrefcode | varchar(128) | NO |  |  |  |
| description | longtext | NO |  |  |  |
| showonweb | int(11) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **xrefcode**: Cross-reference category code (up to 128 characters)
- **description**: Category description
- **showonweb**: Web visibility flag (0=hidden, 1=visible)

*Note: Missing fields from local version: updatedon, updatedby, hostcompanyid, showfeaturesonweb.*

## viamed_xref_cleanlookup (Online - Full Structure)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int(11) | NO | PRI |  | auto_increment |
| clean | varchar(128) | NO |  |  |  |
| dirty | varchar(128) | NO |  |  |  |
| xrefcode | varchar(64) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **clean**: Cleaned/standardized value (up to 128 characters)
- **dirty**: Original/raw value (up to 128 characters)
- **xrefcode**: Associated cross-reference code (up to 64 characters)

*Note: Matches local structure - no missing fields.*

## viamed_xref_fields (Online - Full Structure)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int(11) | NO | PRI |  | auto_increment |
| field_name | varchar(32) | NO |  |  |  |
| xrefcode | varchar(128) | NO |  |  |  |
| displayorder | int(11) | NO |  |  |  |
| addedon | int(11) | NO |  |  |  |
| addedby | bigint(20) | NO |  | 0 |  |
| showweb | int(11) | NO |  |  |  |
| showlocal | int(11) | NO |  |  |  |
| hiddenfield | int(11) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **field_name**: Field name (up to 32 characters)
- **xrefcode**: Associated cross-reference code (up to 128 characters)
- **displayorder**: Display order priority
- **addedon**: Unix timestamp when field was added
- **addedby**: User who added the field
- **showweb**: Web display flag (0=hidden, 1=visible)
- **showlocal**: Local display flag (0=hidden, 1=visible)
- **hiddenfield**: Hidden field flag (0=visible, 1=hidden)

*Note: Matches local structure - no missing fields.*

## viamed_xref_fields_answers (Online - Full Structure)

| Field | Type | Null | Key | Default | Extra |
|-------|------|------|-----|---------|-------|
| id | int(11) | NO | PRI |  | auto_increment |
| xrefcode | varchar(126) | NO |  |  |  |
| fieldid | int(11) | NO |  |  |  |
| answer | varchar(255) | NO |  |  |  |
| specsheet_id | int(11) | NO |  |  |  |
| setby | int(11) | NO |  |  |  |
| seton | bigint(20) | NO |  | 0 |  |
| stock_ref | varchar(32) | NO |  |  |  |
| addedby | int(11) | NO |  |  |  |
| addedon | int(11) | NO |  |  |  |
| answerid | int(11) | NO |  |  |  |

### Field Descriptions
- **id**: Primary key, auto-incrementing identifier
- **xrefcode**: Associated cross-reference code (up to 126 characters)
- **fieldid**: Reference to field definition
- **answer**: Answer text (up to 255 characters)
- **specsheet_id**: Reference to specification sheet
- **setby**: User who set the answer
- **seton**: Timestamp when answer was set
- **stock_ref**: Associated stock reference (up to 32 characters)
- **addedby**: User who added the answer
- **addedon**: Timestamp when answer was added
- **answerid**: Reference to predefined answer

*Note: Matches local structure - no missing fields.*

## Key Observations

### Missing Fields in Online Database
- **Audit Fields**: Most timestamp and user tracking fields are missing (updatedon, updatedby, addedon, addedby, etc.)
- **Extended Metadata**: Fields like combined search indexes, row colors, priority orders, etc.
- **Business Logic Fields**: Certificate compatibility, import tags, preferred selling info, soft delete fields
- **Integration Fields**: Many external reference fields (docid, specsheet_id in some tables)

### Implications for Synchronization
- Online database appears designed for read-only cross-reference operations
- Local database has full CRUD capabilities with audit trails
- Synchronization will require careful mapping of available fields
- Some local functionality may not be replicable online without schema updates

### Recommended Next Steps
1. Identify which local fields are essential for online operations
2. Plan schema updates to add missing critical fields to online database
3. Develop synchronization scripts that handle field mapping and defaults
4. Consider data validation rules for partial field sets