Tessera accesses NetSuite data through two mechanisms: SuiteQL via the N/query module for aggregate computations, and Saved Searches via N/search for permission-scoped result sets. All data access is mediated by the Query Layer — a TypeScript module comprising a slot resolver and an executor. No query strings are constructed at runtime outside of this layer.
Metric Definition (R1)
│
├── Query Components (R2) ──── Filter/Scope records (R3)
│ │
│ └── Query Template selection
│
▼
Slot Resolver
│ Reads R1/R2/R3 DTOs
│ Selects template
│ Populates parameter slots
│ Returns executable SQL string
│
▼
Query Executor
│ N/query.runSuiteQL() or N/search.run()
│ Returns raw result set
│
▼
Formula Evaluator
│ Applies Formula Pattern
│ math.js for statistical operations
│
▼
Metric Result (R6)
The slot resolver is a TypeScript module that takes Metric Definition, Query Component, and Filter/Scope DTOs and returns a complete, executable SuiteQL string. It does not call any NetSuite APIs — it operates on plain TypeScript objects.
This design means the resolver is fully testable in Node.js without a sandbox. The resolver has 79 unit tests covering all template types, multi-tier routing, period resolution, constant components, sign convention, and duplicate account detection.
The resolver:
MetricDefinitionDTO, array of ComponentDTO, and array of FilterScopeDTOquery_type on R2N/query.runSuiteQL()The executor is the adapter layer that calls NetSuite APIs. It receives the SQL string from the resolver, calls N/query.runSuiteQL() or N/search.run(), and returns a typed result set. The executor enforces subsidiary scope as a post-processing step for raw SuiteQL paths.
Tessera uses parameterized SQL templates — called Query Templates — to produce metric computations. Each template is a SuiteQL skeleton with declared parameter slots. The slot resolver populates the slots at compute time. Template skeletons live in the resolver code; Query Template records (R11) in NetSuite store the template’s metadata and slot manifest.
Adding a new metric that fits an existing template requires no code change — only data configuration.
Use cases: DSO (Days Sales Outstanding), DPO (Days Payable Outstanding), DIO (Days Inventory Outstanding), Cash Conversion Cycle
Pattern: Numerator aggregate ÷ Denominator trailing average × Days-in-window
SuiteQL structure:
SELECT
SUM(CASE WHEN {account_filter} THEN tl.netAmount ELSE 0 END) AS numerator,
SUM(CASE WHEN {denominator_filter} THEN tl.netAmount ELSE 0 END) AS denominator_sum
FROM transaction t
INNER JOIN transactionLine tl ON t.id = tl.transaction
WHERE
tl.mainLine = 'F'
AND tl.taxLine = 'F'
AND t.tranDate BETWEEN {window_start} AND {period_end}
AND tl.subsidiary IN ({subsidiary_list})
AND t.type IN ({transaction_types})
Formula application: (numerator / (denominator_sum / window_days)) * window_days
Validated: NS 26.1 sandbox — confirmed correct results for DSO against manual calculation.
Use cases: Revenue, COGS, Gross Profit, Operating Expense, EBITDA components, any income statement metric bounded by posting period
Pattern: SUM of net amounts where transaction posts within the accounting period
SuiteQL structure:
SELECT
SUM(tl.netAmount) * {sign_multiplier} AS result
FROM transaction t
INNER JOIN transactionLine tl ON t.id = tl.transaction
INNER JOIN accountingPeriod ap ON t.postingPeriod = ap.id
WHERE
tl.mainLine = 'F'
AND tl.taxLine = 'F'
AND tl.account IN ({account_list})
AND ap.startDate >= {period_start}
AND ap.endDate <= {period_end}
AND tl.subsidiary IN ({subsidiary_list})
AND t.type IN ({transaction_types})
Key implementation notes:
accountingPeriod.startDate / accountingPeriod.endDate, not tranDate. This matches how NetSuite’s own financial reports bound period activity.mainLine = 'F' and taxLine = 'F' exclude the transaction header line and tax lines. Both are required to avoid double-counting.sign_multiplier is set per component via the negate flag on R2. Expense accounts carry a natural debit balance; negating produces the conventional positive display value.t.type does not require BUILTIN.CF() wrapping in WHERE clauses.Validated: NS 26.1 sandbox — confirmed against P&L report for Revenue and COGS.
Use cases: Trailing 12-month revenue, rolling average expense, any metric requiring a trailing time window that doesn’t align with accounting periods
Pattern: SUM of net amounts where tranDate falls within a trailing window
SuiteQL structure:
SELECT
SUM(tl.netAmount) * {sign_multiplier} AS result
FROM transaction t
INNER JOIN transactionLine tl ON t.id = tl.transaction
WHERE
tl.mainLine = 'F'
AND tl.taxLine = 'F'
AND tl.account IN ({account_list})
AND t.tranDate BETWEEN {window_start} AND {as_of_date}
AND tl.subsidiary IN ({subsidiary_list})
AND t.type IN ({transaction_types})
Differs from Template 2 in: Uses tranDate rather than posting period. Appropriate for trailing window metrics that should not be recalculated when periods close.
Use cases: Cash and bank balances, Accounts Receivable balance, Accounts Payable balance, any point-in-time stock metric
Pattern: Point-in-time GL balance via accountPeriodActivity
SuiteQL structure:
SELECT
SUM(apa.periodEndBalance) * {sign_multiplier} AS result
FROM accountPeriodActivity apa
INNER JOIN accountingPeriod ap ON apa.period = ap.id
WHERE
apa.account IN ({account_list})
AND ap.endDate <= {as_of_date}
AND ap.isQuarter = 'F'
AND ap.isYear = 'F'
AND apa.subsidiary IN ({subsidiary_list})
Key implementation notes:
accountPeriodActivity (not transactionLine) is the correct table for running balance. It provides pre-aggregated period-end balances maintained by NetSuite’s accounting engine.isQuarter = 'F' and isYear = 'F' restrict to leaf-level accounting periods, avoiding double-counting from summary period rows.accountPeriodActivity and would produce incorrect results.periodEndBalance reflects the balance as of the period end date, incorporating all postings through period close.Validated: NS 26.1 sandbox — accountPeriodActivity confirmed as Tier 1 path; correct at any account size.
Use cases: Net Revenue Retention, Gross Revenue Retention, cohort churn, any metric requiring comparison of two populations of entities across two time periods
Pattern: Two independent SuiteQL queries producing per-entity aggregates; application-layer entity join to compute retention/comparison metric
Rationale for two-query design: SuiteQL does not support window functions (LAG, RANK, PARTITION BY). Cohort comparison requires identifying the same entity in two different periods and computing a ratio. This cannot be expressed as a single SuiteQL query. The two-query model executes both queries, returns two result sets, and performs the entity join in TypeScript.
Template 5 is designed and stubbed. Implementation deferred to v2.
Filter/Scope records externalize the filter logic for each Query Component. Rather than embedding “which accounts qualify” in code, the specification is stored as a child record of R2 and interpreted by the resolver at compute time.
Each R3 record specifies one filter condition:
| Field | Values | Purpose |
|---|---|---|
| Filter type | account, account_type, subsidiary, transaction_type, segment, date_range |
What this filter applies to |
| Operator | IN, NOT_IN, EQUALS, BETWEEN |
Comparison operator |
| Value(s) | List or scalar | The filter values |
The resolver assembles all R3 records for a component and generates the WHERE clause. Adding an account to a metric’s scope does not require a code deployment — only a data record change.
Formula Patterns (R12) define how component values are combined into a metric result. Each Formula Pattern stores a formula string using component role variables (A, B, C…) and is evaluated by math.js.
| Pattern | Formula | Example metric |
|---|---|---|
| Ratio — Trailing Days | (A / (B / days)) * days |
DSO |
| Simple Aggregation | A |
Revenue, COGS |
| Gross Margin % | (A - B) / A * 100 |
Gross Margin % |
| Multi-Component Sum | A + B + C + D + E |
EBITDA |
| Percent Change | (A - B) / ABS(B) * 100 |
Revenue growth % |
| Running Balance | A |
Cash balance |
| Ratio — Calendar Period | A / B |
Expense ratio |
| Weighted Average | (A * B) / C |
Blended rate |
Formula strings are stored in R12 records, not hardcoded. A new formula type can be added without a code change — only a new R12 record.
The following findings were confirmed against a live NetSuite 26.1 sandbox before the query engine was finalized:
| Finding | Status | Impact |
|---|---|---|
accountPeriodActivity table available and fast |
✅ Confirmed | Running Balance uses Tier 1 path |
transactionLine field names are camelCase |
✅ Confirmed | netAmount, mainLine, taxLine, subsidiary |
SuiteQL boolean syntax: 'T' / 'F' strings |
✅ Confirmed | Used throughout WHERE clauses |
tl.subsidiary (not t.subsidiary) |
✅ Confirmed | Subsidiary filter on line, not header |
t.type works without BUILTIN.CF() |
✅ Confirmed | Transaction type filter simplified |
| SuiteQL window functions (LAG, RANK) | ❌ Not supported | Two-query model adopted for Template 5 |
TransactionAccountingLine table (not accountingLine) |
✅ Confirmed | Correct table name for accounting line access |
Statistical/NonPosting accounts in accountPeriodActivity |
❌ Not present | Excluded from Wizard account picker |
| Field name resolution: snake_case | ❌ Fails | Use camelCase or all-lowercase only |
Tessera resolves accounting period boundaries from the accountingPeriod table at compute time. Period resolution handles:
Because SuiteQL does not support window functions, any metric that compares two periods (e.g., revenue this quarter vs. revenue last quarter, or retention of the Q1 cohort measured in Q2) requires two separate queries. The resolver generates both queries; the executor runs them sequentially; the formula evaluator combines the results.
This is not a limitation — it is an architectural decision that produces more predictable execution plans and clearer debugging than a complex self-join or subquery equivalent would.
The DAL abstracts all NetSuite record I/O. Modules in the MDL, CRL, and rendering layer do not call N/record directly — they call DAL methods that return typed DTOs.
The AccountConfig DTO implements a self-load pattern: if no AccountConfig is supplied to a function that requires it, the function calls the DAL to load it from the Account Configuration custom record. This avoids threading the config object through every call stack while keeping the pattern testable (the self-load can be mocked at the DAL interface boundary).