Purpose

When a conjugation is displayed on the screen, there should be example sentences in the same language that are examples of the conjugation in use.

We want the same functionality for infinitives, which are stored in a different table.

Example

In French, the conjugation of conduire for vous in Indicatif - Imparfait is vous conduisiez.

This would be matched to the sentence Je ne veux pas que vous conduisiez ma voiture., which translated into English is I don't want you to drive my car..

We want every conjugation to have at least 1 matching sentence, and to support multiple matches.

Requirements

  1. Matching Logic:
    • Matching is from the full form of the conjugation (pronoun and verb), eg: vous conduisiez
    • For infinitives, matching is from the infinitive form, eg: conduire
    • Matching is case-insensitive
    • Sentences are matched by language
    • Only sentences ≤80 characters are matched for performance
  2. Performance:
    • Background process pre-performs matches for quick retrieval
    • Uses PostgreSQL GIN full-text search indexes
    • Processes in batches to handle large tables
    • If no matches available for a conjugation/infinitive, this is OK
  3. Data Integrity:
    • System is idempotent - can be re-run safely
    • Handles missing or incomplete indexes gracefully
    • Overall system performance prioritized over matching completeness

Implementation

1. Database Schema

Join Tables:

  • sentence_conjugations - links sentences to conjugations
  • sentence_infinitives - links sentences to infinitives
  • Both tables include cached metadata fields for performance

Tracking Columns:

  • conjugations.sentences_matched (boolean) - Has this conjugation been processed for sentence matching?
  • infinitives.sentences_matched (boolean) - Has this infinitive been processed for sentence matching?

Indexes:

  • GIN index on sentences.sentence using ‘simple’ configuration for full-text search
  • Standard foreign key indexes on join tables
  • Index on conjugations(language, sentences_matched) for efficient batch processing
  • Index on infinitives(language, sentences_matched) for efficient batch processing

2. Service Layer Architecture

Core Services:

  • Resources::Conjugations::FindSentenceMatchesService - Given a conjugation, finds all sentences that contain it
  • Resources::Infinitives::FindSentenceMatchesService - Given an infinitive, finds all sentences that contain it
  • Both services use PostgreSQL full-text search for performance

Matching Algorithm:

  1. Use to_tsvector('simple', sentence) with plainto_tsquery('simple', form) for word-boundary matching
  2. Strip non-word characters from conjugation/infinitive forms
  3. Match only within same language
  4. Only search sentences ≤80 characters for performance
  5. Create records in join tables with metadata
  6. Update sentences_matched tracking column on conjugation/infinitive

3. Background Jobs

Conjugation-Driven Processing:

  • Resources::Conjugations::FindSentenceMatchesJob - Find sentences for single conjugation
  • Can be triggered manually or via API
  • Skips if sentences_matched = true (already processed)
  • Uses Resources::Conjugations::FindSentenceMatchesService
  • Updates conjugations.sentences_matched = true when complete

Infinitive-Driven Processing:

  • Resources::Infinitives::FindSentenceMatchesJob - Find sentences for single infinitive
  • Can be triggered manually or via API
  • Skips if sentences_matched = true (already processed)
  • Uses Resources::Infinitives::FindSentenceMatchesService
  • Updates infinitives.sentences_matched = true when complete

4. Rake Tasks

Main Task: immersive:match_sentences[language,mode,limit]

Modes:

  • conjugations_only - Match only conjugations to sentences
  • infinitives_only - Match only infinitives to sentences
  • both - Match both (default)
  • rerun - Force reprocessing (clears existing matches)

Options:

  • language - Single language code (fr, de, es, etc.) or blank for all
  • limit - Optional limit for testing (e.g., limit=100 for first 100 items)

Examples:

# Process all unmatched conjugations for French
rake "immersive:match_sentences[fr,conjugations_only]"

# Process first 50 infinitives for German (testing)
rake "immersive:match_sentences[de,infinitives_only,50]"

# Reprocess all conjugations and infinitives for Spanish
rake "immersive:match_sentences[es,both,rerun]"

# Process all languages
rake immersive:match_sentences

Features:

  • Progress reporting with percentage complete
  • Skips already-matched items unless rerun specified
  • Idempotent - safe to run multiple times
  • Reports statistics at completion

Testing Task: immersive:test_sentence_matching[language,limit]

Tests the complete sentence matching system with a small dataset:

# Test with first 10 conjugations/sentences in French
rake "immersive:test_sentence_matching[fr,10]"

5. Performance Considerations

GIN Index Management:

  • GIN indexes are expensive for writes due to pending list mechanism
  • Default gin_pending_list_limit is 4MB
  • Autovacuum flushes pending list periodically
  • For very large tables (>1M rows), consider:
    • Adjusting gin_pending_list_limit
    • Tuning autovacuum frequency
    • Using CONCURRENTLY for index creation

Batch Processing Strategy:

  • Process in small batches to avoid memory issues
  • Report progress frequently for visibility
  • Use find_each for memory-efficient iteration
  • Skip already-processed items by default

Query Optimization:

  • Use single SQL query with joins instead of N+1 queries
  • Leverage denormalized language/lemma columns
  • Filter on common: true verb lemmas only
  • Use insert_all for bulk inserts

6. Testing and Validation

Test Data Generation:

  • Create small test datasets per language
  • Include edge cases (short/long forms, special characters)
  • Verify GIN index exists and is usable

Validation Checks:

  • Count of matched vs unmatched conjugations
  • Average sentences per conjugation
  • Sentences with no matches vs sentences with matches
  • Performance metrics (queries per second)

7. Approach

Verb-Driven Matching: The system uses a verb-driven approach where:

  1. For each conjugation/infinitive, we find all sentences containing it
  2. This is more efficient than sentence-driven matching for our use case
  3. Rake tasks iterate through conjugations/infinitives and call the service for each
  4. Background jobs can process individual conjugations/infinitives as needed
  5. The sentences_matched flag prevents duplicate processing
This site uses Just the Docs, a documentation theme for Jekyll.