Skip to main content

Data Model (Turso)

Applies to: v3 (Turso)

Data Model (Turso)​

Barcode tables
barcodes (+ whitelist_barcodes, whitelists)
Company tables
boycott_companies, alternative_companies, boycott_levels
Category tables
company_categories, product_categories
Region tables
geographic_regions (+ junctions)
Relations
companies ↔ categories, alt ↔ regions, barcode ↔ company
Reports
boycott_reports, alternative_reports (+ junctions)

Scope: this document describes the Turso (libSQL) schema used by v3. Directus collection interfaces live in src/lib/dbs/directus/schema.ts.

This section lists the key tables and their relationships. Names reflect src/lib/dbs/schema/schema.ts.

Barcode Tables

  • barcodes: cached external enrichment results (barcode, product_name, brand_name, scan_count)
  • whitelist_barcodes: curated barcode-to-whitelist mappings
  • whitelists: whitelist entries with parent_company_name for company matching

Common barcode columns

  • barcode (unique), product_name, brand_name
  • is_official (boolean), scan_count
  • created_at, updated_at

Company Tables

  • boycott_companies: name, proof, level, is_bds, parent_company (FK → boycott_companies.id), countries, vote_count
  • alternative_companies: name, countries, vote_count
  • boycott_levels: label, description (lookup for boycott severity; FK from boycott_companies.level)

Relations

  • Barcodes → BoycottCompanies (optional)
  • Barcodes → AlternativeCompanies (optional)
  • BoycottCompanies ↔ AlternativeCompanies (many-to-many)
  • Companies ↔ CompanyCategories (many-to-many)
  • AlternativeCompanies ↔ GeographicRegions (many-to-many)
  • Country filters support two inputs: ISO alpha-2 codes (country=DE) or MCC/MNC pairs (code_type=mcc|mnc and numeric country code). MCC/MNC values are translated to ISO codes via getISOCountryCode before querying.

Category Tables

  • company_categories: canonical category list shared across companies and reports.
  • product_categories: Google product-category taxonomy slices mapped (optionally) back to company_categories.
  • Junctions:
    • boycott_companies_company_categories
    • alternative_companies_company_categories
    • boycott_reports_company_categories
    • alternative_reports_company_categories

Region Tables

  • geographic_regions: normalized region list (mirrors the Directus “regions” field).
  • Junctions:
    • alternative_companies_geographic_regions
    • boycott_reports_geographic_regions
    • alternative_reports_geographic_regions

Lookup Tables

  • reward_point_menu: action-to-points mappings for gamification.

Mermaid (Conceptual)

Directus Interfaces

  • Used for syncing: company, alternative_company, level, category, upc_barcode, whitelist, whitelist_barcode.
  • Definitions: src/lib/dbs/directus/schema.ts.

DTO & Mapper References

  • Boycott companies: src/dto/company.dto.ts, src/mappers/company.mapper.ts
  • Alternative companies: same as above (shared DTO file)

Code references​

Last updated on