98 lines
3.6 KiB
Markdown
98 lines
3.6 KiB
Markdown
# Schema Upgrade: Lifecycle, Transfer Requests, and Data Weights
|
|
|
|
## Summary of Changes Applied via sync_engine.py
|
|
|
|
The following schema changes were successfully applied to the database:
|
|
|
|
### 1. Added `data_status` column to `vehicle.assets` table
|
|
- **Column**: `data_status VARCHAR(20)`
|
|
- **Nullable**: Yes (initially to handle existing rows)
|
|
- **Default**: `'draft'`
|
|
- **Purpose**: Tracks data completeness lifecycle (draft → verified → archived)
|
|
|
|
### 2. Created `vehicle.vehicle_transfer_requests` table
|
|
- **Purpose**: Tracks asset transfer requests between owners/organizations
|
|
- **Columns**:
|
|
- `id UUID PRIMARY KEY`
|
|
- `asset_id UUID REFERENCES vehicle.assets(id)`
|
|
- `requester_id INTEGER REFERENCES identity.users(id)`
|
|
- `current_owner_id INTEGER REFERENCES identity.persons(id)` (nullable)
|
|
- `status VARCHAR(20) DEFAULT 'pending'`
|
|
- `proof_document_id UUID REFERENCES system.documents(id)` (nullable)
|
|
- `requested_at TIMESTAMPTZ DEFAULT now()`
|
|
- `processed_at TIMESTAMPTZ` (nullable)
|
|
- `notes TEXT` (nullable)
|
|
|
|
### 3. Created `system.system_data_completion_weights` table
|
|
- **Purpose**: System-wide configuration for data completion weighting
|
|
- **Columns**:
|
|
- `id INTEGER PRIMARY KEY AUTOINCREMENT`
|
|
- `entity_type VARCHAR(50)` (e.g., "vehicle", "person", "organization")
|
|
- `field_name VARCHAR(100)` (e.g., "vin", "license_plate", "email")
|
|
- `weight_percent INTEGER` (0-100%)
|
|
- `is_mandatory BOOLEAN DEFAULT false`
|
|
- `is_active BOOLEAN DEFAULT true`
|
|
- `description TEXT` (nullable)
|
|
- `created_at TIMESTAMPTZ DEFAULT now()`
|
|
- `updated_at TIMESTAMPTZ DEFAULT now() ON UPDATE now()`
|
|
- **Unique Constraint**: `(entity_type, field_name)`
|
|
|
|
## SQL Equivalent
|
|
|
|
```sql
|
|
-- 1. Add data_status to assets
|
|
ALTER TABLE vehicle.assets
|
|
ADD COLUMN data_status VARCHAR(20) NULL DEFAULT 'draft';
|
|
|
|
-- 2. Create vehicle_transfer_requests table
|
|
CREATE TABLE vehicle.vehicle_transfer_requests (
|
|
id UUID PRIMARY KEY,
|
|
asset_id UUID NOT NULL REFERENCES vehicle.assets(id),
|
|
requester_id INTEGER NOT NULL REFERENCES identity.users(id),
|
|
current_owner_id INTEGER REFERENCES identity.persons(id),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending',
|
|
proof_document_id UUID REFERENCES system.documents(id),
|
|
requested_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
processed_at TIMESTAMPTZ,
|
|
notes TEXT,
|
|
INDEX (asset_id),
|
|
INDEX (requester_id),
|
|
INDEX (current_owner_id)
|
|
);
|
|
|
|
-- 3. Create system_data_completion_weights table
|
|
CREATE TABLE system.system_data_completion_weights (
|
|
id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
|
|
entity_type VARCHAR(50) NOT NULL,
|
|
field_name VARCHAR(100) NOT NULL,
|
|
weight_percent INTEGER NOT NULL,
|
|
is_mandatory BOOLEAN DEFAULT false,
|
|
is_active BOOLEAN DEFAULT true,
|
|
description TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (entity_type, field_name)
|
|
);
|
|
CREATE INDEX ON system.system_data_completion_weights(entity_type);
|
|
CREATE INDEX ON system.system_data_completion_weights(field_name);
|
|
```
|
|
|
|
## Model Updates
|
|
|
|
The following Python models were updated/created:
|
|
|
|
1. **`backend/app/models/vehicle/asset.py`**:
|
|
- Added `data_status: Mapped[Optional[str]]` field to `Asset` model
|
|
- Added `VehicleTransferRequest` model class
|
|
|
|
2. **`backend/app/models/system/system.py`**:
|
|
- Added `SystemDataCompletionWeight` model class
|
|
|
|
## Verification
|
|
|
|
The sync_engine.py script reported:
|
|
- ✅ 942 elements OK
|
|
- ✅ 3 elements fixed/created
|
|
- ⚠️ 2 extra (shadow) elements (unrelated to this migration)
|
|
|
|
All schema changes have been successfully applied to the database. |