Back to all articlesAPI integration planning

Set list-query rules so connected teams retrieve the same records

Alactic Editorial8 min read

Prepared with AI assistance by Alactic Editorial. Examples identified as hypothetical are illustrative, not customer results.

Two teams request “open records created yesterday” from the same API and receive different lists. Neither request necessarily failed. They may disagree about filter grouping, archive defaults, date boundaries, or ordering.

The fix is a written API filtering and sorting contract: a shared definition of eligible records, supported expressions, defaults, and sequence. Test it against a frozen dataset with expected results agreed before implementation.

The filled worksheet below is a fictional worked example. All identifiers, timestamps, requests, and results are hypothetical; the syntax describes a recommended design, not an existing Alactic API.

1. Agree on what must remain identical

Start with one acceptance statement: given the same stored data, authorised identity, workspace, query, and contract version, connected teams should retrieve the same eligible records in the same order.

Separate two questions:

  • Membership: Which records qualify?
  • Ordering: In what sequence do qualifying records appear?

A sorting disagreement should not become a membership fix. Broadening a filter to recover a missing record can conceal an archive-state or permission difference.

Use a frozen fixture first. Live records can change status, owner, or permissions between requests. This contract covers selection and ordering; page traversal, cursor storage, dashboard definitions, and business metrics need separate decisions.

2. Write membership and parameter rules together

A parameter catalogue is insufficient. “Supports status and owner” does not explain whether conditions intersect, whether repeated values broaden selection, or whether an empty value removes a restriction.

In this hypothetical worksheet, two connected teams agree on the following contract. Copy its structure, then replace its choices with behaviours your actual API supports.

Filled worksheet: scope and filters

Resource: shared records
Contract version: query-v1
Comparison identity: service-team-eastwest
Workspace: demonstration-workspace
Evaluation: stored values at request evaluation time

Base eligibility:
- Requested workspace only.
- Requesting identity must be authorised to read each record.
- Deleted records always excluded.

Filters:
status         Exact enum: open or won; may repeat.
region         Exact enum: east or west; may repeat.
owner          Exact, case-sensitive owner ID.
owner_missing  Only accepted value: true; selects absent owners.
created_from   Inclusive lower bound on created_at.
created_to     Exclusive upper bound on created_at.
archive        exclude, include, or only; default exclude.
sort           Ordering selector, defined below.

Combination and repetition:
- Different filter names combine with AND.
- Repeated status values combine with OR.
- Repeated region values combine with OR.
- Identical status or region duplicates have no extra effect.
- Every other parameter is single-valued: owner, owner_missing,
  created_from, created_to, archive, and sort.
- Repeated single-valued parameters fail, even if values match.
- Validate repetitions before any parser collapses duplicates.
- No arbitrary OR groups, fuzzy matching, or implicit negation.

Omission and empty values:
- Omitted optional field filters add no restriction; base
  eligibility and documented defaults still apply.
- Omitted archive is equivalent to archive=exclude.
- Empty values are invalid, not equivalent to omission.
- Literal null is not accepted as a filter value.
- owner and owner_missing cannot be combined.

Thus status=open&status=won&region=east means “open or won, and east.” It does not mean “open, or won and east.” Duplicate handling is equally deliberate: repeating status=open is harmless, but repeating owner=ana is invalid.

This distinction prevents one component from taking the first scalar value while another takes the last. Validation needs access to the parameter occurrences, not just a map that has already discarded them.

3. Define time boundaries and deterministic ordering

“Created on June 1” needs a field, timezone rule, and endpoint semantics. This fictional contract compares timestamp instants, not timestamp strings, and rejects inputs that require guessing a timezone.

Filled worksheet: dates and order

Date field: created_at
Input:
- Require Z or an explicit numeric UTC offset.
- Reject date-only and offset-free timestamps.
- Accept fractional seconds up to three digits.
- Reject finer-than-millisecond precision; do not round it.
- Convert accepted offsets to equivalent UTC instants.
- Either bound may be omitted.
- With both bounds, created_from must precede created_to.

Interval: created_from <= created_at < created_to

Sorting:
- Default: sort=-created_at,id
- Accepted values:
  -created_at,id  newest first, then ID ascending
  created_at,id   oldest first, then ID ascending
- IDs use case-sensitive lexical order for ties.
- Other expressions are unsupported.
- Eligible records have non-null created_at and ID.

The half-open interval includes the starting instant and excludes the ending instant. Adjacent intervals can meet without both selecting the boundary record. For a local calendar day, callers must calculate both timezone-aware boundaries rather than assuming a fixed day length.

The ID tie-breaker resolves equal timestamps. It does not freeze changing data: deterministic sorting cannot make separate requests identical if records become eligible between them.

4. Build a fixture that exposes disagreements

This hypothetical fixture includes exact boundaries, tied timestamps, an absent owner, and excluded states. All records belong to the demonstration workspace; timestamps use UTC.

ID   created_at                 status region owner archived deleted readable
R01  2026-05-31T23:59:59.999Z   open   east   ana   no       no      yes
R02  2026-06-01T00:00:00.000Z   open   east   ana   no       no      yes
R03  2026-06-01T12:00:00.000Z   won    west   ben   no       no      yes
R04  2026-06-01T12:00:00.000Z   open   west   none  no       no      yes
R05  2026-06-02T00:00:00.000Z   open   east   ana   no       no      yes
R06  2026-06-01T10:00:00.000Z   open   east   ana   yes      no      yes
R07  2026-06-01T11:00:00.000Z   open   east   ana   no       yes     yes
R08  2026-06-01T09:00:00.000Z   open   east   ana   no       no      no

none denotes an absent owner, not a literal owner ID. Readability applies to the comparison identity. Each expected sequence below also defines the expected membership set; compare both rather than checking counts alone.

Filled acceptance artifact: valid requests

A. No parameters
Sequence: R05, R03, R04, R02, R01
Checks: archive defaults and descending order.

B. status=open
Sequence: R05, R04, R02, R01
Checks: exact status match.

C. status=open&region=east
Sequence: R05, R02, R01
Checks: different filters intersect.

D. status=open&status=won&region=west
Sequence: R03, R04
Checks: OR within status, AND with region.

E. created_from=2026-06-01T00:00:00.000Z
   &created_to=2026-06-02T00:00:00.000Z
Sequence: R03, R04, R02
Checks: lower boundary included; upper boundary excluded.

F. E bounds plus archive=include
Sequence: R03, R04, R06, R02
Checks: archive inclusion does not restore R07 or R08.

G. E bounds plus archive=only
Sequence: R06
Checks: archived-only selection.

H. owner_missing=true
Sequence: R04
Checks: absent owner differs from omitted owner filter.

I. owner=ana&region=west
Sequence: empty
Checks: valid intersection with no matches.

J. E bounds plus sort=created_at,id
Sequence: R02, R03, R04
Checks: ascending order with ID tie-breaker.

K. created_from=2026-06-01T02:00:00.000+02:00
   &created_to=2026-06-02T02:00:00.000+02:00
Sequence: R03, R04, R02
Checks: same instants as E produce exactly E's sequence.

L. archive=exclude
Sequence: R05, R03, R04, R02, R01
Checks: explicit exclusion equals omission in A.

M. status=open&status=open
Sequence: R05, R04, R02, R01
Checks: identical repeatable values equal B.

Wrapped requests are single logical queries. For actual transport, encode literal plus signs in offset values as %2B so a query decoder does not interpret them as spaces. Case K tests instant equivalence, not textual equality: its local timestamp text differs from E, but both bounds represent the same moments.

5. Reject ambiguity instead of returning a broader list

The hypothetical contract validates the whole request before selection. Invalid input returns HTTP 400, a stable error code, relevant parameter names, and a correction message—not a partial or unfiltered result list.

Filled acceptance artifact: invalid requests

N. owner=ana&owner_missing=true
Error: conflicting_filters

O. owner=ana&owner=ben
Error: repeated_scalar

P. owner=ana&owner=ana
Error: repeated_scalar

Q. created_from=2026-06-01
Error: invalid_timestamp

R. created_from=2026-06-01T00:00:00.000
Error: invalid_timestamp (missing offset)

S. created_from=2026-06-01T00:00:00.0001Z
Error: invalid_timestamp (precision too fine)

T. created_from=2026-06-02T00:00:00.000Z
   &created_to=2026-06-01T00:00:00.000Z
Error: invalid_range

U. Equal created_from and created_to instants
Error: invalid_range

V. created_from=2026-06-01T00:00:00.000Z
   &created_from=2026-06-02T00:00:00.000Z
Error: repeated_scalar

W. created_to=2026-06-02T00:00:00.000Z
   &created_to=2026-06-02T00:00:00.000Z
Error: repeated_scalar

X. sort=created_at,id&sort=-created_at,id
Error: repeated_scalar

Y. sort=created_at,id&sort=created_at,id
Error: repeated_scalar

Z. owner_missing=true&owner_missing=true
Error: repeated_scalar

AA. archive=include&archive=only
Error: repeated_scalar

AB. status=
Error: empty_value

AC. status=closed
Error: invalid_enum

AD. owner=null
Error: invalid_value

AE. region=east,west
Error: invalid_enum

AF. sort=-status
Error: unsupported_sort

AG. Unknown parameter or attempted nested-expression parameter
Error: unknown_parameter

Cases V and W contrast different repeated date bounds with identical repeated bounds; both fail. Cases X and Y do the same for sort. No parser may select a preferred occurrence. For requests containing multiple faults, this proposed contract reports all detected validation errors without returning records.

Keep valid emptiness separate from invalid input. Case I succeeds with no matches; case N fails validation. Treating both as empty searches hides integration mistakes.

6. Keep access scope separate from filters

An archive option changes archive membership, not permissions. In this worksheet, archive=include cannot restore deleted records or reveal unreadable ones.

OWASP’s authorisation guidance recommends least privilege, denial by default, and permission checks on every request. These are design recommendations, not evidence about a particular product.

Document three logical layers: workspace and identity establish access scope; lifecycle rules govern deletion and archive defaults; requested field conditions narrow the remaining records. These layers do not prescribe database execution order, but a filter must never widen access.

Debug permission differences in an authorised test environment. Ordinary query responses should not expose excluded records merely to explain a membership mismatch.

7. Diagnose differences without weakening the query

Capture raw requests and client-applied defaults. A screen label such as “all records” cannot reconstruct the selection.

  1. Match workspace, identity, contract version, and fixture revision.
  2. Compare record IDs to isolate membership differences.
  3. Check grouping, repetition, date conversion, archive state, deletion, and permissions.
  4. Only after membership matches, compare sort direction and tie-breaking.
  5. Add each discovered disagreement as a regression case.

If the server cannot express the agreed selection, do not silently discard a condition. Block the unsupported choice, explain the limitation, or explicitly revise the integration design. Client-side filtering can imply completeness even when the client has not retrieved all eligible records.

Changing a default can affect consumers that send no new parameters. Identify those consumers and agree on migration timing before replacing the contract.

8. Use the worksheet as an acceptance gate

Assign an integration owner and a reviewer from each connected team. Replace fictional fields with supported ones, retain the boundary cases, and approve expected results before comparing implementations.

Run both clients against the same fixture. Record membership, sequence, and validation outcomes separately for every case. A failure in one dimension should not disappear into an aggregate success score.

The completed artifact is the shared result sheet: request, expected outcome, actual outcome, and reviewer decision. Acceptance means every agreed case matches, including offset equivalence, repeated scalar rejection, and archive defaults—not merely that both clients return plausible lists.

API integrationQuery contractsData qualityAcceptance testing

Put the next conversation to work.

Explore Alactic Communications for WhatsApp conversations, campaigns and AI-assisted workflows. Choose the product and plan that fit your team.

Explore Communications