Sequential ID Generator

In this article, one of our library developers, Tamás, walks through the development decisions behind the Sequential ID Generator building block app available in the library.

Design Rationale

Hi, I’m Tamás from Tulip.

I designed this component as a minimal, deterministic ID engine that can be reused across tables and across apps without requiring refactoring. The intent was to make the behavior obvious and inspectable rather than clever or overly abstract. The guiding preference was to make reuse a configuration exercise rather than a code-editing exercise.
A deliberate decision was to keep idNumberFormat as a sequence of zeros (for example, 00000000) rather than introducing a separate length parameter. While it encodes length implicitly, it is immediately legible to builders and keeps the configuration surface small.

Why a Connector is Used to Read the Last ID

The component reads the latest ID via a connector so that the target table can be switched by updating the static table ID value provided as input to the connector. The connector query is configured to:

• Filter records by the provided prefix.
• Sort by _sequenceNumber in descending order.
• Limit the result set to 1.
• Return the id field from the most recent record for that prefix.

This ensures that each prefix within a table maintains its own independent counter stream and that the generator always derives the next value from the latest persisted table state.
This design supports a repeatable pattern:

• Create one button per table that sets the required configuration values.
• Provide the table ID as a static connector input.
• Reuse the same generation logic unchanged.
• Copy/paste the button into another app without modifying the trigger logic, because only configuration varies.

This approach keeps the generator trigger table-agnostic and avoids embedding table-specific references into the ID computation layer.

Authentication Consideration

The connector requires an authentication token. The token must be generated once and stored in the connector authentication settings. This is treated as a deployment prerequisite rather than application logic, keeping the component logic focused on deterministic ID generation.

Architectural Preference: Explicit Separation

Configuration resolves table identity, prefix namespace, and numeric width. Generation performs only deterministic computation. Persistence is handled separately. This explicit separation makes the component easier to review, debug, and safely reuse.

Core Logic Architecture

I chose to implement the ID generator as a single-expression “seed-or-increment” pattern to eliminate branching logic and ensure deterministic behavior. I deliberately avoided splitting first-ID and next-ID logic into separate branches because that typically leads to divergence over time. Instead of separating initial ID creation and incremental generation into different trigger paths, the component treats the previous ID as optional input and resolves both scenarios within one expression.

Architectural Principle

The logic follows three sequential stages:

1. Source Selection (Coalesce Pattern)
   The LINK() function is used as a null-coalescing mechanism. If message.id exists, it is used as the source. If not, a synthetic seed value (prefix + idNumberFormat) is used. This removes the need for explicit IF conditions and guarantees a valid numeric baseline.
2. Numeric Extraction and Increment
   The prefix is removed from the selected source using a regex anchored to the beginning of the string. The remaining numeric portion is converted to an integer and incremented by one.
3. Fixed-Width Formatting (Zero-Padding Strategy)
   The incremented value is appended to the numeric format template and truncated from the right to enforce fixed length. This avoids calculating left-padding lengths and prevents negative substring edge cases.

Implementation Expression

@Variable.prefix
+ right(
    @Variable.idNumberFormat
    + totext(
        texttointeger(
          regex_replace(
            link(@Variable.message.id, @Variable.prefix + @Variable.idNumberFormat),
            "^" + @Variable.prefix,
            ""
          )
        ) + 1
      ),
    len(@Variable.idNumberFormat)
  )

Design Decisions

Single Expression Architecture
Keeping the entire generation logic inside one expression guarantees that ID creation is stateless and reproducible. I intentionally avoided intermediate counter variables or multi-step calculations because they tend to introduce hidden state and synchronization risks.

Prefix as Namespace Boundary
The prefix is treated as a structural namespace separator and never modified during calculation. It defines the logical ID domain and prevents cross-domain numeric collisions.

Template-Driven Width Enforcement
The numeric width is anchored to idNumberFormat. Changing the format template automatically changes the ID width without altering the increment logic.

RIGHT-Based Padding Strategy
Using RIGHT(template + value, len(template)) is intentional. It avoids dynamic left-padding calculations and ensures predictable formatting behavior even when the incremented value grows in length.

Deterministic Behavior

The logic does not depend on trigger order, operator interaction timing, or previously stored counter variables. Given the same input ID and format template, the output will always be the same. This makes the component safe for reuse in distributed or parallel execution environments.

Configuration and Integration Assumptions

1. Two-phase trigger structure (Config → Generate)
   Configuration is resolved in a dedicated trigger (table target, prefix, idNumberFormat). The generation trigger consumes only these resolved parameters and does not embed table-specific constants. This prevents divergence between initial-seed and increment paths and reduces misconfiguration risk.
2. idNumberFormat** as the single source of truth for numeric width**
   The numeric digit count is always derived from LEN(idNumberFormat). No hardcoded widths are used, ensuring the component adapts cleanly when the format template changes.
3. Prefix consistency per table
   Each table may contain multiple prefixes, but each prefix defines its own independent numeric sequence. The connector query filters by prefix and retrieves the latest record for that specific namespace. Prefix mismatch for the selected namespace should be treated as a configuration/data integrity fault, not as an alternate valid branch.
4. Stale state avoidance
   The generate trigger should compute output from the latest connector-provided “last ID” on each invocation, and should not rely on cached counters. Clearing prior output state at trigger start is preferred to avoid leaking stale values into downstream logic.

Constraints

• The prefix must remain stable once IDs are generated.
• Multiple prefixes per table are supported, but each prefix maintains its own independent sequence. The generator derives the next value from the latest record matching the configured prefix; cross-prefix sequencing is intentionally not supported by this design.
• The numeric portion must always follow the prefix.
• In the base implementation, overflow beyond the defined numeric width truncates from the left (wrap behavior). If strict overflow prevention is required, the guard described in the Strict Overflow Guard Implementation section must be enabled.

Internal Data Model

The internal data model reflects a deliberate separation between configuration intent and execution state. This was a conscious architectural choice: configuration should describe what the generator is, while runtime state should describe what the generator is doing at a given moment.

Configuration Scope (_config*)

• _configPrefix – Namespace string prepended to every ID.
• _configIdNumberFormat – Zero-based numeric template defining width (e.g., 00000000).
  These variables are resolved once in a dedicated configuration trigger. They are treated as stable parameters during generation. The preference here was to avoid dynamic reconfiguration during execution, which could introduce non-deterministic behavior.

Runtime Scope (_state*)

• _stateLastId – Connector response containing the latest table record.
• _stateOutput – Newly generated ID.
  Runtime variables are intentionally transient. No persistent counter is stored locally. This avoids drift between application memory and database state and ensures that the database remains the single source of truth.

Data Flow

1. Configuration trigger sets namespace and format.
2. Connector retrieves the last record ID from the table defined as a static input in the connector configuration.
3. Generation trigger derives the next ID from connector output.
4. Create-record trigger persists the generated ID.

This staged flow reflects a preference for explicit sequencing over compact but opaque logic. Each step has a clear responsibility and can be inspected independently during debugging or review.

Failure Handling Strategy

Failure handling in this component favors predictability over automatic correction. I deliberately chose explicit failure over silent mutation, because identifier systems become dangerous when they "fix" structural problems invisibly.

1. Null / Empty Table Handling

If the connector returns no last ID, LINK() falls back to a synthetic seed value (prefix + idNumberFormat). This removes the need for branching logic while still guaranteeing a valid baseline. The decision to embed this directly into the expression was motivated by a desire to keep first-ID generation and increment logic unified.

2. Prefix Mismatch

Each prefix defines its own namespace and independent sequence within a table. If a retrieved ID does not match the configured prefix for the current namespace, this is treated as a structural integrity issue rather than something to "fix" automatically. The component intentionally avoids rewriting or stripping unexpected structures, as that could mask configuration errors.

3. Overflow Behavior

If the incremented numeric value exceeds the defined width, the RIGHT() padding strategy truncates from the left (wrap behavior). This behavior is deterministic and consistent, but not protective. In environments where reuse is unacceptable, explicit overflow validation should be added. The base implementation remains minimal by design.

4. Stale State Prevention

The generator recalculates the next ID from the connector-provided value on every execution. No cached counters are reused. This design decision prioritizes correctness over micro-optimization.

5. Concurrency Consideration

Because generation is based on reading the last ID and then writing a new record, concurrent executions can theoretically produce identical results. The component deliberately does not attempt in-app locking. In higher-concurrency environments, collision protection should be implemented at the database layer (unique constraints or atomic increment mechanisms).
Overall, the failure model is transparent: deterministic behavior, no hidden mutations, and clear responsibility boundaries between configuration, generation, and persistence.

Strict Overflow Guard Implementation

The base implementation allows deterministic wrap behavior when the numeric portion exceeds its configured width. I kept this as the default to preserve simplicity, but made the strict guard available for environments where implicit wrap would be unacceptable.

Guard Condition

Overflow is detected by comparing the configured numeric width with the length of the next incremented numeric value:

LEN(_configIdNumberFormat)
<
LEN(
  TOTEXT(
    TEXTTOINTEGER(
      REGEX_REPLACE(
        LINK(_stateLastId.id, _configPrefix + _configIdNumberFormat),
        '^' + _configPrefix,
        ''
      )
    ) + 1
  )
)

If the incremented value exceeds the allowed width, generation is halted.

Overflow Branch Behavior

When the guard condition evaluates to true:

• _stateOutput is cleared.
• An explicit error message is raised.

The system does not attempt truncation, wrapping, or automatic resizing.

Format Integrity Check

In addition to overflow protection, the component validates that the last ID retrieved from the table matches the configured prefix and numeric width. This prevents silent drift if legacy or manually inserted records violate the expected structure.

Validation Expression

REGEX_REPLACE(
  LINK(_stateLastId.id, _configPrefix + _configIdNumberFormat),
  '^' + _configPrefix + '[0-9]{' + LEN(_configIdNumberFormat) + '}$',
  'ok'
  )

If the expression result does not equal ok, the generation process is halted and an error is raised.

Purpose

• Ensures that the last retrieved ID belongs to the currently configured prefix namespace and matches its exact numeric structure.
• Enforces exact numeric width.
• Blocks generation if unexpected suffixes or malformed IDs are present.

This safeguard converts structural assumptions into explicit runtime validation, improving reliability in shared or long-lived tables.

Get Involved

Join the community to share improvements, propose additional units, report issues, or discuss architectural decisions.