Data model

A mental model of what's in the database. For exhaustive column-by-column references, see the auto-generated schema/.

There are three Postgres schemas, each owning one domain:

1. public — carbon accounting (activities, categories, emission factors, calculated emissions, reporting workflow)
2. parcel — last-mile/regional logistics (orders, transporter network, per-route emissions)
3. basejump — multi-tenant auth (accounts, members, roles, billing)

Everything in public and parcel is scoped by account_id, which points back to basejump.accounts.id. That's the single tenant key.

public — carbon accounting

The core chain is Activity → Category → Emission factor → Emission.

Table	What it is
category	Hierarchical taxonomy of emission sources (e.g. Waste → Incineration). Stores the form schema for data collection and the AI instructions used for ESRS text generation.
activity	One operational event recorded by a customer — "5 tons of waste collected on 2024-11-15". Belongs to a category and an account; may reference a supplier/customer account.
emission_factor	The science: how much CO₂e per unit of activity. Scoped by category, country, and year. Splits Scope 1 / Scope 2 / Scope 3.
emission	The calculated result: links an activity to an emission_factor and stores the quantity → CO₂e flows from there.
account_category	Workflow state: which categories an account is reporting on this year, status (not_started, in_progress, completed).
account_category_user	Which user on a team is responsible for which category.
category_submission	Annual sustainability content: AI-drafted or manual text per (account, category, year), with requirement-validation status.
country, nace_category	Reference data — ISO countries, NACE industry codes for emission-factor lookup and regulatory mapping.

Supporting tables (accounting_category, etc.) bridge to external accounting integrations but aren't part of the hot path.

parcel — logistics emissions

The core chain is Order → Route (edges between nodes) → Emission summary → Daily aggregate.

Table	What it is
transporter_node_v2	A logistics hub or pickup point for a transporter ("Early Bird depot at Stockholm Central"). One per address per transporter.
transporter_edge_v2	A directed route between two nodes. Carries the vehicle type, load factor, empty-running %, distance, and emissions class.
transporter_vehicle_v2	Vehicle specs: fuel type, consumption per km, capacity. Used to derive per-edge emissions.
order	A single shipment — customer + transporter + addresses + weight + product.
address_v2 + address_zipcode / _street / _city / _locality / _region / _urban_city	Normalised address hierarchy for routing and zipcode-level emissions classification.
emission_summary	The calculated result for a route (origin zipcode → destination district node), aggregating well-to-tank and tank-to-wheel emissions.
orders_daily_summary	Daily aggregate per (customer, transporter): total weight, total CO₂e, order count, routing coverage %.
vehicle_fuel_type, vehicle_type	Reference data driving the WTT/TTW factors.

Naming convention

• view_* — standard views (must use SECURITY_INVOKER = ON).
• viewm_* — materialized views. They need an explicit refresh, usually via parcel.refresh_network_graph(). Key examples: viewm_transporter_routes, viewm_transporter_nodes, viewm_zipcode_districts, viewm_zipcode_emissions.

Pipeline

1. Daily CSV import from Google Cloud Storage → imported_list (status-tracked via the process_status enum)
2. Continuous worker (tasks/order-relations-updater) runs update_order_relations to populate FKs (~690 rows/min)
3. Emission calculation: create_emission_summary → calculate_emission_summary per route
4. Daily rollup: create_orders_daily_summary writes aggregates (cron-scheduled)

basejump — multi-tenant primitives

Table	What it is
accounts	A team OR an individual (personal_account = true when id = user_id). Carries NACE category and default emission-factor preferences. public_metadata.feature_flags holds opt-in flags.
account_user	Membership: which users belong to which account, with role owner / member / super_admin.
billing_customers, billing_subscriptions	Stripe integration per account.
invitations	Invite tokens for onboarding new members.
config	Platform-wide settings (e.g. enable_team_accounts).

How the schemas connect

There is no direct FK between parcel and public. They share a tenant key only:

• basejump.accounts.id appears as account_id in every multi-tenant table — activity.account_id, parcel.order.transporter_account_id, parcel.order.customer_account_id, etc.
• Both domains compute emissions independently and surface them in dashboards under the same account.

Security patterns to know

• Every public table has RLS enabled. The default policy reads account_id IN (SELECT basejump.get_accounts_with_role()).
• Views inherit RLS only when SECURITY_INVOKER = ON is set; if you forget this, the view will leak across tenants.
• Functions prefer SECURITY INVOKER and GRANT EXECUTE ... TO AUTHENTICATED. Use SECURITY DEFINER only when you need to bypass RLS deliberately — and document why.