Contributing to the Semantic Layer

Actyze's semantic layer is what turns natural-language questions into accurate SQL. It relies on community-contributed knowledge packs that teach the system about industry-specific terminology, relationships, metrics, and query patterns.

This guide explains each contribution type and how to submit yours.

Synonym Packs

Synonym packs map business terms to database column names so the AI understands domain-specific language.

Each pack is a YAML file organised by industry:

# synonyms/ecommerce.yaml
synonyms:
  - term: "revenue"
    columns: ["total_amount", "order_total", "gross_revenue", "net_sales"]
  - term: "customer"
    columns: ["buyer", "client", "account", "purchaser"]
  - term: "SKU"
    columns: ["product_id", "item_code", "product_code"]
  - term: "churn"
    columns: ["is_churned", "churn_flag", "subscription_status"]

Guidelines:

• Group synonyms by industry (e-commerce, SaaS, finance, healthcare, etc.).
• Include common abbreviations and acronyms.
• Keep mappings general enough to apply across different schemas.

Relationship Heuristics

Relationship heuristics define patterns the system uses to infer JOINs between tables when explicit foreign keys are not declared.

# relationships/naming_conventions.yaml
heuristics:
  # Column named "X_id" likely references table "X"
  - pattern: "{table}_id"
    inference: "JOIN {table} ON {source}.{table}_id = {table}.id"

  # Columns sharing the same name across tables are likely joinable
  - pattern: "shared_column_name"
    inference: "JOIN {target} ON {source}.{column} = {target}.{column}"

  # Common foreign key patterns
  - pattern: "fk_{referenced_table}"
    inference: "JOIN {referenced_table} ON {source}.fk_{referenced_table} = {referenced_table}.id"

Guidelines:

• Document the naming convention the heuristic targets.
• Include confidence levels (high, medium, low) so the system can rank ambiguous joins.
• Add negative examples (patterns that look like joins but are not).

Verified Query Templates

Verified query templates are pre-approved SQL patterns for common business questions. They serve as few-shot examples that improve accuracy for frequently asked queries.

# templates/saas_metrics.yaml
templates:
  - question: "What is the monthly recurring revenue?"
    sql: |
      SELECT
        DATE_TRUNC('month', payment_date) AS month,
        SUM(amount) AS mrr
      FROM subscriptions
      WHERE status = 'active'
      GROUP BY 1
      ORDER BY 1
    tags: ["saas", "revenue", "mrr"]

  - question: "What is the customer churn rate?"
    sql: |
      SELECT
        DATE_TRUNC('month', churned_at) AS month,
        COUNT(*) * 100.0 / LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', churned_at)) AS churn_rate
      FROM customers
      WHERE churned_at IS NOT NULL
      GROUP BY 1
      ORDER BY 1
    tags: ["saas", "churn", "retention"]

Guidelines:

• Use standard SQL (ANSI) where possible; note dialect-specific syntax.
• Include multiple phrasings of the same question in the question field or as aliases.
• Tag templates so the system can match them to the right context.

KPI Definition Packs

KPI packs define standard metrics per industry with their formulas, so the AI can compute them correctly without guessing.

# kpis/saas.yaml
kpis:
  - name: "MRR (Monthly Recurring Revenue)"
    formula: "SUM(amount) WHERE status = 'active' GROUP BY month"
    description: "Total recurring revenue normalised to a monthly period."

  - name: "CAC (Customer Acquisition Cost)"
    formula: "SUM(marketing_spend + sales_spend) / COUNT(new_customers)"
    description: "Average cost to acquire a new customer."

  - name: "LTV (Lifetime Value)"
    formula: "AVG(revenue_per_customer) * AVG(customer_lifespan_months)"
    description: "Predicted total revenue from a customer over their lifetime."

# kpis/ecommerce.yaml
kpis:
  - name: "AOV (Average Order Value)"
    formula: "SUM(order_total) / COUNT(DISTINCT order_id)"
    description: "Average revenue per order."

  - name: "Cart Abandonment Rate"
    formula: "(COUNT(carts_created) - COUNT(orders_completed)) / COUNT(carts_created) * 100"
    description: "Percentage of shopping carts that do not convert to orders."

Available industry packs: SaaS, E-commerce, Finance, Healthcare.

How to Test Your Contributions

1. Fork the repository and create a branch for your contribution.

2. Validate YAML syntax:

   pip install yamllint
   yamllint your-file.yaml

3. Run the test suite to ensure your additions do not break existing behaviour:

   make test-semantic

4. Test with sample queries — connect a database with a matching schema and verify that your synonyms, heuristics, or templates improve query accuracy.

5. Submit a pull request with:

   • A clear description of the industry or use case your contribution covers.
   • Example questions that are now answered correctly thanks to your changes.
   • Any edge cases or limitations you are aware of.

File Organisation

semantic/
├── synonyms/
│   ├── ecommerce.yaml
│   ├── saas.yaml
│   ├── finance.yaml
│   └── healthcare.yaml
├── relationships/
│   └── naming_conventions.yaml
├── templates/
│   ├── saas_metrics.yaml
│   ├── ecommerce_analytics.yaml
│   └── finance_reporting.yaml
└── kpis/
    ├── saas.yaml
    ├── ecommerce.yaml
    ├── finance.yaml
    └── healthcare.yaml