LCFS - Fuel code data model

**Problem Description**

Determine the impacts of implementing a relational data model for fuel codes instead of the existing flat data model.

**In order to** improve fuel code data structure, maintainability, and future workflow support, the LCFS Portal **needs to** assess whether a relational model would better support fuel code information and related application data.

**Solution Needs**

- Review the existing flat fuel code data model.
- Assess the impacts of moving to a relational model.
- Identify data migration considerations.
- Identify impacts to existing fuel code views, forms, exports, validations, calculations, and reporting.
- Identify risks, benefits, and recommended next steps.
- Do not implement model changes as part of this spike.
- Match FC companies with existing portal organizations, many will not have a match.
- Identify proposed entities, relationships, and dependencies based on the following possible structure:

**FC table**
- Status
- Prefix
- Fuel code iteration
- Carbon intensity
- Fuel
- Feedstock
- Feedstock location
- Misc
- Co-processed
- Feedstock transport mode
- Finished fuel transport mode
- Former company
- Notes
- Application date
- Approval date
- Effective date
- Expiry date

**Existing Organization table**not all company will exist
- EDRMS#
- Company
- Contact name
- Contact email

**Fuel production facility table**
- Fuel production facility city
- Fuel production facility province/state
- Fuel production facility country
- Facility nameplate capacity
- Unit

**Timebox**

- Confirm with team.

**Outcome**

- A recommended direction is documented for whether to retain the flat model or move to a relational fuel code model.
- Key impacts, risks, and migration considerations are identified.
- Follow-up development tasks or stories are created if a relational model is recommended.

**Additional Context**

- The proposed relational structure should consider fuel code lifecycle information, company/contact information, facility details, and pathway-specific attributes.
- Review how the new model would support current and future CI application workflows.
- Consider whether some fields should become shared lookup values or related tables.
- Consider how historical fuel code records would be preserved and displayed.