build_etl
ChatGPTPRIMARY tool for preparing Yardi Voyager 7S ETL import files. Use this when the user wants to convert/transform an Excel or CSV into a Yardi template, import properties, units, leases, tenants, charges or residents into Yardi, or says "convert this for Yardi", "make this file importable", "prepare our data for Yardi go-live". Call this as soon as the user uploads a workbook — pass the file as base64; do NOT read or map the file yourself and do NOT transcribe any data. The server ingests EVERY sheet, profiles all values, and returns a workbook inventory with status "needs_module_confirmation" plus the protocol questions. Ask those questions in ONE batched message (which ETLs to produce; new_migration or add_property_to_existing), then call build_etl again with session_id, requested_modules (the user's wording, verbatim, one entry per ETL) and migration_mode. NEVER pick modules or a migration mode on the user's behalf — the server refuses to build until the user has confirmed both. Sheets outside the requested ETLs are evidence only and never produce files. When results arrive, present each module's mapping table and four-part summary, report import_ready exactly as returned, show download links as-is, and follow claude_next_action.
detect_import_type
ChatGPT[ADVANCED / STANDALONE — build_etl resolves the module internally; prefer it.] Identify which Yardi import module (units, leases, tenants, charges, residents...) a spreadsheet belongs to. Use this when the user asks what Yardi template their file matches. Must call read_excel first. OPTIONAL — skip this and call map_columns directly with an explicit import_type when the user has already named the target ETL (e.g. "make a property ETL" → import_type="CommProperties").
explain_errors
ChatGPTConvert validation results into a plain-English report that a non-technical user can act on. Use this when the user asks why a Yardi import failed, was rejected, or has errors and warnings. Groups issues by field and rule type, with specific examples and fix instructions. Uses session validation results or an explicit list.
finalize_etl
ChatGPTApplies the user's decisions and rebuilds the affected ETL files server-side. Call only after the user has resolved the open questions surfaced by build_etl: ambiguous-column choices (column_decisions, each with its module when several were requested), sheet choices for modules the server could not place (sheet_choices), or a clarified migration_mode/source_system. Pass each decision as a yardi_field chosen from the options provided, or "skip" — never invent a target field. Present the updated summaries and import_ready exactly as returned, and the download links as-is. Do not modify the files or rename any output column.
find_yardi_join_path
ChatGPTFind how to join two Yardi Voyager tables: BFS over the FK relationship graph (12,908 edges incl. 3,497 declared FKs), preferring confirmed edges and refusing to route through audit columns (hUserCreatedBy etc. all point at PMUSER and would bridge everything). Returns the join steps and a ready ySQL FROM/JOIN snippet.
generate_etl_file
ChatGPT[ADVANCED / STANDALONE — build_etl generates the file internally; prefer it.] Generate a Yardi-ready import .xlsx file from the session data. Use this when the user asks to create or produce the Yardi template/import file once data is mapped and validated. Columns are mapped and ordered according to Yardi field definitions. Returns the file as a base64 string. ALL session data is wiped after this call (privacy guarantee). Call map_columns (and optionally validate_data) first.
get_field_definitions
ChatGPTReturn the field definitions for a Yardi import module (by import_type). Use this when the user asks what fields or columns a Yardi module requires, or what makes a valid units / leases / charges record. Shows field names, data types, required flags, max lengths, descriptions, allowed values, and multi-source aliases (Yardi/MRI/CTI/JDE). Call with no import_type to list all 300+ available modules.
get_migration_chronology
ChatGPTReturn the best-practice upload chronology for a Yardi property migration. Use this when the user asks what order to upload Yardi ETL files, wants a go-live plan, or a data-migration sequence for a new property-management system implementation. Supported property types: "commercial_retail" (36 steps), "residential" (29 steps, US). Supports two migration modes: "new_migration" (full chronology) and "add_property_to_existing" (skips global setup ETLs that already exist). NOTE: This is a best-practice reference, not a hard rule.
get_migration_guidance
ChatGPTHand-holding migration guidance: given the ETL you are about to upload, Use when the user asks what to upload next to Yardi or what prerequisites the current ETL needs. this tells you where it fits in the chronology, what prerequisites need to exist (either uploaded earlier OR already in the system), what comes next, and which Yardi module it belongs to. Supports both Commercial/Retail and Residential property types, and both "new migration" and "adding a property to an existing live system" modes. Use this before every ETL upload to feel confident.
get_module_schema
ChatGPTDebug/introspection only. Returns the field definitions for a module from the KB. NOT required for normal ETL preparation — build_etl handles mapping internally. Do not use this to map columns yourself.
get_yardi_enum_decodes
ChatGPTLook up install-invariant enum/status code decodes (tenant status, GL books, TRANS/PROPERTY iType, the polymorphic ObjectType registry, commercial amendment/option decodes, WO origin codes, sysobjects.xtype, ...). Sourced from the standard UNION-ALL decoder views Yardi ships on every Voyager install, plus values verified against live client data. Check here BEFORE profiling a client DB for enum values. Call with no arguments to list all decoders.
get_yardi_knowledge
ChatGPTRead the curated Yardi Voyager semantic knowledge: naming conventions, known traps (HMYPERSON subtype PKs, polymorphic hRecord/hCode references, audit columns, high-volume tables), core join patterns, enum decodes, and the ySQL constraints/discovery protocol. Read this ONCE at the start of any Yardi querying session — it is small and prevents the classic mistakes.
get_yardi_query_patterns
ChatGPTGet proven ySQL query templates (rent roll, lease expiry, GL balance check, etc.) with Use this when the user asks for a Yardi report, a ySQL query, a rent roll, delinquency or aging analysis, or any property-management data question expressed as SQL. __PLACEHOLDER__ filters. Call with no arguments to list all patterns; pass name for the full SQL. Prefer a pattern over hand-deriving a multi-table join. IMPORTANT: templates are starting points, not schema ground truth — verify every column you copy against get_yardi_table_schema before executing (the schema tool is always authoritative).
get_yardi_report_knowledge
ChatGPTRead the curated Yardi report-writing knowledge: the YSR/SSRS report script format (//Section anatomy, render engines, Columns/Filter grammars, #token# substitution), report SQL idioms (hMy record-class ranges, aging buckets, charge annualization), and the drafting protocol. Read this ONCE at the start of any report-authoring session.
get_yardi_report_patterns
ChatGPTGet sanitized Yardi report script skeletons (columnar grid, directory, aging, invoice, statement of account, FillDocs Word contract, Excel exception, e-mail notification, SSRS) ready to adapt. Call with no arguments to list; pass name for the full script template. Start every new report from the matching skeleton.
get_yardi_table_schema
ChatGPTGet the full column list for a Yardi Voyager table (case-insensitive): every column with data type, nullability, PK/FK flags, FK target, and warnings on polymorphic reference columns. Also returns FK relationships in and out of the table, and any curated knowledge notes that mention it. ALWAYS call this before writing SQL against a table — use the returned columns verbatim; do not trust remembered column names.
map_columns
ChatGPT[ADVANCED / STANDALONE — build_etl does mapping internally; prefer it.] Map spreadsheet column headers to Yardi field names for a specific import_type. Uses exact match, source-system alias lookup (Yardi/MRI/CTI/JDE), and fuzzy matching. Call read_excel first. Skip detect_import_type entirely when the user has already specified the ETL — pass import_type directly to save time.
read_excel
ChatGPT[ADVANCED / STANDALONE — for normal ETL preparation use build_etl, which does this plus mapping, validation, and file generation in one call.] Parse an Excel (.xlsx/.xls) or CSV file and return a column/row preview. Use this when you need to inspect an uploaded spreadsheet before or without a full ETL run. CSVs are parsed natively (no xlsx round-trip needed). Pass the file as a base64-encoded string. The parsed data is held in memory for the rest of this session and cleared after generate_etl_file completes. FAST PATH: if you already know the Yardi import_type (e.g. "CommProperties"), skip detect_import_type and go straight to map_columns — it saves a round-trip.
search_yardi_tables
ChatGPTSearch the Yardi Voyager schema knowledge base for tables by name or description keyword. Covers 9,793 standard (out-of-box) Voyager tables. Returns name, column count, row count (from a reference install; NULL = unknown, 0 = empty), and description. Use this FIRST to find the real table before writing any query — never name a Yardi table from memory; the schema is large and remembered names are frequently wrong.
validate_data
ChatGPT[ADVANCED / STANDALONE — build_etl validates internally; prefer it.] Validate every row of the uploaded file against the rule set defined in the etl_validations table. Use this when the user asks whether a file will import into Yardi cleanly. Returns a structured list of errors and warnings with row numbers so issues can be fixed in the source file. Call map_columns first.