Yearly Resetting Sequential ID Generator - Multi-Prefix Support

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

Design Rationale & Architectural Principle

Hi, I’m Tamás from Tulip.

I designed this component to solve a very specific structural problem: generating business-compliant IDs that include a prefix, include the current year, and automatically reset the counter every new year — without introducing external state management.

I intentionally chose not to store a persistent counter in a table. That approach would have required maintaining counter records, handling concurrency explicitly, and implementing separate yearly reset logic. It would also introduce hidden state that could drift from the actual IDs.

Instead, I made a deliberate architectural decision:

The ID itself is the source of truth.

By parsing the last existing ID and deriving the next value directly from it, the system remains stateless, deterministic, and portable.
At the same time, I follow a clear design principle:

Protected logic. Flexible configuration.

The internal parsing rules, year position, counter width handling, and structural guards are intentionally rigid. They are not meant to be modified casually.
The configuration surface, however, is intentionally flexible:

• Prefix is configurable and defines the sequence scope (each unique prefix maintains its own counter).
• Table ID must be provided in the configuration trigger to define which table the sequence applies to.
• Counter width is configurable.
• Target table is configurable via connector input.

I protect the internal logic and expose only safe parameters. This reduces the risk of accidental structural changes while keeping the component reusable.
I separated responsibilities clearly:

• The connector retrieves the last existing ID scoped by prefix (sequence is prefix-specific).
• The component trigger generates the next ID.
• Guards enforce structural integrity.

This separation keeps the logic predictable and reusable across apps and tables.

I also deliberately chose to use the Tulip API through a connector instead of embedding table-specific logic directly in the component. The reason is operational simplicity: the API token and connector authentication only need to be configured once. After that, the component can be reused across different tables by simply changing the Table ID in the configuration trigger.

This makes the component portable. I do not need to duplicate authentication logic or rebuild data access for every new table. The infrastructure setup happens once; the component remains configurable and lightweight.

Because of this design, the component is easily copyable. A copied instance can generate IDs for a completely different table simply by changing the Table ID in the configuration trigger. No structural changes to the logic are required. Each copy operates independently while sharing the same authentication and architectural principles.

Why This Scales

This design scales because it does not accumulate operational complexity over time.

• There is no persistent counter state that can drift from the actual IDs.
• Yearly reset does not require maintenance or scheduled logic — it is derived directly from structure.
• Authentication is configured once at the connector level.
• New tables require configuration changes only, not logic duplication.

Operationally, this eliminates reconciliation tasks, counter audits, and annual reconfiguration. As long as the structural contract is respected, behavior remains deterministic.

Internal Data Model

I kept the configuration surface intentionally minimal.

Configuration Variables

• _configPrefix (Text)
  Defines the business prefix. I require that any separator (such as a hyphen) is part of this prefix. This keeps parsing deterministic. The prefix also defines the counter scope: different prefixes within the same table result in independent, prefix-specific sequences.
• _configIdNumberFormat (Text)
  Defines the numeric counter width using zero-padding (e.g., "00000000"). The length of this string defines the allowed numeric capacity.
• tableId (Static value in configuration trigger)
  Identifies the target table. This must be explicitly set in the configuration trigger so the connector retrieves the correct last ID for that table. Each component instance defines its own table scope via this parameter.

State Variables

• _stateLastId
  Holds the connector result. This is treated as external input and validated before use.
• _stateOutput
  Holds the generated ID.

Structural Contract

I made the structure strict by design:

{PREFIX}{YYYY}{COUNTER}

Where:

• PREFIX is _configPrefix
• YYYY is always four digits
• COUNTER has fixed width equal to LEN(_configIdNumberFormat)

The logic assumes:

• Year begins exactly at position LEN(prefix)
• Year length is always 4
• Counter immediately follows year

If this contract is violated, generation stops. I chose strictness over flexibility because ID integrity is more important than convenience.
This format must remain consistent within a given table. While different copies of the component may target different tables, each individual table must adhere to a single, consistent ID structure defined by the component configuration.

Core Logic Architecture

Main Generation Logic

The ID is built as:

_configPrefix
+ DATETIMETOTEXT(CurrentDateTime, 'YYYY')
+ IF(
    MID(lastId, LEN(prefix), 4) = CurrentYear,
    IncrementCounter,
    ResetToOne
  )

Why This Structure

I extract the year using MID() instead of regex because:

• The year position is deterministic.
• Prefix length is controlled.
• MID is easier to reason about and faster.

If the extracted year equals the current year, the counter is incremented. If not, the counter resets to 1.

This ensures automatic yearly restart without any external scheduler or maintenance logic.

Counter Increment

RIGHT(
  _configIdNumberFormat
  + TOTEXT(TEXTTOINTEGER(parsedCounter) + 1),
  LEN(_configIdNumberFormat)
)

I intentionally use padding + RIGHT truncation instead of pure numeric width checks. This makes overflow detectable by design.

Failure Handling Strategy

I implemented two explicit guards to ensure that structural violations are detected early and communicated clearly.
Without these guards, the system would either fail later with a generic "ID already exists" error or generate malformed IDs that introduce downstream issues.
If something is wrong, the user should receive a descriptive message immediately.

Guard 1 – Format Enforcement

REGEX_REPLACE(
  LINK(lastId, fallback),
  '^' + prefix + '[0-9]{4}[0-9]{N}$',
  'ok'
)

If the result is not ok, generation stops.
This blocks malformed or manually edited IDs from propagating.
Validation occurs after LINK() fallback because:

• Empty tables should not fail.
• New prefixes should not fail.
• Only structurally invalid stored IDs should block generation.

Guard 2 – Overflow Protection

LEN(nextCounter) > LEN(_configIdNumberFormat)

If the next counter exceeds allowed width, generation stops.
Maximum capacity is effectively:

10^N - 1

Where N = LEN(_configIdNumberFormat).
Silent rollover is not allowed.

Why Hard Stops

• Prevent duplicate IDs.
• Prevent non-monotonic sequences.
• Prevent silent structural corruption.
• Replace generic collision errors with descriptive feedback.

If ID integrity is compromised, it must be visible immediately. That is safer than allowing the system to proceed with questionable data.

Design Boundaries

The following constraints define the safe operating boundaries of this component.

Safe to Change

You can safely change:

• Prefix values
• Counter width
• Target table (via tableId in the configuration trigger)
• User-facing messages

You may also use multiple prefixes within the same table. Each unique prefix will maintain its own independent counter sequence, because the connector scopes the "last ID" lookup by prefix.

Requires Redesign

You must redesign if changing:

• Year position
• Year length
• Counter placement
• Structural regex pattern
• The sequence model (for example, switching from prefix-scoped counters to a single global counter across prefixes)

These are parsing and sequencing invariants. Altering them changes the fundamental behavior of the component

Get Involved

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