Schema Breakdown

Primary Tables Overview

1. Authentication & Authorization

`auth_group`

Stores user groups. The system currently defines three primary groups:

ServiceProvider - refers to the Registered Employee role in the Coltivate mobile app
Operator
CoolingUser - refers to the Cooling User role in the Coldtivate mobile app

Key Columns:

id (Primary Key, auto-incremented via auth_group_id_seq)
name (varchar(150), unique)

`auth_permission`

Defines individual permissions.

Key Columns:

id (Primary Key, auto-incremented via auth_permission_id_seq)
name (varchar(255))
content_type_id (FK → django_content_type.id)
codename (varchar(100))

`auth_group_permissions`

Associates groups with permissions. Key Columns:

id (Primary Key, auto-incremented via auth_group_permissions_id_seq)
group_id (FK → auth_group.id)
permission_id (FK → auth_permission.id)

2. Metrics & Reporting

`company_metrics`

This table stores aggregated performance metrics for each company. It includes key performance indicators (KPIs) related to the company’s capacity, operations, user engagement, and financial performance, all of which are used to monitor and analyze the performance of cooling facilities and associated activities.

Key Columns:

report_date (date): The date the metrics report corresponds to.
company_id (int4, Primary Key): Unique identifier for the company.
comp_name (varchar(255)): Name of the company.
comp_country (bpchar(2)): Country code (e.g., 'NG', 'IN') indicating the company’s location.
comp_cap_tons (numeric): Facility capacity measured in metric tons.
comp_cap_num_crates (int4): The number of crates that can be handled.
cooling_unit_types (jsonb): JSON data specifying the types of cooling units supported by the company.
comp_op (int4): Total number of operators employed by the company.
comp_op_fem (int4): Number of female operators.
comp_op_ma (int4): Number of male operators.
comp_op_ot (int4): Number of operators of other genders.
currency (bpchar(3)): Currency code used for financial metrics (e.g., 'USD', 'NGN').
comp_reg_users (int4): Total count of registered users.
comp_reg_users_ma (int4): Count of male registered users.
comp_reg_users_fem (int4): Count of female registered users.
comp_reg_users_ot (int4): Count of registered users of other genders.
comp_beneficiaries (numeric(8,2)): Total number of beneficiaries served.
comp_beneficiaries_fem (numeric(8,2)): Number of female beneficiaries.
comp_beneficiaries_ma (numeric(8,2)): Number of male beneficiaries.
comp_cool_users (int4): Total number of cooling users.
comp_cool_users_fem (int4): Number of female cooling users.
comp_cool_users_ma (int4): Number of male cooling users.
comp_cool_users_ot (int4): Number of cooling users of other genders.
comp_farmers (int4): Number of farmers associated with the company.
comp_traders (int4): Number of traders associated with the company.
comp_unspec_user_type (int4): Count of users with unspecified types.
comp_crates_in (int4): Total number of crates checked in.
comp_ops_in (int4): Total number of check-in operations performed.
comp_kg_in (int4): Total weight (in kilograms) of produce checked in.
comp_crates_out (int4): Total number of crates checked out.
comp_ops_out (int4): Total number of check-out operations performed.
comp_kg_out (int4): Total weight (in kilograms) of produce checked out.
comp_average_room_occupancy (numeric(8,2)): Average occupancy rate of storage rooms.
comp_revenue (numeric(16,2)): Total revenue generated (in local currency).
comp_revenue_usd (numeric(16,2)): Revenue converted to USD.

`cooling_unit_metrics`

This table records performance metrics for each cooling unit. It aggregates various operational, financial, and environmental data—such as capacity usage, operator activity, revenue, and CO2 metrics—to provide insights into the efficiency and usage of cooling facilities.

Key Columns:

id (Primary Key, auto-incremented via cooling_unit_metrics_id_seq)
date (date, NOT NULL): The date when the metrics were recorded.
report_date (date, NOT NULL): The date associated with the report.
cooling_unit_id (int4, NOT NULL): Identifier for the cooling unit.
unit_name (text, NOT NULL): The name or label of the cooling unit.
is_unit_deleted (bool, NOT NULL): Indicates if the cooling unit has been marked as deleted.
state (text): The State where the cooling unit is located, if available (e.g., 'Lagos').
cool_unit_type (text, NOT NULL): The type/category of the cooling unit (e.g., 'MARKET_STORAGE_ROOM', 'MOVABLE_UNIT', 'FARM_GATE_STORAGE_ROOM').
cap_tons (int4, NOT NULL): Capacity of the unit in metric tons.
cap_num_crates (int4, NOT NULL): Number of crates the unit can accommodate.
company_id (int4, NOT NULL): Identifier for the company owning the unit.
comp_name (text, NOT NULL): Name of the company.
comp_pricing (text, NOT NULL): Pricing model or details related to the company.
currency (text, NOT NULL): Currency code used (e.g., 'NGN', 'EUR') for financial figures.
room_op (int4, NOT NULL): Total number of operators assigned to the unit.
room_op_fem (int4, NOT NULL): Number of female operators.
room_op_ma (int4, NOT NULL): Number of male operators.
room_op_ot (int4, NOT NULL): Number of operators of other genders.
room_beneficiaries (numeric, NOT NULL): Total beneficiaries served.
room_beneficiaries_fem (numeric, NOT NULL): Female beneficiaries count.
room_beneficiaries_ma (numeric, NOT NULL): Male beneficiaries count.
room_active_users (int4, NOT NULL): Number of currently active users.
room_active_user_ids (_int4, NOT NULL): Array of active user IDs.
room_active_fem (_int4, NOT NULL): Array of active female user IDs.
room_active_ma (_int4, NOT NULL): Array of active male user IDs.
room_active_ot (_int4, NOT NULL): Array of active user IDs for other genders.
room_crates_in (int4, NOT NULL): Count of crates checked in.
room_ops_in (int4, NOT NULL): Number of check-in operations.
room_kg_in (int4, NOT NULL): Total weight (in kg) of produce checked in.
room_crates_out (int4, NOT NULL): Count of crates checked out.
room_ops_out (int4, NOT NULL): Number of check-out operations.
room_kg_out (int4, NOT NULL): Total weight (in kg) of produce checked out.
average_room_occupancy (numeric, NOT NULL): Average occupancy rate of the unit's storage area.
room_revenue (numeric, NOT NULL): Revenue generated, in local currency.
room_revenue_usd (numeric, NOT NULL): Revenue generated, converted to USD.
check_in_crates_crop (jsonb): JSON data detailing crop types and crate counts during check-in.
check_in_kg_crop (jsonb): JSON data for crop weights during check-in.
check_out_crates_crop (jsonb): JSON data detailing crop types and crate counts during check-out.
check_out_kg_crop (jsonb): JSON data for crop weights during check-out.
tot_co2 (float8): Total CO2 emissions or related environmental metric.
co2_crops (jsonb): JSON data providing CO2 metrics broken down by crop.

`farmer_metrics`

This table tracks the storage and movement of agricultural produce for each farmer, recording check-in and check-out metrics for cooling units. It provides insights into how farmers utilize storage facilities, including the types and quantities of stored crops.

Key Columns:

id (Primary Key, auto-incremented via farmer_metrics_id_seq)
date (date, NOT NULL): The date when the metrics were recorded.
report_date (date, NOT NULL): The date associated with the report.
farmer_id (int4, NOT NULL): Identifier for the farmer.
cooling_unit_id (int4, NOT NULL): Identifier for the cooling unit used.
gender (text, NOT NULL): Gender of the farmer.
room_crates_in (int4, NOT NULL): Number of crates checked in by the farmer.
room_ops_in (int4, NOT NULL): Number of check-in operations.
room_kg_in (int4, NOT NULL): Total weight (in kg) of produce checked in.
room_crates_out (int4, NOT NULL): Number of crates checked out by the farmer.
room_ops_out (int4, NOT NULL): Number of check-out operations.
room_kg_out (int4, NOT NULL): Total weight (in kg) of produce checked out.
check_in_crates_crop (jsonb): JSON data detailing crop types and crate counts during check-in.
check_in_kg_crop (jsonb): JSON data for crop weights during check-in.
check_out_crates_crop (jsonb): JSON data detailing crop types and crate counts during check-out.
check_out_kg_crop (jsonb): JSON data for crop weights during check-out.

`impact_metrics`

This table tracks key performance indicators related to farmer storage usage, post-storage sales, and financial outcomes. It helps analyze how cold storage impacts farmer revenue, reduces food loss, and improves market prices for stored produce. This table provides insights into the economic impact of cold storage, measuring revenue changes, food loss reduction, and price evolution.

Key Columns:

report_date (date): The date associated with the impact report.
cooling_unit_id (int8): Identifier for the cooling unit used.
unit_name (text): Name of the cooling unit.
company_id (int8): Identifier for the company associated with the cooling unit.
farmer_id (int8): Identifier for the farmer using the storage.
crop_id (int8): Identifier for the stored crop.
first_name (varchar(255)): Farmer's first name.
last_name (varchar(255)): Farmer's last name.
crop_name (varchar(255)): Name of the crop stored.
currency (text): Currency used for financial metrics.
Baseline Metrics:
- baseline_quantity_total_month (float8): Total quantity of crop available per month before storage.
- baseline_kg_selling_price_month (numeric): Selling price per kg before using the storage.
- baseline_kg_loss_month (float8): Estimated kg of crop lost before using the storage.
- baseline_perc_loss_month (float8): Percentage of crop loss before storage.
- baseline_kg_sold_month (float8): Quantity sold per month before storage.
- baseline_farmer_revenue_month (float8): Revenue generated by the farmer before using the storage.
Monthly Storage Impact Metrics:
- monthly_kg_selling_price (float8): Selling price per kg after using the storage.
- monthly_kg_checkin (float8): Total quantity of produce checked into storage.
- monthly_kg_loss (int8): Total weight (kg) lost after storage.
- monthly_farmer_revenue (float8): Revenue generated by the farmer after using storage.
- monthly_kg_selling_price_evolution (float8): Change in price per kg after using the storage.
- monthly_perc_unit_selling_price_evolution (float8): Percentage increase in unit selling price after storage.
- monthly_farmer_revenue_evolution (float8): Change in revenue after using the storage.
- monthly_perc_farmer_revenue_evolution (float8): Percentage increase in farmer revenue.
- monthly_perc_loss (float8): Percentage of crop loss after storage.
- monthly_perc_foodloss_diff (float8): Difference in food loss percentage before and after storage.
- monthly_perc_foodloss_evolution (float8): Percentage change in food loss due to storage.
Survey & Data Collection:
- latest_survey_date (timestamptz): Timestamp of the latest farmer survey.
- baseline_completed_surveys_room (int8): Number of surveys completed before storage.
- possible_post_checkout_survey_room (int8): Number of surveys completed after storage.
- total_post_checkout_survey_unit (int8): Total number of post-checkout surveys collected.

3. Marketplace

`marketplace_companydeliverycontact`

Stores contact details for company delivery of produce bought in the Marketplace.

Key Columns:

id (Primary Key, auto-incremented via marketplace_companydeliverycontact_id_seq)
contact_name (varchar(255), Not Null): The name of the contact.
phone (varchar(128), Not Null): The phone number for the contact.
delivery_company_name (varchar(255), Not Null): The name of the delivery company.
is_active (bool, Not Null): Whether this contact is currently active.
created_at (timestamptz, Not Null): Timestamp when the contact was created.
company_id (FK → user_company.id, Not Null)
created_by_user_id (FK → user_user.id, Not Null)
cooling_units (M2M → storage_coolingunit): Cooling units this contact is associated with.

`marketplace_coupon`

Stores information about discount coupons that can be applied to marketplace transactions. These coupons provide percentage-based discounts and can be owned by individual users or companies.

Key Columns:

id (int8, Primary Key): Unique identifier for the coupon.
created_at (timestamptz): Timestamp indicating when the coupon was created.
revoked_at (timestamptz, Nullable): Timestamp indicating when the coupon was revoked (if applicable).
code (varchar(25), Unique when active): The unique code used to redeem the coupon.
discount_percentage (float8, Constraint: Must be between 0 and 1): The percentage discount applied when using the coupon.
created_by_user_id (FK → user_user.id)
owned_on_behalf_of_company_id (FK → user_company.id)
owned_by_user_id (FK → user_user.id)

`marketplace_marketlistedcrate`

Tracks crates that are listed for sale in the marketplace. It records key attributes such as availability, pricing, and listing status, ensuring efficient tracking of produce inventory in the system.

Key Columns:

id (int8, Primary Key): Unique identifier for the listed crate.
created_at (timestamptz, Not Null): Timestamp indicating when the crate was listed in the marketplace.
delisted_at (timestamptz, Nullable): Timestamp indicating when the crate was removed from the marketplace.
currency (varchar(3), Not Null): Currency used for transactions related to this crate.
cmp_last_updated_at (timestamptz, Nullable): Timestamp of the last update to the listed crate's data.
cmp_pending_in_cooling_fees (float8, Not Null): Cooling fees that are pending for the listed crate.
cmp_pending_in_cooling_fees_price_per_kg (float8, Not Null): The per kilogram cooling fee associated with the crate.
cmp_weight_locked_in_payment_pending_orders_in_kg (float8, Not Null): The weight of the produce in kilograms that is reserved due to pending payment.
cmp_available_weight_in_kg (float8, Not Null): The total available weight in kilograms for sale.
crate_id (FK → storage_crate.id)

`marketplace_marketlistedcrateprice`

Stores pricing information for crates listed in the marketplace. It tracks price changes over time, allowing dynamic pricing updates.

Key Columns:

id (int8, Primary Key): Unique identifier for each price entry.
created_at (timestamptz, Not Null): Timestamp when the price entry was created.
produce_price_per_kg (float8, Not Null): The price per kilogram for the produce in the listed crate.
market_listed_crate_id (FK → marketplace_marketlistedcrate.id)
created_by_user_id (FK → user_user.id)

`marketplace_order`

Stores records of orders placed within the marketplace. It tracks order details, payment status, and financial breakdowns.

Key Columns:

id (int8, Primary Key): Unique identifier for each order.
status (varchar(20), Not Null): The current status of the order (e.g., "pending", "completed").
created_at (timestamptz, Not Null): Timestamp when the order was created.
currency (varchar(3), Not Null): The currency used in the order (e.g., "USD").
paystack_split_code (varchar(16)): Paystack-specific split code for payment distribution.
paid_at (timestamptz): Timestamp of when the order was paid.
amount_paid (float8, Not Null): Total amount paid for the order.
cmp_last_updated_at (timestamptz): Timestamp of the last update to the order.
Financial Breakdown:
- cmp_total_produce_amount (float8, Not Null): Total value of produce in the order.
- cmp_total_cooling_fees_amount (float8, Not Null): Cooling storage fees applied to the order.
- cmp_total_coldtivate_amount (float8, Not Null): Fees collected by the marketplace platform.
- cmp_total_discount_amount (float8, Not Null): Discounts applied to the order.
- cmp_total_payment_fees_amount (float8, Not Null): Payment processing fees.
- cmp_total_amount (float8, Not Null): Final order total after fees and discounts.
payment_references (_varchar, Not Null): Array of payment references linked to this order.
status_changed_at (timestamptz, Not Null): Timestamp when the order status last changed.
created_by_user_id (FK → user_user.id)
owned_on_behalf_of_company_id (FK → user_company.id)

`marketplace_ordercrateitem`

Stores information about individual crate items in a marketplace order. It tracks details of the ordered produce, including pricing, discounts, and storage-related fees.

Key Columns:

id (int8, Primary Key): Unique identifier for each ordered crate item.
ordered_entire_crate (bool, Not Null): Indicates whether the user ordered the entire crate.
ordered_produce_weight (float8, Not Null): The weight of produce ordered (if not the entire crate).
frozen_crate_available_weight (float8, Not Null): The weight of produce available at the time of order placement.
frozen_produce_price_per_kg (float8, Not Null): The price per kg of produce at the time of order placement.
cmp_last_updated_at (timestamptz): Timestamp of the last update to this order item.
Financial Breakdown:
- cmp_produce_amount (float8, Not Null): The total price of the ordered produce.
- cmp_cooling_fees_amount (float8, Not Null): The cooling/storage fees for this item.
- cmp_discount_amount (float8, Not Null): Discounts applied to this specific order item.
- cmp_total_amount (float8, Not Null): The final total price for this order item, including fees and discounts.
coupon_id (FK → marketplace_coupon.id)
market_listed_crate_id (FK → marketplace_marketlistedcrate.id)
order_id (FK → marketplace_order.id)

`marketplace_ordercrateitem_resulting_crates`

Records the resulting crates generated from an order crate item.

Key Columns:

id (int8, Primary Key): Unique identifier for each resulting crate record.
ordercrateitem_id (FK → marketplace_ordercrateitem.id)
crate_id (FK → storage_crate.id)

`marketplace_orderpickupdetails`

Stores pickup details for orders placed in the marketplace.

Key Columns:

id (int8, Primary Key): Unique identifier for each order pickup detail.
pickup_method (varchar(16), Not Null): The method chosen for picking up the order (e.g., "self-pickup", "delivery").
cooling_unit_id (FK → storage_coolingunit.id)
order_id (FK → marketplace_order.id)

`marketplace_paystackaccount`

Key Columns:

id (int8, Primary Key): Unique identifier for each Paystack account.
created_at (timestamptz, Not Null): Timestamp when the account was created.
is_default_account (bool, Not Null): Indicates whether this is the default account for the user or company.
account_type (int2, Not Null): Type of account (e.g., business, personal).
bank_code (varchar(9), Not Null): The bank's unique identifier code.
country_code (varchar(2), Not Null): Country of the account (e.g., "NG" for Nigeria).
account_number (varchar(30), Not Null): The bank account number.
account_name (varchar(100), Not Null): Name associated with the bank account.
paystack_subaccount_code (varchar(20), Not Null): Unique identifier assigned by Paystack for subaccounts.
owned_on_behalf_of_company_id (FK → user_company.id)
created_by_user_id (FK → user_user.id)
owned_by_user_id (FK → user_user.id)

4. Operations

`operation_checkin`

Tracks check-in operations, recording the movement and ownership details of items entering storage.

Key Columns:

id (int8, Primary Key): Unique identifier for each check-in operation.
movement_id (FK → operation_movement.id)
owned_by_cooling_user_id (FK → user_cooling_user.id): The cooling user whose produce is being checked in.
owned_on_behalf_of_company_id (FK → user_company.id)

`operation_checkout`

Records details of check-out operations, including total amounts, payments, and discounts applied.

Key Columns:

id (int8, Primary Key): Unique identifier for each checkout operation.
cmp_total_cooling_fees_amount (float8, Not Null): Total cooling fees applied.
paid (bool, Not Null): Indicates whether the checkout has been paid.
cmp_total_amount (float8, Not Null): Total amount of the checkout.
discount_amount (float8, Not Null): Discount applied.
currency (varchar(3)): The currency used.
cmp_last_updated_at (timestamptz): Timestamp of last update.
payment_gateway, payment_method, payment_through (varchar(20)): Payment details.
movement_id (FK → operation_movement.id)

`operation_storagepayment`

Records storage-fee payments for check-ins, including upfront payments at check-in and later payments during storage or checkout. These payments are used by KF-2 pricing flows and are allocated to individual crates through operation_storagepaymentallocation.

Key Columns:

id (int8, Primary Key): Unique identifier for the payment.
amount (numeric(18,4), Not Null, CHECK amount >= 0): Total payment amount.
currency (varchar(3), Not Null): ISO currency code for the payment.
status (varchar(16), Not Null): Payment status. Valid values are PENDING, PAID, FAILED, and VOID.
timing (varchar(32), Not Null): When the payment applies. Valid values are CHECKIN, DURING_STORAGE, CHECKOUT, MARKETPLACE_SAME_DAY_CHECKOUT, and ADJUSTMENT.
payment_gateway (varchar(20)): External payment gateway, when applicable.
payment_method (varchar(20)): Payment method, when applicable.
payment_reference (varchar(255)): External reference or transaction identifier.
notes (text): Optional payment notes.
created_at (timestamptz, Not Null): Timestamp when the payment record was created.
updated_at (timestamptz, Not Null): Timestamp when the payment record was last updated.
paid_at (timestamptz): Timestamp when the payment was marked paid.
checkin_id (FK → operation_checkin.id, Not Null): Check-in this storage payment belongs to.

Constraints and Semantics:

Indexed by (checkin_id, status).
Paid storage payments are immutable and cannot be deleted.
Non-check-in payments must have allocations totaling exactly the payment amount before they are marked PAID.
A CHECKIN payment represents a shared prepaid balance. It may be marked PAID before it is fully allocated, then consumed incrementally by later checkouts.

`operation_storagepaymentallocation`

Allocates a storage payment amount to one or more crates. Allocations drive paid and due cooling-fee calculations for KF-2 priced crates.

Key Columns:

id (int8, Primary Key): Unique identifier for the allocation.
amount (numeric(18,4), Not Null, CHECK amount >= 0): Amount of the payment allocated to the crate.
created_at (timestamptz, Not Null): Timestamp when the allocation was created.
updated_at (timestamptz, Not Null): Timestamp when the allocation was last updated.
payment_id (FK → operation_storagepayment.id, Not Null): Payment being allocated.
crate_id (FK → storage_crate.id, Not Null): Crate receiving the allocated payment amount.
checkout_id (FK → operation_checkout.id): Checkout that consumed this portion of the payment. Null for allocations not associated with a checkout.

Constraints and Semantics:

Unique per (payment_id, crate_id, checkout_id).
Indexed by crate_id.
The allocated crate must belong to the same check-in as the payment.
Existing allocations linked to paid payments are immutable.
New allocations may be added to a paid CHECKIN payment as later checkouts consume its remaining balance. The total allocated amount cannot exceed the payment amount.

`operation_refund`

Records a refundable remainder from a check-in payment. The checkout flow reports the refund due so the operator can settle it manually; nothing in the current flow automatically creates one of these records.

Key Columns:

id (int8, Primary Key): Unique identifier for the refund.
amount (numeric(18,4), Not Null, CHECK amount >= 0): Amount owed back.
currency (varchar(3), Not Null): ISO currency code for the refund.
status (varchar(16), Not Null): Refund status. Valid values are PENDING, PAID, FAILED, and VOID.
payment_method (varchar(20)): Refund payment method, when known.
payment_gateway (varchar(20)): External payment gateway, when applicable.
payment_reference (varchar(255)): External reference or transaction identifier.
notes (text): Optional refund notes.
created_at (timestamptz, Not Null): Timestamp when the refund was created.
updated_at (timestamptz, Not Null): Timestamp when the refund was last updated.
paid_at (timestamptz): Timestamp when the refund was marked paid.
source_payment_id (OneToOne → operation_storagepayment.id, Not Null): Check-in payment whose unspent balance is being refunded.

Constraints and Semantics:

Only one refund can reference a source storage payment.
Paid refunds are immutable and cannot be deleted.
A refund must be created in a non-paid state before it can be marked PAID.

`operation_marketsurvey`

Tracks market survey data for agricultural products, including sales price, unit, and losses.

Key Columns:

id (int8, Primary Key): Unique identifier for the market survey.
selling_place (varchar(255), Not Null): The location where the product was sold.
price (float8, Not Null): The selling price of the product.
currency (varchar(3)): Currency used for the transaction.
selling_unit (varchar(32)): Measurement unit of the product.
loss (int4, Check loss >= 0): Amount of product lost before sale.
selling_date (timestamptz): Date of sale.
date_filled_in (timestamptz): Date the survey was recorded.
kg_in_unit (int4, Check kg_in_unit >= 0): Kilograms per unit.
reason_for_loss (varchar(255)): Reason for any recorded loss.
checkout_id (FK → operation_checkout.id)
crop_id (FK → storage_crop.id)
market_id (FK → prediction_market.id)

`operation_marketsurvey_checkout`

Links market surveys to checkout operations.

Key Columns:

id (int8, Primary Key): Unique identifier for the record.
checkout_id (FK → operation_checkout.id)
marketsurvey_id (FK → operation_marketsurvey.id)

`operation_marketsurveypreprocessing`

Tracks pre-processing activities for market survey data before final checkout.

Key Columns:

id (int8, Primary Key): Unique identifier for each preprocessing entry.
checkout_at (timestamptz, Not Null): Timestamp when the checkout occurred.
modified_at (timestamptz, Not Null): Timestamp of last modification.
is_active (bool, Not Null): Indicates whether the entry is currently active.
checkout_id (FK → operation_checkout.id)
crop_id (FK → storage_crop.id)
cooling_user_id (FK → user_cooling_user.id)
operator_id (FK → user_operator.id)

`operation_movement`

Records the movement of agricultural products between different locations, including check-ins and checkouts.

Key Columns:

id (int8, Primary Key): Unique identifier for each movement.
date (timestamptz, Not Null): Timestamp when the movement occurred.
code (varchar(255), Not Null): Unique movement code.
used_for_checkin (bool, Not Null): Indicates if the movement was for a check-in operation.
initiated_for (varchar(2), Not Null): Entity for which the movement was initiated.
order_id (FK → marketplace_order.id)
operator_id (FK → user_operator.id)

5. Prediction & Analytics

`prediction_market`

Stores market locations used for price prediction analysis.

Key Columns:

id (int8, Primary Key): Unique identifier for each market.
name (varchar(255), Not Null): Name of the market.
district (varchar(255), Not Null): District where the market is located.
used_for_predictions (bool, Not Null): Whether the market is used for predictions.
added_by_user (bool, Not Null): Indicates if the market was manually added.
state_id (FK → prediction_state.id)

`prediction_mlmarketdataindia`

Contains market data from India used for machine learning-based price predictions.

Key Columns:

id (int8, Primary Key): Unique identifier for the market data entry.
date (date, Not Null): Date of the recorded market data.
state_label, district_label, market_label, commodity_label (int4, Not Null): Labels identifying market parameters.
arrivals_metric_tons (float8, Not Null): Quantity of the commodity arriving at the market.
modal_price_rs_per_quintal (float8, Not Null): Price of the commodity per quintal.
last_price_1d to last_price_7d (float8, Not Null): Historical price data.
usd_to_inr (float8, Not Null): USD to INR exchange rate.
brent_oil_price (float8, Not Null): Price of Brent crude oil.
crop_id (FK → storage_crop.id)
market_id (FK → prediction_market.id)

`prediction_mlmarketdatanigeria`

Contains market data from Nigeria used for price predictions.

Key Columns:

id (int8, Primary Key): Unique identifier for the market data entry.
date (date, Not Null): Date of the recorded market data.
state_label (int4, Not Null): Label identifying the state.
commodity_label (int4, Not Null): Label identifying the commodity.
price (float8, Not Null): Current price of the commodity.
last_price_1m to last_price_5m (float8, Not Null): Historical price data for past months.
usd_to_ngn (float8, Not Null): USD to Nigerian Naira exchange rate.
cpi (float8, Not Null): Consumer Price Index.
crop_id (FK → storage_crop.id)
state_id (FK → prediction_stateng.id)

`prediction_mlpredictiondata`

Stores machine learning-based price predictions.

Key Columns:

id (int8, Primary Key): Unique identifier for the prediction entry.
fetched_at (timestamptz, Not Null): Timestamp when the prediction was fetched.
reference_date (date, Not Null) – The reference date for the prediction.
price_forecast_1 to price_forecast_14 (float8): Forecasted prices for the next 14 days.
only_interpolated_data (varchar(255), Not Null): Data type indication.
crop_id (FK → storage_crop.id)
market_id (FK → prediction_market.id)

`prediction_mlpredictiondatang`

Stores price forecasts for Nigerian markets.

Key Columns:

id (int8, Primary Key): Unique identifier for the prediction entry.
fetched_at (timestamptz, Not Null): Timestamp when the prediction was fetched.
reference_date (date, Not Null): The reference date for the prediction.
price_forecast_1 to price_forecast_8 (float8): Forecasted prices for the next 8 days.
only_interpolated_data (varchar(255), Not Null): Data type indication.
crop_id (FK → storage_crop.id)
state_id (FK → prediction_stateng.id)

`prediction_state`

Stores state-level data for price predictions.

Key Columns:

id (int8, Primary Key): Unique identifier for each state.
name (varchar(255), Not Null): Name of the state.
added_by_user (bool, Not Null): Indicates if the state was manually added.
country_id (FK → user_country.id)

`prediction_stateng`

Stores Nigerian state-level data for price predictions.

Key Columns:

id (int8, Primary Key): Unique identifier for each state.
name (varchar(255), Not Null): Name of the state.
added_by_user (bool, Not Null): Indicates if the state was manually added.
country_id (FK → user_country.id)

6. Storage & Infrastructure

`storage_coolingunit`

Stores details about cold storage units.

Key Columns:

id (int8, Primary Key): Unique identifier for each cooling unit.
name (varchar(255), Not Null): Name of the cooling unit.
capacity_in_metric_tons (float8): Storage capacity in metric tons.
capacity_in_number_crates (int4): Storage capacity in number of crates.
metric (varchar(32), Not Null): Storage metric type.
sensor (bool, Not Null): Indicates whether a sensor is installed.
cooling_unit_type (varchar(32)): Type of cooling unit.
location_id (FK → storage_location.id)

`storage_coolingunit_date_operator_assigned`

Tracks operators assigned to a cooling unit on specific dates.

Key Columns:

id (int8, Primary Key): Unique identifier.
coolingunit_id (FK → storage_coolingunit.id)
operatorassignedcoolingunit_id (FK → storage_operatorassignedcoolingunit.id)

`storage_coolingunit_operators`

Links users (operators) to cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier.
coolingunit_id (FK → storage_coolingunit.id)
user_id (FK → user_user.id)

`storage_coolingunitcrop`

Tracks which crops are available in each cooling unit. This table still carries the legacy pricing_id relationship, while KF-2 pricing-plan availability is represented by storage_coolingunitpricingplan.

Key Columns:

id (int8, Primary Key): Unique identifier.
active (bool, Not Null): Indicates if the crop is actively stored.
cooling_unit_id (FK → storage_coolingunit.id)
crop_id (FK → storage_crop.id)
pricing_id (FK → storage_pricing.id): Legacy pricing configuration for this cooling-unit/crop pair.

Constraints and Semantics:

Unique per (cooling_unit_id, crop_id).

`storage_coolingunitpricingplan`

Controls which KF-2 pricing plans are available for a cooling unit.

Key Columns:

id (int8, Primary Key): Unique identifier.
is_active (bool, Not Null): Whether this pricing-plan assignment is currently available.
created_at (timestamptz, Not Null): Timestamp when the assignment was created.
updated_at (timestamptz, Not Null): Timestamp when the assignment was last updated.
cooling_unit_id (FK → storage_coolingunit.id, Not Null): Cooling unit that can use the pricing plan.
pricing_plan_id (FK → storage_pricingplan.id, Not Null): Pricing plan available to the cooling unit.

Constraints and Semantics:

Unique per (cooling_unit_id, pricing_plan_id).
Indexed by (cooling_unit_id, is_active).
The cooling unit and pricing plan must belong to the same company.

`storage_coolingunitpower`

Tracks power consumption and energy sources of cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier.
power_consumption_in_mt (float8): Power consumption in metric tons.
power_source_diesel_percent (float8): Percentage of power from diesel.
power_source_grid_percent (float8): Percentage of power from the grid.
power_source_pv_percent (float8): Percentage of power from solar PV.
cooling_unit_id (FK → storage_coolingunit.id)

`storage_coolingunitspecifications`

Stores specifications and sensor readings for cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier.
value (varchar(32), Not Null): Specification value.
specification_type (varchar(32), Not Null): Type of specification.
datetime_stamp (timestamptz, Not Null): Timestamp of the reading.
cooling_unit_id (FK → storage_coolingunit.id)

`storage_crate`

Stores details of crates used for storing produce in cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier.
checkedin_initial_weight (float8): Weight captured at check-in and used as a locked KF-2 pricing input.
weight (float8): Current remaining crate weight.
remaining_shelf_life (int4): Remaining shelf-life estimate.
tag (varchar(255)): Operator-facing crate tag. Blank tags are normalized to null.
run_dt (bool): Whether the crate participates in digital-twin processing.
quality_dt (float8): Digital-twin quality value.
temperature_dt (float8): Digital-twin temperature value.
modified_dt (timestamptz): Timestamp for digital-twin modification.
checkedin_planned_days (int4): Optional planned storage duration captured at check-in. When supplied through the API it must be at least one day. Actual duration is resolved at checkout when it is absent.
currency (varchar(3)): Currency used for crate pricing.
price_per_crate_per_pricing_type (float8, Not Null): Legacy compatibility value. Pricing-plan fees are resolved from the immutable pricing snapshot and rules instead.
cmp_last_updated_at (timestamptz): Timestamp when computed crate fields were last recalculated.
cmp_fully_checked_out (bool, Not Null): Whether the crate has been fully checked out.
cmp_total_in_cooling_fees (float8, Not Null): Total cooling fees incurred by the crate.
cmp_total_paid_in_cooling_fees (float8, Not Null): Total paid cooling fees allocated to the crate.
cmp_total_due_in_cooling_fees (float8, Not Null): Outstanding cooling fees for the crate.
cooling_unit_id (FK → storage_coolingunit.id)
produce_id (FK → storage_produce.id)
pricing_plan_version_id (FK → storage_pricingplanversion.id): KF-2 pricing version snapshotted at check-in.
pricing_crop_id (FK → storage_crop.id): Crop snapshotted for KF-2 pricing.
pricing_crop_type_id (FK → storage_croptype.id): Crop type snapshotted for KF-2 pricing and taxonomy-rule resolution.
pricing_weight_bracket_id (FK → storage_pricingbracket.id): Weight bracket resolved for KF-2 pricing.

Constraints and Semantics:

Indexed by (cooling_unit_id, produce_id).
Non-null tag values are unique per cooling_unit_id.
Once a checked-in crate references a KF-2 pricing version, its pricing snapshot fields are immutable.

`storage_cratepartialcheckout`

Tracks partial checkouts of crates from storage units.

Key Columns:

id (int8, Primary Key): Unique identifier.
percentage (float8, Not Null): The percentage of the crate that was checked out.
weight_in_kg (int4, Not Null): The weight (in kg) of the produce checked out.
cooling_fees (float8, Not Null): Cooling fees associated with the checkout.
checkout_id (FK → operation_checkout.id)
crate_id (FK → storage_crate.id)

`storage_crop`

Contains information about different crops that can be stored in cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier.
is_active (bool, Not Null): Whether the crop is available for current operational selection. Inactive crops remain available to historical records.
name (varchar(255), Not Null): Name of the crop.
image (varchar(100)): Image URL for the crop.
optimal_storage_temperature (varchar(255)): Recommended storage temperature.
approximate_shelf_life (varchar(255)): Expected shelf life duration.
harvested_today (int2): Quantity harvested today.
harvested_yesterday (int2): Quantity harvested yesterday.
harvested_day_before_yesterday (int2): Quantity harvested two days ago.
harvested_before (int2): Quantity harvested before that period.
size_selection_1, size_selection_2, size_selection_3 (varchar(255)): Different size classifications for the crop.
digital_twin_identifier (varchar(255)): Identifier for digital tracking of the crop.
activation_energy_constant (float8): Constant used in predictive models for crop quality.
dependent_constant (float8): Another model-related constant.
crop_type_id (FK → storage_croptype.id)

`storage_croptype`

Categorizes different types of crops.

Key Columns:

id (int8, Primary Key): Unique identifier.
name (varchar(255), Not Null): Name of the crop type.

`storage_location`

Stores details about different storage locations where cooling units or other assets are situated.

Key Columns:

id (int8, Primary Key): Unique identifier.
name (varchar(255)): Name of the location.
state (varchar(255), Not Null): State where the location is situated.
city (varchar(255), Not Null): City where the location is situated.
street (varchar(255), Not Null): Street name.
street_number (int4): Street number of the location.
zip_code (varchar(255), Not Null): Zip code.
point (geometry/geography, Not Null): Geographical coordinates as a PostGIS point.
deleted (bool, Not Null): Indicates if the location is deleted.
date_creation (timestamptz): Date the location was created.
date_last_modified (timestamptz): Date of the last modification.
company_id (FK → user_company.id)

`storage_operatorassignedcoolingunit`

Keeps track of operators assigned to specific cooling units on a given date.

Key Columns:

id (int8, Primary Key): Unique identifier.
date (timestamptz, Not Null): Date of assignment.
operator_id (FK → user_user.id)

`storage_pricing`

Legacy pricing table for pre-KF-2 storage pricing. New pricing configuration is modeled by storage_pricingplan, storage_pricingplanversion, crop groups, brackets, and rules.

Key Columns:

id (int8, Primary Key): Unique identifier.
pricing_type (varchar(32), Not Null): Legacy pricing type. Valid values are FIXED and PERIODICITY.
fixed_rate (float8, Not Null): Fixed storage rate.
daily_rate (float8, Not Null): Daily storage rate.

Constraints and Semantics:

Still referenced by storage_coolingunitcrop.pricing_id for legacy pricing.
Legacy-to-KF-2 backfill provenance is tracked in storage_legacypricingplanmapping.

`storage_pricingplan`

Defines a reusable KF-2 pricing plan owned by a company. A plan groups one or more versioned pricing configurations.

Key Columns:

id (int8, Primary Key): Unique identifier.
name (varchar(255), Not Null): Pricing plan name.
description (text): Optional description.
is_active (bool, Not Null): Whether the pricing plan is available for use.
deleted_at (timestamptz): Soft-deletion timestamp. The default manager excludes records where this is set.
created_at (timestamptz, Not Null): Timestamp when the plan was created.
updated_at (timestamptz, Not Null): Timestamp when the plan was last updated.
company_id (FK → user_company.id, Not Null): Company that owns the pricing plan.

Constraints and Semantics:

Unique per (company_id, name) while deleted_at is null. A name may be reused after the prior plan is soft-deleted.
API deletion is a soft delete: it deactivates the plan and its cooling-unit assignments and sets deleted_at.
A plan assigned to an active, non-deleted cooling unit cannot be soft-deleted until that assignment is removed or replaced.
Physical deletion of a plan with published versions remains prohibited so historical versions and crate snapshots are preserved.

`storage_pricingplanversion`

Versioned KF-2 pricing configuration for a pricing plan. Versions hold the optional advance-payment capability and own crop groups, brackets, and pricing rules.

Key Columns:

id (int8, Primary Key): Unique identifier.
version_number (int4, Not Null): Version number within the pricing plan.
status (varchar(16), Not Null): Version lifecycle status. Valid values are DRAFT, ACTIVE, and RETIRED.
requires_declared_storage_days (bool, Not Null): Compatibility flag retained in the schema; it is not currently enforced. Planned days are optional and, when declared, are only used for check-in-time projections (upfront-payment caps, coverage validation, reporting estimates) — the actual checkout charge is always calculated from the real elapsed storage duration, whether or not planned days were declared.
upfront_payment_allowed (bool, Not Null): Whether an optional advance storage payment may be registered at check-in.
created_at (timestamptz, Not Null): Timestamp when the version was created.
updated_at (timestamptz, Not Null): Timestamp when the version was last updated.
pricing_plan_id (FK → storage_pricingplan.id, Not Null): Parent pricing plan.
created_by_id (FK → user_user.id): User who created the version.

Constraints and Semantics:

Unique per (pricing_plan_id, version_number).
Only one ACTIVE version is allowed per pricing plan.
Published or crate-referenced versions are immutable.

`storage_pricingcropgroup`

Defines either a taxonomy crop-type scope or a custom crop collection within a pricing-plan version.

Key Columns:

id (int8, Primary Key): Unique identifier.
name (varchar(255), Not Null): Crop group name.
kind (varchar(16), Not Null): Group kind. Valid values are TAXONOMY and CUSTOM.
crop_type_id (FK → storage_croptype.id): Crop type represented by a taxonomy group. Null for custom groups.
is_active (bool, Not Null): Whether the crop group participates in pricing.
pricing_plan_version_id (FK → storage_pricingplanversion.id, Not Null): Version that owns the crop group.

Constraints and Semantics:

Unique per (pricing_plan_version_id, name).
Only one TAXONOMY group may represent a crop type in a pricing-plan version.
TAXONOMY groups require crop_type_id; CUSTOM groups cannot reference a crop type.
Belongs to immutable version-owned pricing configuration once the version is published or referenced.

`storage_pricingcropgroupcrop`

Links crops to custom pricing groups. Taxonomy groups derive their crops from storage_crop.crop_type_id and cannot have explicit memberships.

Key Columns:

id (int8, Primary Key): Unique identifier.
crop_group_id (FK → storage_pricingcropgroup.id, Not Null): Pricing crop group.
crop_id (FK → storage_crop.id, Not Null): Crop included in the group.

Constraints and Semantics:

Unique per (crop_group_id, crop_id).
The referenced group must have kind CUSTOM.
A crop can belong to only one custom crop group per pricing-plan version.

`storage_pricingbracket`

Defines weight or duration bands used by KF-2 pricing rules.

Key Columns:

id (int8, Primary Key): Unique identifier.
dimension_type (varchar(16), Not Null): Bracket dimension. Valid values are WEIGHT and DURATION.
name (varchar(255), Not Null): Human-readable bracket name.
min_value (numeric(18,4)): Inclusive lower bound. Null means open-ended.
max_value (numeric(18,4)): Exclusive upper bound. Null means open-ended.
unit (varchar(8), Not Null): Bracket unit. KG is used for weight brackets and DAY for duration brackets.
sort_order (int4, Not Null): Display and resolution ordering.
pricing_plan_version_id (FK → storage_pricingplanversion.id, Not Null): Version that owns the bracket.

Constraints and Semantics:

Indexed by (pricing_plan_version_id, dimension_type).
max_value must be greater than min_value when both are present.
Unit must match dimension type: WEIGHT → KG, DURATION → DAY.
Only one open-ended bracket is allowed per (pricing_plan_version_id, dimension_type).
Brackets of the same dimension in the same version cannot overlap.
Bounds use inclusive minimums and exclusive maximums. During checkout, the final duration bracket continues to apply beyond its displayed maximum so overstays remain priceable.

`storage_pricingrule`

Defines the amount and billing basis used to calculate KF-2 storage fees.

Key Columns:

id (int8, Primary Key): Unique identifier.
billing_basis (varchar(24), Not Null): Fee calculation basis. Valid values are PER_CRATE_FLAT, PER_CRATE_PER_DAY, PER_KG_FLAT, and PER_KG_PER_DAY.
amount (numeric(18,4), Not Null, CHECK amount >= 0): Rate amount used by the billing basis.
is_active (bool, Not Null): Whether the rule participates in price resolution.
pricing_plan_version_id (FK → storage_pricingplanversion.id, Not Null): Version that owns the rule.
crop_id (FK → storage_crop.id): Individual crop targeted by a crop-specific rule.
crop_group_id (FK → storage_pricingcropgroup.id): Crop group the rule applies to.
weight_bracket_id (FK → storage_pricingbracket.id): Weight bracket the rule applies to.
duration_bracket_id (FK → storage_pricingbracket.id): Duration bracket the rule applies to.

Constraints and Semantics:

Indexed by (pricing_plan_version_id, is_active).
crop_id and crop_group_id cannot both be set.
Bracket references must belong to the same pricing-plan version and match the expected dimension.
Rule shape is unique within a version for each supported generic, crop, or crop-group combination of billing basis, weight bracket, and duration bracket.
Applicable rules resolve in this order: individual crop, custom crop group, taxonomy crop group, then generic fallback. Within a scope, rules matching more resolved bracket dimensions take precedence.

`storage_legacypricingplanmapping`

Tracks the reversible backfill from legacy storage_pricing / storage_coolingunitcrop records to generated KF-2 pricing plans.

Key Columns:

id (int8, Primary Key): Unique identifier.
created_at (timestamptz, Not Null): Timestamp when the mapping was created.
legacy_pricing_id (FK → storage_pricing.id, Not Null): Legacy pricing record used as the source.
legacy_cooling_unit_crop_id (OneToOne → storage_coolingunitcrop.id, Not Null): Legacy cooling-unit/crop assignment used as the source.
pricing_plan_id (FK → storage_pricingplan.id, Not Null): Generated KF-2 pricing plan.

`storage_produce`

Stores details about produce items that have been checked into storage.

Key Columns:

id (int8, Primary Key): Unique identifier for the produce entry.
harvest_date (int4): Date of harvest (stored as an integer).
initial_grade (int4): The quality grade assigned to the produce at check-in.
size (int4): Size category of the produce.
cmp_checkout_completed (bool, Not Null): Indicates if the produce has been completely checked out.
picture (varchar(100)): URL or reference to an image of the produce.
additional_info (varchar(255)): Additional descriptive details.
cmp_last_updated_at (timestamptz): Timestamp of the last update.
checkin_id (FK → operation_checkin.id)
crop_id (FK → storage_crop.id)

`storage_sensorintegration`

Stores authentication and connection details for sensors linked to cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier for the sensor.
type (varchar(16), Not Null): Sensor provider type (victron, ubibot, figorr, ecozen).
username (varchar(150)): Username used for authentication.
password (encrypted varchar): Encrypted password for the sensor.
source_id (varchar(64)): Provider-specific identifier for the sensor device.
date_sensor_first_linked (timestamptz): Timestamp when the sensor was first linked.
date_sensor_modified (timestamptz): Timestamp when sensor details were last modified.
cooling_unit_id (FK → storage_coolingunit.id)

7. User & Company Management

`user_bankaccount`

Stores bank account details associated with users and companies.

Key Columns:

id (int8, Primary Key): Unique identifier for the bank account.
account_name (varchar(64), Not Null): Name associated with the bank account.
account_number (varchar(64), Not Null): Bank account number.
bank_name (varchar(64), Not Null): Name of the bank.

`user_company`

Contains details of registered companies, including financial and operational information.

Key Columns:

id (int8, Primary Key): Unique identifier for the company.
name (varchar(255), Not Null): Name of the company.
country (varchar(2)): Country code of the company's location.
logo (varchar(100)): URL or reference to the company logo.
currency (varchar(3)): Currency code used by the company.
digital_twin (bool, Not Null): Indicates whether the company has a digital twin model.
ML4_market (bool, Not Null): Indicates participation in ML4 market predictions.
ML4_quality (bool, Not Null): Indicates participation in ML4 quality assessments.
ML4_farmers (bool, Not Null): Indicates participation in ML4 farmer analytics.
date_joined (timestamptz): Timestamp of when the company joined.
flag_opt_out_from_marketplace_filter (bool, Not Null): Indicates if the company opts out of marketplace filters.
bank_account_id (FK → user_bankaccount.id)

`user_company_crop`

Links companies to the crops they are associated with.

Key Columns:

id (int8, Primary Key): Unique identifier for the record.
company_id (FK → user_company.id, Not Null)
crop_id (FK → storage_crop.id, Not Null)

`user_country`

Stores country information, primarily referenced by other tables to associate data with specific countries.

Key Columns:

id (int8, Primary Key): Unique identifier for the country.
country (varchar(2)): Two-letter country code.

`user_country_crop`

Maps crops to specific countries, representing where each crop is cultivated.

Key Columns:

id (int8, Primary Key): Unique identifier for the record.
country_id (FK → user_country.id, Not Null)
crop_id (FK → storage_crop.id, Not Null)

`user_cooling_user`

Stores cooling user profiles, including personal details and lifecycle state.

Key Columns:

id (int8, Primary Key): Unique identifier for the cooling user.
first_name (varchar(255)): First name.
last_name (varchar(255)): Last name.
phone (varchar(128)): Phone number in E.164 format.
email (varchar(254)): Email address.
gender (varchar(2)): Gender code.
language (varchar(2)): Preferred language code.
country (varchar(150)): Country of residence.
cooling_user_id (varchar(255)): Operator-assigned identifier; unique per company when set.
is_unknown (bool, Not Null): Indicates whether the cooling user's identity is unknown.
is_profile_only (bool, Not Null): True when the profile has no linked app user account.
is_archived (bool, Not Null): Whether the profile has been archived.
archived_at (timestamptz): Timestamp of archival.
archived_by_id (FK → user_user.id): Operator who archived the profile.
is_anonymized (bool, Not Null): Whether PII has been removed from this profile.
anonymized_at (timestamptz): Timestamp of anonymization.
anonymized_by_id (FK → user_user.id): User who anonymized the profile.
last_action (varchar(32)): Most recent operation type (e.g. checkin, purchase).
last_action_at (timestamptz): Timestamp of the most recent operation.
company_id (FK → user_company.id, Not Null): Company this profile belongs to.
created_by_id (FK → user_operator.id): Operator who created this profile.
user_id (FK → user_user.id): Linked app user account; null for phone-only or no-phone profiles.

`user_cooling_user_cooling_units`

Tracks which cooling units are associated with specific cooling users, including audit fields.

Key Columns:

id (int8, Primary Key): Unique identifier for the record.
cooling_user_id (FK → user_cooling_user.id, Not Null)
cooling_unit_id (FK → storage_coolingunit.id, Not Null)
created_at (timestamptz, Not Null): When the association was created.
last_action (varchar(32)): Most recent operation type for this cooling-user–unit pair.
last_action_at (timestamptz): Timestamp of the most recent operation.

`user_cooling_user_survey`

Collects survey data from cooling users about their experience and farming activities.

Key Columns:

id (int8, Primary Key): Unique identifier for the survey.
user_type (varchar(32)): The role or category of the cooling user.
experience (bool): Indicates whether the cooling user has experience in agriculture.
experience_duration (int2, CHECK experience_duration ≥ 0): Duration of farming experience in years.
date_filled_in (timestamptz): Date when the survey was completed.
date_last_modified (timestamptz): Date when the survey was last modified.
cooling_user_id (FK → user_cooling_user.id)

`user_cooling_user_survey_commodity`

Records detailed information about the crops cooling users produce, including pricing and quantities.

Key Columns:

id (int8, Primary Key): Unique identifier for the survey commodity record.
average_price (int2, CHECK average_price ≥ 0): Average price per unit of the commodity.
unit (varchar(32)): Unit of measurement (e.g., kg, ton).
average_season_in_months (int4, CHECK average_season_in_months ≥ 0): Average length of the growing season.
quantity_below_market_price (float8): Quantity sold below market price.
quantity_self_consumed (float8): Quantity consumed by the farmer's household.
quantity_sold (float8): Quantity sold in the market.
quantity_total (float8): Total quantity harvested.
currency (varchar(3)): Currency used for transactions.
date_filled_in (timestamptz): Date when the survey was completed.
date_last_modified (timestamptz): Date when the survey was last modified.
kg_in_unit (int4, CHECK kg_in_unit ≥ 0): Weight of the commodity per unit.
reason_for_loss (varchar(255)): Explanation for crop loss.
crop_id (FK → storage_crop.id)
cooling_user_survey_id (FK → user_cooling_user_survey.id)

`user_genericusercode`

Stores temporary user codes for various authentication and verification processes.

Key Columns:

id (int8, Primary Key): Unique identifier for the code entry.
type (varchar(32)): Type of code (e.g., verification, reset password).
code (varchar(64), Not Null): The generated user code.
expiration_date (timestamptz): Expiry date for the code.
user_id (OneToOne → user_user.id, Not Null)

`user_invitationuser`

Stores invitations sent to potential users for registration.

Key Columns:

id (int8, Primary Key): Unique identifier for the invitation.
user_type (int2, Not Null, CHECK user_type ≥ 0): The type of user being invited.
phone (varchar(128), Not Null): Phone number of the invitee.
expiration_date (timestamptz, Not Null): Expiry date for the invitation code.
code (varchar(64), Not Null): Unique invitation code.
date_invitation_sent (timestamptz): Timestamp of when the invitation was sent.
sender_id (FK → user_user.id)

`user_invitationuser_cooling_units`

Links invited users to specific cooling units.

Key Columns:

id (int8, Primary Key): Unique identifier for the record.
invitationuser_id (FK → user_invitationuser.id, Not Null)
coolingunit_id (FK → storage_coolingunit.id, Not Null)

`user_notification`

Tracks notifications sent to users for various events.

Key Columns:

id (int8, Primary Key): Unique identifier for the notification.
seen (bool, Not Null): Indicates whether the user has seen the notification.
date (timestamptz, Not Null): Timestamp when the notification was created.
specific_id (int4): Optional reference to a related event or entity.
event_type (varchar(32), Not Null): The type of event triggering the notification.
user_id (FK → user_user.id, Not Null)

`user_operator`

Links operators to companies.

Key Columns:

id (int8, Primary Key): Unique identifier for the operator.
company_id (FK → user_company.id, Not Null)
user_id (FK → user_user.id, Not Null)

`user_serviceprovider`

Links service providers to companies.

Key Columns:

id (int8, Primary Key): Unique identifier for the service provider.
company_id (FK → user_company.id, Not Null)
user_id (FK → user_user.id, Not Null)

`user_user`

Stores details of system users.

Key Columns:

id (int8, Primary Key): Unique identifier for the user.
password (varchar(128), Not Null): User's hashed password.
is_superuser (bool, Not Null): Whether the user has superuser privileges.
is_staff (bool, Not Null): Whether the user is part of the staff.
is_active (bool, Not Null): Whether the user account is active.
date_joined (timestamptz, Not Null): Timestamp of account creation.
username (varchar(255), Not Null): Unique username.
first_name (varchar(255), Not Null): User’s first name.
last_name (varchar(255), Not Null): User’s last name.
email (varchar(254)): Email address of the user.
phone (varchar(128)): User's phone number.
gender (varchar(2)): User’s gender.
last_login (timestamptz): Timestamp of the last login.
language (varchar(2)): Preferred language.
user_code (varchar(8), Unique): Short unique code used to identify and import the user.
is_email_public (bool, Not Null): Whether the user’s email is public.
is_phone_public (bool, Not Null): Whether the user’s phone is public.

`user_user_groups`

Links users to groups.

Key Columns:

id (int8, Primary Key): Unique identifier for the record.
user_id (FK → user_user.id, Not Null)
group_id (FK → auth_group.id, Not Null)

`user_user_user_permissions`

Assigns permissions to users.

Key Columns:

id (int8, Primary Key): Unique identifier for the permission assignment.
user_id (FK → user_user.id, Not Null)
permission_id (FK → auth_permission.id, Not Null)