Recipe Service¶
Purpose¶
The recipe service manages prep recipe documentation — creating, editing, approving, and publishing recipe BOMs (Bills of Materials) along with auto-generated Google Docs for kitchen use. It powers the Recipe Docs tab in OK Manager, which automates BOM updates and Google Doc generation for prep recipes. The service handles the full draft lifecycle from creation through approval, syncing approved BOMs to the Snowflake data warehouse and managing Google Docs across Drafts, Production, and Archive folders.
Note: The Recipes tab in OK Manager also has a bulk operations feature for execution recipe updates (SKU switchouts, modifier changes, quantity updates, packaging changes, and recipe cloning). The backend for bulk operations lives in the legacy C# codebase (
/api/BulkOperations/) because it relies on shared .NET methods already built for the recipe tool. This document focuses on the Python recipe service backend.
Development¶
The recipe service lives in the recipe_service directory. It can be started via:
Architecture Overview¶
The recipe service is composed of three main layers — the drafts service (business logic and workflow), the Google Docs service (document generation and management), and the Snowflake merger (production BOM sync). It uses PostgreSQL (Supabase) for draft storage and Snowflake for production BOM data.
Recipe Drafts Service¶
The drafts service (services/recipe_drafts_service.py) manages the core draft lifecycle. It handles creation, updates, status transitions, starting weight calculation, diff generation against production BOMs, and metadata inheritance from previous drafts. It coordinates between PostgreSQL (draft storage), Snowflake (production BOM reads and writes), and Google Docs (document generation). Snowflake and Google Docs dependencies are lazily initialized so the service can still operate in a degraded state if either is unavailable.
Google Docs Service¶
The Google Docs service (services/google_docs_service.py) handles all interaction with the Google Docs and Drive APIs using a service account. It generates professionally formatted two-column recipe documents with styled metadata, monospaced ingredient tables with color-coded highlight groups, embedded photos, bulleted equipment/handling notes, and preparation steps with inline ingredient highlighting. It manages documents across three Drive folders — Drafts, Production, and Archive — handling moves, overwrites, and archival with timestamps.
Snowflake Merger¶
The Snowflake layer (sql/snowflake_sql/merge_sql.py and query_sql.py) handles reading production subrecipe data and writing approved BOMs. On approval, it syncs the draft's ingredients to the SUBRECIPES and SUBRECIPE_DETAILS tables in Snowflake, handling inserts, updates, and deletes to match the approved draft. It auto-generates master_id values for new recipes.
PostgreSQL Layer¶
The PostgreSQL layer (sql/postgres_sql/recipe_drafts_sql.py) manages all draft metadata and ingredients in Supabase. This includes recipe fields not stored in Snowflake — steps, equipment, handling notes, critical control points, photos, highlight groups, and hidden sections. It uses asyncpg connection pooling.
Draft Workflow¶
Drafts follow a status-gated workflow: - DRAFT — Editable. Can be updated, deleted, or submitted. - PENDING — Submitted for approval. Can be approved or rejected. Google Doc is generated on submit. - PUBLISHED — Approved. BOM merged to Snowflake, Google Doc moved to Production. - REJECTED — Rejected. Can be edited and resubmitted.
Users can create drafts from scratch or from an existing production subrecipe. When creating from an existing subrecipe, the service pulls the current BOM from Snowflake and inherits PostgreSQL-only metadata (steps, equipment, CCPs, etc.) from the most recent published draft for that subrecipe.
Key Points¶
- All operations are synchronous — there are no background workers or job queues. Google Doc generation and Snowflake merges happen inline during requests.
- Authentication uses JWT tokens issued by the .NET auth service (HS256,
orbital.comissuer). Two permission levels:require_mdt_read(MasterItem.Read) andrequire_mdt_write(MasterItem.Write). - Photo uploads go to Azure Blob Storage (
recipe-docscontainer), with a 10 MB limit and JPEG/PNG/WebP/GIF support. - The diff endpoint compares a draft's BOM against the current production subrecipe in Snowflake, categorizing ingredients as added, removed, changed, or unchanged.
- When approving a draft that overwrites an existing production doc, the old doc is archived (copied to the Archive folder with a timestamp) before being replaced.
- Starting weight auto-calculation sums ingredient quantities converted to a common weight UOM (supports g, kg, oz, lb).
- Google Docs use 8 hardcoded highlight colors for ingredient groups, which visually link ingredients in the BOM table to their mentions in preparation steps.
External Integrations¶
| Service | Purpose | Auth |
|---|---|---|
| Google Docs API v1 | Create, update, and style recipe documents | Service account (JSON key from Azure Key Vault) |
| Google Drive API v3 | Manage folders (Drafts/Production/Archive), move and archive docs | Service account |
| Azure Blob Storage | Store recipe and step photos | Connection string from Azure Key Vault |
| Snowflake | Read/write production BOMs (ORBITAL_KITCHENS_DW.RECIPES) |
Snowflake credentials |
| PostgreSQL (Supabase) | Store draft metadata, ingredients, doc URLs | asyncpg connection pool |
Draft Lifecycle Diagram¶
This diagram shows the flow from draft creation through approval, including BOM sync and Google Doc management.
sequenceDiagram
participant User as User (OK Manager)
participant API as Recipe Service API
participant Svc as RecipeDraftsService
participant PG as PostgreSQL (Supabase)
participant SF as Snowflake
participant GDoc as GoogleDocsService
participant Drive as Google Drive
User->>API: POST /recipe-drafts/ (or /from-existing/{uuid})
API->>Svc: create_draft() or create_from_existing()
alt From Existing
Svc->>SF: fetch subrecipe BOM
Svc->>PG: fetch latest published draft metadata
end
Svc->>PG: insert draft + ingredients
Svc-->>User: draft (status: DRAFT)
Note over User: Edit draft, add ingredients, upload photos...
User->>API: POST /{draft_id}/submit
API->>Svc: submit_for_approval()
Svc->>PG: update status DRAFT → PENDING
Svc->>GDoc: generate or update Google Doc
GDoc->>Drive: create doc in Drafts folder
Svc-->>User: draft (status: PENDING, google_doc_url)
User->>API: GET /{draft_id}/diff
API->>Svc: get_diff()
Svc->>PG: fetch draft ingredients
Svc->>SF: fetch production subrecipe
Svc-->>User: diff (added, removed, changed, unchanged)
User->>API: POST /{draft_id}/approve
API->>Svc: approve_draft()
Svc->>SF: merge BOM (insert/update/delete)
Svc->>PG: update status PENDING → PUBLISHED
alt Overwrite Existing Production Doc
GDoc->>Drive: archive old doc (copy to Archive)
GDoc->>Drive: update production doc in-place
GDoc->>Drive: trash draft doc
else New Production Doc
GDoc->>Drive: move doc from Drafts to Production
end
Svc-->>User: draft (status: PUBLISHED, production_doc_url)Frontend Notes¶
Recipe Docs Tab¶
The Recipe Docs tab in OK Manager (/recipe_docs route) provides three views:
- List view — Filterable table of all drafts with status chips, search, and per-row actions (edit, review, open doc, delete).
- Form view — Full recipe editor with metadata fields, an interactive ingredient table with cost calculations and color-coded highlight groups, step editor with photo support and ingredient highlighting, and Google Doc generation.
- Approval view — Side-by-side layout showing recipe summary with change highlights on the left and a BOM diff table on the right, with approve/reject actions.
Recipes Tab — Bulk Operations¶
The Recipes tab (/boms route) includes a bulk operations modal with a 4-step wizard (Select Operation, Configure, Preview, Results). Six operations are available:
| Operation | Purpose |
|---|---|
| SKU Switchout | Replace one ingredient with another across recipes |
| Add/Update Modifier | Add or update modifier ingredients on target recipes |
| Remove Modifier | Remove all ingredients for a modifier from target recipes |
| Quantity Change | Update quantity/UOM for an ingredient across recipes |
| Packaging Update | Add or remove packaging items/modifiers across recipes |
| Clone Recipe | Copy a recipe with all ingredients and packaging |
These operations hit the C# backend at POST /api/BulkOperations/Preview and POST /api/BulkOperations/Execute.
Future Direction¶
The long-term goal is to evolve this into an all-in-one documentation and BOM update tool that replaces the current Google Sheets workflow entirely. With current resources, the prep recipe drafts and doc generation described here made sense as a phase 1.