NOTICE: All information contained herein is, and remains
the property of TechnoCore Automate.
The ObjConversation.py module implements a page-based
conversational state machine for multi-channel chatbot
interactions. It manages conversation definitions, page
navigation, option handling, contact tracking, and intent
matching across channels such as Console, Discord,
WhatsApp, and Slack.
The module contains two classes:
Master conversation definition. One row per conversation.
| Column | Type | Description |
|---|---|---|
| ConversationCode | char(255) | Primary key identifier |
| Package | char(255) | Package scope (e.g. HOMECHOICE, CORE) |
| WebBanner | char(255) | Banner image URL for web channel |
| Avatar | char(255) | Bot avatar image URL |
| BaseTable | char(255) | Base data table name |
| BaseURL | char(255) | Base URL for web interactions |
| Glyph | char(255) | Icon glyph identifier |
| RemoteConnection | char(255) | Remote connection reference |
| Title | char(255) | Conversation display title |
| Description | text | Conversation description |
| Msisdn | char(50) | Associated phone number |
| ConversationExpireTime | int | Session expiry in seconds (default 14400) |
| RoleText | text | Persona/system prompt injected into all workflows in this conversation |
| ConstantsTable | varchar(255) | Table name whose key/value rows are loaded as $key$ substitution constants |
| DefaultLang | varchar(10) | Default language code seeded into context["lang"] on load (e.g. AF, EN) |
| OnStartWorkflow | varchar(255) | Workflow to run when a new conversation session begins; result keys are merged into context |
| ContextSql | text | SQL run at session start to populate extra context variables; see ContextSql |
| ContactIdSql | text | SQL that resolves contact_id dynamically at startup; see Contact ID Resolution |
| Userid | varchar(255) | Static fallback contact_id used when ContactIdSql is not set or returns empty |
| Active | char(2) | Active flag (Y/N) |
| CreatedDate | datetime | Record creation timestamp |
| ModifiedDate | datetime | Last modification timestamp |
Channel-specific configuration per conversation.
| Column | Type | Description |
|---|---|---|
| ConversationCode | varchar(255) | FK to def_conversation |
| Channel | varchar(50) | Channel name (CONSOLE, DISCORD, WHATSAPP, SLACK) |
| Botname | varchar(255) | Bot display name for this channel |
| BotEmoji | varchar(50) | Emoji prepended to $botname$ for this channel (leave blank for CONSOLE) |
| BotAvatar | varchar(500) | Avatar image URL for this channel (used via $botavatar$) |
| Template | varchar(255) | Message template reference |
| OnWelcomeMessage | text | Welcome message text |
| OnUploadSql | text | SQL executed on file upload |
| OnUploadMessage | text | Message sent on file upload |
| OnHelpMessage | text | Help message text |
| OnDigitMessage | text | Message for digit-only input |
| OnTextMessage | text | Message for text input |
| OnLocationMessage | text | Message for location input |
| PageLength | int | Items per page for pagination (default 5, capped at 9 by the engine) |
| ApiToken | varchar(255) | Channel API token |
| BotActivation | varchar(255) | Bot activation keyword |
| ErrorMessage | text | Default error message |
| TimeoutMessage | text | Session timeout message |
| TimeoutSeconds | int | Channel-level timeout (default 300) |
Page definitions for conversation flow.
| Column | Type | Description |
|---|---|---|
| ConversationCode | char(255) | FK to def_conversation |
| Package | char(255) | Package scope |
| PageNo | int | Page number (1-based) |
| HeaderText | text | Page header/prompt text |
| Option1-6 | text | Option text for positions 1-6 |
| Option1-6Callback | text | Callback command for each option |
| Option1-6Display | char(50) | Display override (N hides option from menu) |
| OptionOverrideSql | text | SQL to dynamically generate options; see OptionOverrideSql |
| OptionOverrideShow | text | Display column from override SQL |
| OptionOverridePossibleCallbacks | text | Allowed callbacks from override SQL |
| PreSql | text | SQL executed before page render |
| PostSql | text | SQL executed after user responds |
| RerouteSql | text | SQL to reroute conversation flow |
| PageName | varchar(255) | Named alias for this page; unique per conversation |
| RerouteWorkflow | varchar(255) | Workflow code run synchronously; param1 of result = next page |
| HeaderReport | varchar(255) | Report code whose JSON output is rendered as additional header lines |
| HeaderReport2 | varchar(255) | Second report rendered below HeaderReport |
| HeaderReport3 | varchar(255) | Third report — passed as AI context rather than rendered directly |
| AiPrompt | text | AI prompt run against the header content |
| AiPromptMode | varchar(10) | NOTE (appended) or REPLACE (replaces header) |
| AiPromptFallback | text | Text shown if the AI call returns empty |
| TimeoutSeconds | int | Page-level timeout (default 0 = inherit) |
| MaxRetries | int | Max retry attempts (default 3) |
| HeaderImage | varchar(500) | Image URL rendered above the header |
| MediaUrl | varchar(500) | Media attachment URL |
| PageBanner | varchar(500) | Banner image for this page (overrides conversation banner) |
| OptionContextVar | varchar(255) | Context key populated from selected option's ContextValue |
Per-conversation contact tracking table (created dynamically).
| Column | Type | Description |
|---|---|---|
| ConversationCode | varchar(255) | Conversation identifier |
| Package | varchar(255) | Package scope |
| SenderId | varchar(255) | Contact/sender identifier |
| ConversationGuid | varchar(255) | Session GUID |
| Channel | varchar(50) | Active channel |
| Firstname | varchar(255) | Contact first name |
| Lastname | varchar(255) | Contact last name |
| PageNo | int | Current page number |
| Offset | int | Current pagination offset |
| ExtraContext | text | JSON blob of extra context fields persisted across sessions |
| LastUpdate | timestamp | Last interaction time |
Per-conversation data capture table (created dynamically).
| Column | Type | Description |
|---|---|---|
| Guid | char(255) | Session GUID (PK) |
| CreateTime | datetime | Session start |
| UpdateTime | datetime | Last update |
| Channel | char(255) | Channel used |
| MsIsdn | char(255) | Phone number |
| RDG | int | Random data group |
The core flow is driven by read_conversation():
def_conversation joined with def_conversation_detailcontact_id is supplied,ContactIdSql; fall back to static Userid.conversation_factory() (Console, Discord, etc.).data_conversation_contact_{code}. Restore page numberContextSql to populatedef_conversation_page. Cached with TTL.OptionOverrideSql results.data_conversation_{code} and track in MongoDB.When contact_id is not supplied explicitly (e.g. via CLI or API), the engine
resolves it through a priority chain:
read_conversation().ContactIdSql — SQL stored on def_conversation; the engine runs itcontact_id. Useful forUserid — Static varchar field on def_conversation; used as aContactIdSql is not set or returns empty.SELECT u.US_USERNAME
FROM trader.sales_transaction_header th
JOIN trader.users u
ON u.US_ID = th.TH_USER_ID
AND u.US_STORE_ID = th.TH_STORE_ID
WHERE th.META_TH_TRANSACTION_DATE >= DATE_FORMAT(NOW(), '%Y-01-01')
AND th.TH_TYPE = 1
AND th.TH_STATUS = 0
AND u.US_NAME NOT LIKE '%LEFT%'
AND u.US_STATUS = 0
GROUP BY u.US_ID, u.US_USERNAME, u.US_NAME
ORDER BY SUM(th.TH_TRANSACTION_TOTAL) DESC
LIMIT 1
Caveat:
ContactIdSqlmust return exactly one scalar value (one row,
one column). The engine callssql_get_value()— multiple columns are
ignored and multiple rows cause only the first to be used.
Several fields accept raw SQL that runs through local_process_text() before
execution. Follow these rules to avoid silent failures.
Dynamically generates the option list for a page. The result rows must use
these exact column aliases — the engine reads them by name:
| Column alias | Purpose |
|---|---|
Option |
Option display text shown to the user |
OptionCallback |
Page name or number to navigate to on selection |
OptionDisplay |
Y = visible, N = hidden (hidden options still navigate but don't appear in the menu) |
ContextValue |
Optional value stored in context under OptionContextVar |
Wrong aliases cause silent empty options. If your SQL uses
ShowText,
Callback, orVisibleinstead, all options return as empty strings and
the page appears to have no choices.
UNION ALL with ORDER BY — MariaDB rejects ORDER BY in the first leg of a
UNION ALL without parentheses. Wrapping the first SELECT in (...) triggers
a second issue: sql_get_dictionary_list uses _sql_patch() which prepends
SELECT to any SQL that does not start with a keyword — SQL starting with (
is misidentified and silently returns 0 rows.
Use a derived table pattern instead:
-- WRONG: ORDER BY in first UNION leg
SELECT Name AS Option, 'page2' AS OptionCallback, 'Y' AS OptionDisplay, '' AS ContextValue
FROM my_table
ORDER BY Name
UNION ALL
SELECT 'Back', 'dashboard', 'Y', '' FROM dual
-- CORRECT: derived table with outer ORDER BY
SELECT Option, OptionCallback, OptionDisplay, ContextValue
FROM (
SELECT Name AS Option, 'page2' AS OptionCallback, 'Y' AS OptionDisplay, '' AS ContextValue,
Name AS SortKey
FROM my_table
UNION ALL
SELECT 'Back', 'dashboard', 'Y', '', '' FROM dual
) t
ORDER BY SortKey
Language blocks in SQL string literals — local_process_text() resolves
[[LANG:...]] blocks before the SQL is executed. Blocks inside single-quoted
string literals in the SQL are resolved at the Python level, producing the
plain text value in the final SQL string. This is intentional and works
correctly. Do not put [[LANG:...]] blocks outside of string literals
(e.g. in column names or keywords), as they will corrupt the SQL.
Pagination placeholders — OptionOverrideSql supports $offset$ and
$pagelength$ which are resolved from the current context. $pagelength$ is
capped at 9. Use $nextoffset$ for the "next page" link value. Example:
SELECT Name AS Option, 'client_overview' AS OptionCallback, 'Y' AS OptionDisplay,
AccountCode AS ContextValue
FROM customers
WHERE Status = 1
ORDER BY Name
LIMIT $offset$, $pagelength$
SQL executed before/after a page renders or the user responds. Errors are
caught and logged via self.debug() — they do not surface to the user.
Avoid SELECT statements; these fields are for DML (INSERT, UPDATE) or
procedural side-effects.
SQL that returns a page number or page name to redirect to. Must return a
single scalar value. An empty or non-numeric result keeps the user on the
current page.
SQL stored on def_conversation and run once per session at startup (after
contact state is loaded). Use it to inject session-wide variables into context
from the database — e.g. a customer's account tier, a salesman's store ID,
or today's target list count.
The query result is merged into self.context as key/value pairs from the
first returned row. Keys become available as $key$ placeholders in all
subsequent SQL and text fields for the session.
Caveat:
ContextSqlruns once and is not re-evaluated during the
session. Use page-levelPreSqlfor values that must refresh on each page.
| Method | Purpose |
|---|---|
calculate_next_offset() |
Compute next pagination offset from current context |
local_process_text(text) |
Replace placeholders: $guid$, $channel$, $request$, $nextoffset$, language blocks, emoji codes |
_resolve_lang_blocks(text) |
In-place substitution of [[LANG:...]] sibling groups; preserves all surrounding text |
get_page_detail(pageno) |
Load page definition with caching |
get_page_option(option, callback, display) |
Build option dict from raw fields |
get_pagetext(pageno) |
Build complete option set for a page |
store_option(opts) |
Store current options (copy) |
reset_options() |
Clear stored options and command counter |
data_line(request) |
Record user response in SQL and MongoDB |
read_intent(content, opts) |
Match input to option via numeric, keyword, or fuzzy matching |
resolve_page(identifier) |
Resolve a page identifier (number or name string) to a page number |
execute_reroute_workflow(workflow_code) |
Run a workflow synchronously; return param1 as next page identifier |
render_report_as_lines(report_code) |
Run a report and format its JSON output as display lines |
read_contact(code, engine, contact_id) |
Load or create contact state |
read_conversation(code, contact_id, content, engine) |
Main conversation loop entry point |
journey_test(conversation_code, contact_id) |
BFS traversal of all reachable pages; returns pass/fail results per page |
conversation_factory(engine) |
Dynamic engine loader with caching |
RecieveMessage(sender_guid, message) |
Inbound message handler (called by ServeConversation) |
display_keys() |
Display available data keys for debugging |
| Method | Purpose |
|---|---|
render_flow(conversation_code) |
Rich tree visualization of conversation flow |
display_summary(conversation_code) |
Rich table summary of conversation config |
read(conversation_code) |
Load conversation definition into service object |
load_reference(filename, new_code) |
Import conversation from YAML file |
save_reference(code, filename) |
Export conversation to YAML file |
list_engines() |
List available channel engines |
TraceConversation(conversation_code) |
Debug trace: load and display summary |
Configurable values are stored in ObjConversation.yaml
under the constants: section and accessed via
self.get_constants():
| Constant | Default | Description |
|---|---|---|
| emoji_placeholders | 9 emoji codes | List of {name} placeholders converted to :name: |
| help_command | H | Command letter for help |
| help_text | Send H for help | Help option display text |
| restart_command | R | Command letter for restart |
| restart_text | Send R to restart | Restart option display text |
| fuzzy_match_threshold | 75 | Minimum fuzz ratio for fuzzy intent matching |
In addition to constants, local_process_text() expands these
dynamic placeholders in page and SQL text:
| Placeholder | Value |
|---|---|
$offset$ |
Current pagination offset from context |
$pagelength$ |
Current page length (capped at 9) |
$nextoffset$ |
offset + pagelength (for "next page" links) |
$pagename$ |
PageName of the current page (empty if unset) |
$roletext$ |
RoleText from the parent conversation definition |
$botname$ |
BotEmoji + Botname for the active channel (emoji omitted if blank) |
$botavatar$ |
BotAvatar URL for the active channel |
$lang$ |
Active language code (e.g. AF, EN) from context["lang"] |
$userid$ / $contactid$ |
Active contact_id |
$guid$ |
Current session GUID |
$KEY$ |
Any key in self.context or self.constants is expanded as $KEY$ |
Any text field (HeaderText, options, OnWelcomeMessage, OptionOverrideSql
string literals, etc.) supports inline language blocks using the [[LANG:text]]
syntax. Each contiguous group of sibling blocks is resolved in-place —
surrounding text is preserved unchanged.
[[AF:Welkom terug, *firstname*!]][[EN:Welcome back, *firstname*!]]
_resolve_lang_blocks() selects the block matching context["lang"]. If no
block matches, the first is used as a fallback so content is never silently
dropped. Text with no [[ blocks passes through unchanged.
Caveat: the resolver operates on the raw SQL string before the query is
sent to the database. Language blocks inside SQL string literals are resolved
to their plain text value (e.g.'[[AF:Terug]][[EN:Back]]'becomes
'Terug'). This is correct and intentional. Do not use[[LANG:...]]
blocks outside string literals in SQL — they will corrupt keywords, column
names, or operators.
The active language is seeded from DefaultLang on def_conversation when a
conversation loads. Available languages are self-derived via
get_available_langs(), which scans all text fields for [[LANG:...]] blocks
— no manual configuration is needed.
Three module-level caches with TTL support:
| Cache | Default TTL | Content |
|---|---|---|
| CONVERSATION_BUFFER | 300s | Conversation definitions (ObjData instances) |
| CONVERSATION_ENGINE | 3600s | Imported channel engine modules |
| CONVERSATION_PAGE_BUFFER | 300s | Page definitions (ObjData instances) |
Cache entries are stored as (value, timestamp) tuples.
The _cache_get() function checks expiry using
time.monotonic(). Expired entries are evicted on read.
Note: page definitions are cached by
(conversation_code, pageno, package).
After updatingOptionOverrideSqlor other page fields in the database, the
cache must expire (or the process restarted) before changes take effect.
journey_test(conversation_code, contact_id="") performs a BFS traversal of
all pages reachable from page 1 by following option callbacks. It returns one
result dict per visited page:
{
"pageno": int, # page number
"reachable": bool, # get_pagetext() returned non-empty opts
"has_output": bool, # header non-empty OR visible options > 0
"header": str, # first 200 chars of rendered header
"option_count": int, # number of visible (non-hidden) options
"error": str, # exception message if any, else ""
}
Pages reachable only via dynamic OptionOverrideSql callbacks are
discovered when the override SQL runs and returns rows. Pages behind
dynamic callbacks that return no rows at test time will not be discovered.
Use ObjConversationTui for the interactive CLI runner:
python factory.conversation/ObjConversationTui.py test SALESWATCH
python factory.conversation/ObjConversationTui.py test SALESWATCH BENNICK
# Preflight check
python factory.core/ObjConversation.py preflight
# Run a conversation
python factory.core/ObjConversation.py run CODE CONTACT_ID CONTENT --engine console
# Render conversation flow tree
python factory.core/ObjConversation.py flow CODE
# Display conversation summary
python factory.core/ObjConversation.py summary CODE
# List available engines
python factory.core/ObjConversation.py engines
# Save conversation to YAML
python factory.core/ObjConversation.py save CODE filename.yaml
# Load conversation from YAML
python factory.core/ObjConversation.py load filename.yaml [NEW_CODE]
| Module | Relationship |
|---|---|
ServeConversation.py |
FastAPI entry point; calls RecieveMessage() and TraceConversation() |
factory.conversation/ObjConversationTui.py |
Fullscreen TUI tester with chat and test CLI commands |
factory.conversation/ObjConversationConsole.py |
Console channel engine |
factory.conversation/ObjConversationDiscord.py |
Discord channel engine |
factory.conversation/ObjConversationWhatsapp.py |
WhatsApp channel engine |
factory.conversation/ObjConversationSlack.py |
Slack channel engine |
factory.conversation/ObjConversationEngine.py |
Base engine class |
factory.core/ObjData.py |
Database abstraction base class |
cythonize -3 -a -i ObjConversation.py
Compiling factory.core/ObjConversation.py
OptionOverrideSql diagnostics with explicit probe logging for SQL rows/columns and execution errors.Option, OptionDisplay string coercion).OptionContextVar before reroute workflows and making reroute field access casing-tolerant.option_context_value even when OptionContextVar is not configured.option_context_label capture for selected dynamic options so reroute workflows/services can parse display text when direct SQL hydration is unavailable.read_conversation() to skip reroute/route execution when inbound content is empty (prevents page-refresh polling from firing menu workflows)._hydrate_option_context() to always capture dynamic option ContextValue into option_context_value; custom OptionContextVar mapping is now optional rather than required.promo_image_url, promo_status, next_page_target) to conversation params.Updated : 2026-03-10