Preserve Numeric-Looking Identifiers Across SQLite Inserts

A parameterized insert can be completely successful and still violate an identifier contract.

Start with D1: the Python value is the plain string 000742. It is bound through sqlite3 as a parameter, the INSERT raises no exception, and the row is present afterward. Yet in the supplied November 3, 2026 commissioning run, an ordinary SQLite column declared ANY reads back as storage class integer with value 742. The arithmetic value survived. The opaque identifier spelling did not.

That distinction matters whenever leading zeros, decimal spelling, exponent notation, or other characters belong to identity rather than presentation. An application that treats 000742, 742, 7.42e2, and 742.0 as four unrelated labels cannot accept a storage boundary that collapses all four to integer 742, even if every statement is parameterized and every numerical comparison looks correct.

This article qualifies that boundary rather than introducing SQLite generally. The controlled population is five ASCII strings plus one deliberately wrong Python integer. Six declarations are exercised, followed by three two-row UNIQUE controls, two recovery controls, and an application-side type gate. The reference environment is CPython 3.12.14 linked to SQLite 3.53.1, using one owned in-memory connection with autocommit=True, ordinary adapters and converters, no network, and no concurrent writer.

SQLite documents the underlying behavior: ordinary tables apply affinity rules, while ANY inside a STRICT table is special because it performs no type coercion. STRICT itself is not a promise that every declared type preserves its incoming storage class.

The operational question is therefore narrow: did the insert preserve the original string and its text storage class, or did write-time affinity erase meaningful identifier spelling?

Define the exact text that an insert must preserve

The acceptance contract must exist before the schema is chosen.

For this fixture, every admitted identifier is required to arrive as a plain Python str. After insertion, SQLite must report typeof(ref) = 'text', and the returned Python string must contain exactly the same characters as the source manifest. Those are two separate conditions: the stored class must be TEXT, and the text must be character-for-character identical.

The five admissible values are:

D6 is intentionally different: Python int value 742. It exists to test whether the writer and database can distinguish “the source supplied the text 742” from “the source supplied an integer that later became plausible text.”

This is deliberately downstream of parsing. The neighboring Refonte Learning article on identifier precision at the JSON boundary addresses information loss while interpreting JSON numbers. Here, every accepted source value is already an intact Python string. No digit has been rounded or reconstructed before SQLite sees it.

Stable row keys D1 through D6 are also part of the experimental design. The tested ref value cannot serve as its own identity oracle because it is precisely the value that may change. A successful statement, numerical equality with 742, and a later call to str() are all weaker evidence than exact comparison against the independent manifest.

Read the table declaration and its affinity rules

SQLite’s ordinary type system associates storage class with values while giving columns an affinity that influences conversion. The documentation says a TEXT-affinity column converts supplied numerical values to text, while NUMERIC affinity converts well-formed numeric text to INTEGER or REAL when possible.

Declared type names determine that affinity in ordinary tables. In particular, an ordinary declaration named ANY does not mean “preserve whatever arrives.” SQLite’s STRICT documentation explicitly contrasts the two cases: ordinary ANY may convert numeric-looking strings, while ANY in a STRICT table preserves the supplied value and storage type.

That gives the fixture five important behaviors to compare:

The last row is a frequent source of overstatement. SQLite documents that STRICT columns other than ANY still try to coerce the incoming value to the declared datatype; a lossless conversion is acceptable, while an impossible lossless conversion produces SQLITE_CONSTRAINT_DATATYPE. STRICT therefore means stronger declared-type enforcement, not “never transform an input.”

STRICT tables were introduced in SQLite 3.37.0 on November 27, 2021, not in this article’s 2026 research batch. Collation and normalization are different questions; the existing Refonte workflow on SQLite Unicode name-equality policies belongs to that boundary, not this one.

Separate storage preservation from input validation

STRICT ANY answers a storage-preservation question: “What type and value did SQLite receive?” Because it applies no coercion, a supplied integer stays an integer.

That is useful evidence, but it does not by itself enforce this application’s rule that identifiers must originate as strings.

STRICT TEXT answers a different question. It can establish that the stored result satisfies a TEXT declaration, but SQLite is allowed to convert a losslessly representable integer to text. A readback of text "742" therefore does not prove that Python originally supplied "742" rather than integer 742.

Those observations are complementary, not contradictory. Storage policy and source-type admission are separate controls.

Freeze the binding environment and the independent manifest

The commissioning evidence is scoped to a particular pipeline: CPython 3.12.14, SQLite 3.53.1, one :memory: database, autocommit=True, ordinary parameter substitution, no custom adapters or converters, and no concurrent writer.

Python’s 3.12 sqlite3 documentation currently identifies itself as the 3.12.16 documentation set, so that documentation version must not be silently substituted for the observed interpreter build. It documents that Python str maps natively to SQLite TEXT and Python int to SQLite INTEGER, and that returned SQLite TEXT values become Python str by default. It also records that the autocommit connection parameter was added in Python 3.12.

The measured interpreter remains 3.12.14. The documentation is the API contract consulted; it is not evidence that the experiment ran on 3.12.16.

SQLite 3.53.1’s published release source ID is 2026-05-05 10:34:17 c88b22011a54b4f6fbd149e9f8e4de77658ce58143a1af0e3785e4e6475127e9. The supplied fixture records sqlite_source_id() as part of its result so a reviewer can compare the executing library rather than relying only on the short version string.

The commissioning record is:

PASS_36_CELLS_3_PAIRS_2_RECOVERY_CONTROLS

Run timestamp supplied for this research batch:

2026-11-03T07:52:18.355169+00:00

Source SHA-256:

baac18d640d9aa37e45fb6397ecf6abb106a205a28b33a6ee7916f50b17b9b76

A digest identifies source bytes. It does not prove execution, upstream authenticity, production correctness, or database integrity.

Four evidence layers should remain explicit throughout the review. The documented contract comes from SQLite and Python documentation. The application policy is authored here: accepted identifiers must be plain strings and must remain exact TEXT. The observations are the supplied November 3 commissioning results. Everything outside that environment (other adapters, triggers, expressions, source grammars, writers, and concurrency arrangements) is an untested extension, not an inferred guarantee.

Keep the row key outside the value being tested

Independent expectations are a database-reliability requirement, not a stylistic preference.

The manifest owns both expected spelling and stable case identity:

D1 -> 000742
D2 -> 742
D3 -> 7.42e2
D4 -> 742.0
D5 -> sensor-7

The candidate table does not get to manufacture its own expected values. If an ordinary ANY insert turns D1 into integer 742, the test must still compare that row with manifest value 000742, not cast the database result to a string and redefine success as "742".

That same separation is useful beyond this fixture: SQL row-grain and validation foundations emphasize knowing what one row identifies before validating results. Here, row_key identifies the experiment case, while ref is the potentially corrupted payload.

Run the complete storage and recovery fixture

Save the following as db_affinity_fixture.py and invoke it with:

python db_affinity_fixture.py > db_affinity_results.json

The program creates only owned in-memory tables and closes the connection in finally. Every SQL value under test is parameterized. Dynamic table names come only from the program’s fixed SCHEMAS inventory, and snap() refuses names outside the explicitly owned set.

"""Observe storage affinity on owned, synthetic SQLite identifier values."""
import hashlib, json, platform, sqlite3
from datetime import datetime, timezone
from pathlib import Path

MANIFEST = [('D1','000742'), ('D2','742'), ('D3','7.42e2'),
            ('D4','742.0'), ('D5','sensor-7')]
SCHEMAS = {
    'ordinary_any': ('ANY', ''), 'ordinary_numeric': ('NUMERIC', ''),
    'ordinary_text': ('TEXT', ''), 'strict_any': ('ANY', ' STRICT'),
    'strict_text': ('TEXT', ' STRICT'),
    'strict_text_guard': ("ANY CHECK(typeof(ref)='text')", ' STRICT'),
}

def require(ok, label):
    if not ok:
        raise AssertionError(label)

def sql(db, statement, params=()):
    cur = db.execute(statement, params)
    try:
        return cur.fetchall()
    finally:
        cur.close()

def snap(db, table):
    require(table in set(SCHEMAS) | {'copied','reloaded','guarded'}, 'owned table')
    return sql(db, f'SELECT row_key, typeof(ref), ref FROM {table} ORDER BY row_key')

def run():
    require(sqlite3.sqlite_version_info >= (3,37,0), 'STRICT support required')
    db = sqlite3.connect(':memory:', autocommit=True)
    report = {'status':'FAIL', 'python':platform.python_version(),
              'sqlite':sqlite3.sqlite_version,
              'source_sha256':hashlib.sha256(Path(__file__).read_bytes()).hexdigest(),
              'started_utc':datetime.now(timezone.utc).isoformat(), 'cells':[]}
    try:
        report['sqlite_source_id'] = sql(db, 'SELECT sqlite_source_id()')[0][0]
        for table,(decl,options) in SCHEMAS.items():
            sql(db, f'CREATE TABLE {table}(row_key TEXT PRIMARY KEY, ref {decl} NOT NULL){options}')
            for row_key,incoming in MANIFEST + [('D6',742)]:
                expected_error = table == 'strict_text_guard' and row_key == 'D6'
                error = None
                try:
                    sql(db, f'INSERT INTO {table} VALUES(?,?)', (row_key,incoming))
                except sqlite3.IntegrityError as exc:
                    error = {'type':type(exc).__name__, 'name':exc.sqlite_errorname}
                observed = sql(db, f'SELECT typeof(ref), ref FROM {table} WHERE row_key=?', (row_key,))
                if expected_error:
                    require(error is not None and error['name'] == 'SQLITE_CONSTRAINT_CHECK', 'type guard rejection')
                    expected = []
                elif table in ('ordinary_any','ordinary_numeric') and row_key != 'D5':
                    expected = [('integer',742)]
                elif row_key == 'D6' and table == 'strict_any':
                    expected = [('integer',742)]
                elif row_key == 'D6':
                    expected = [('text','742')]
                else:
                    expected = [('text',incoming)]
                require(bool(error) == expected_error and observed == expected, table+':'+row_key)
                exact_text = type(incoming) is str and observed == [('text',incoming)]
                report['cells'].append({'schema':table,'case':row_key,
                    'input_python_type':type(incoming).__name__, 'input':incoming,
                    'expected':expected, 'observed':observed, 'error':error,
                    'text_contract':'ACCEPT' if exact_text else 'REJECT'})

        report['unique_pairs'] = []
        for table in ('ordinary_any','strict_any','strict_text'):
            decl,options = SCHEMAS[table]
            sql(db, f'CREATE TABLE pair_{table}(ref {decl} NOT NULL UNIQUE){options}')
            outcomes = []
            for incoming in ['000742','742']:
                try:
                    sql(db, f'INSERT INTO pair_{table} VALUES(?)', (incoming,))
                    outcomes.append('INSERTED')
                except sqlite3.IntegrityError as exc:
                    outcomes.append(exc.sqlite_errorname)
            expected_outcomes = (['INSERTED','SQLITE_CONSTRAINT_UNIQUE']
                                 if table == 'ordinary_any' else ['INSERTED','INSERTED'])
            require(outcomes == expected_outcomes, table+' unique pair')
            report['unique_pairs'].append({'schema':table, 'outcomes':outcomes,
                'rows':sql(db, f'SELECT typeof(ref),ref FROM pair_{table} ORDER BY rowid')})

        expected_text = [(key,'text',value) for key,value in MANIFEST]
        sql(db, 'CREATE TABLE copied(row_key TEXT PRIMARY KEY, ref TEXT NOT NULL) STRICT')
        sql(db, "INSERT INTO copied SELECT row_key,ref FROM ordinary_any WHERE row_key != 'D6'")
        copied = snap(db,'copied')
        require(copied == [(key,'text','742' if key != 'D5' else value)
                           for key,value in MANIFEST], 'copy observes existing converted values')
        require(copied != expected_text, 'copy cannot reconstruct original spelling')
        sql(db, 'CREATE TABLE reloaded(row_key TEXT PRIMARY KEY, ref TEXT NOT NULL) STRICT')
        for row in MANIFEST:
            sql(db, 'INSERT INTO reloaded VALUES(?,?)', row)
        reloaded = snap(db,'reloaded')
        require(reloaded == expected_text, 'trusted manifest replay')
        report['recovery'] = {'expected':expected_text, 'copy_from_converted':copied,
                              'reload_from_manifest':reloaded}

        sql(db, 'CREATE TABLE guarded(row_key TEXT PRIMARY KEY, ref TEXT NOT NULL) STRICT')
        def write_text(row_key, value):
            if type(value) is not str:
                return 'REJECT_INPUT_TYPE'
            sql(db, 'INSERT INTO guarded VALUES(?,?)', (row_key,value))
            return 'INSERTED'
        denied = write_text('D6',742)
        require(denied == 'REJECT_INPUT_TYPE' and snap(db,'guarded') == [], 'reject before SQL')
        for row in MANIFEST:
            require(write_text(*row) == 'INSERTED', 'text writer')
        require(snap(db,'guarded') == expected_text, 'guarded writer preserves authored text')
        report['writer_gate'] = {'numeric_input':denied, 'rows':snap(db,'guarded')}
        require(len(report['cells']) == 36 and len(report['unique_pairs']) == 3, 'case inventory')
        report['status'] = 'PASS_36_CELLS_3_PAIRS_2_RECOVERY_CONTROLS'
        report['finished_utc'] = datetime.now(timezone.utc).isoformat()
        return report
    finally:
        db.close()

if name == '__main__':
    print(json.dumps(run(), indent=2))

Python documents both qmark parameter substitution and the native mapping of str to TEXT and int to INTEGER. The SQLite version gate corresponds to the documented introduction of STRICT syntax in 3.37.0.

The important control-flow property is that success is earned only after the expected matrix, uniqueness cases, recovery cases, and writer gate all pass their require() assertions. report['status'] begins as FAIL and is changed to PASS_36_CELLS_3_PAIRS_2_RECOVERY_CONTROLS only near the end.

That also defines what not to use as run evidence. Shell redirection can create db_affinity_results.json before Python finishes. If an assertion fails before the final print(), the file can exist yet be empty or incomplete. Acceptance therefore requires the process exit status and a complete JSON document with the matching final status, runtime information, source fingerprint, cell inventory, and controls. File existence is not execution proof.

The findings discussed below are the supplied November 3 commissioning observations. They are not represented as a new execution performed for this article. If the source changes (even whitespace that changes its digest), the old fingerprint no longer identifies the revised bytes, and revised behavior must be rerun before it is described as observed.

Observe four spellings become one stored integer

The decisive failure appears in both ordinary ANY and ordinary NUMERIC.

SQLite says NUMERIC affinity attempts to convert well-formed numeric text into INTEGER or REAL, preferring INTEGER when appropriate. It also documents the special ordinary-table behavior of ANY: numeric-looking strings can be converted rather than preserved.

The supplied run observed:

D1 is the obvious spelling loss: three leading zeros disappear. D3 loses exponent notation. D4 loses its decimal point and trailing zero. All become the same integer.

D2 deserves equal attention. Someone reviewing values only after calling str() might say nothing changed because integer 742 renders as "742". But the declared contract requires both exact spelling and TEXT storage class. D2 therefore fails too: its SQLite storage class is integer, not text.

This is not a floating-point precision demonstration. Every selected numeric result in D1–D4 is the small exact integer 742. No conclusion here depends on binary floating-point rounding, unsafe integer width, or JSON number parsing. The controlled failure is affinity-mediated identity loss at the SQLite write boundary.

The source strings remain part of the evidence after failure. Once "7.42e2" becomes integer 742, deleting the original expectation would erase precisely the information needed to show what went wrong.

Compare the text-preserving candidates on the same inputs

For D1 through D5, four declarations in the fixture preserve the authored strings:

SQLite’s published rules support those observations: TEXT affinity stores numerical inputs as text, while STRICT ANY preserves the supplied type and value without coercion.

The comparison does not establish one universally superior declaration.

If the application’s actual domain is “this field is text and only text,” a TEXT column combined with a writer-side input-type gate is straightforward: the schema expresses the intended stored domain, while the application refuses a nontext source before SQLite can make it look acceptable.

STRICT ANY serves a different purpose. It is attractive when preserving the exact supplied SQLite storage class is itself important, because text remains text and integers remain integers. That makes it highly diagnostic at type boundaries. It does not, however, encode “identifier inputs must be strings.”

Likewise, the CHECK-based STRICT ANY variant is useful when the database should reject non-TEXT storage classes under the tested path. It carries a stronger SQL-side storage-class rule but still does not establish provenance or a complete identifier grammar.

The declaration must therefore follow the intended policy, not keyword preference. “STRICT” is valuable, but it should not become shorthand for “raw source type is always preserved.” SQLite explicitly limits no-coercion behavior to ANY in a STRICT table.

Reject a numeric bind before it becomes plausible text

D6 is where storage preservation and admission policy separate cleanly.

The supplied value is Python integer 742, not string "742". Python’s sqlite3 native type mapping sends an int as SQLite INTEGER. What happens next depends on the column:

The two TEXT declarations produce a result that looks perfectly plausible to a downstream reader: "742". Yet that observation cannot establish that a valid string entered the API.

The fixture therefore adds an explicit application gate:

def write_text(row_key, value):
    if type(value) is not str:
        return 'REJECT_INPUT_TYPE'
    sql(db, 'INSERT INTO guarded VALUES(?,?)', (row_key,value))
    return 'INSERTED'

When called with D6, this function returns REJECT_INPUT_TYPE and executes no insert. The subsequent snapshot of guarded is empty. Only after that negative control succeeds does the fixture submit D1–D5, after which all five rows read back as the expected TEXT strings.

That is stronger evidence for the authored admission rule because the invalid Python type is stopped before any database affinity can normalize it into a superficially acceptable representation.

The gate is intentionally narrow. type(value) is str answers one exact question about this fixture. It is not a complete identifier grammar, a general validation framework, or evidence of source provenance.

Do not infer the original bind type from a TEXT result

A stored TEXT value answers a storage question. It cannot necessarily answer a historical input question.

For example, both of these paths can yield TEXT "742":

Python str "742" -> TEXT column -> SQLite text "742"
Python int 742   -> TEXT column -> SQLite text "742"

SQLite documents TEXT affinity’s conversion of numerical values to text, including in the broader affinity model, and STRICT documentation confirms that non-ANY STRICT columns still attempt lossless coercion.

A database CHECK that sees only the post-affinity value therefore cannot magically reconstruct the pre-conversion Python type in a TEXT column.

This laboratory injects one wrong type: integer 742. It does not qualify bool, float, bytes, None, custom string subclasses, custom adapters, or alternative identifier grammars. Those are separate populations requiring separate tests.

Test a SQL storage-class guard without overclaiming it

The sixth main-matrix declaration is intentionally different:

ref ANY CHECK(typeof(ref)='text') NOT NULL

and the table is declared STRICT.

The choice of ANY matters. SQLite documents that no type coercion occurs for ANY columns in STRICT tables. Consequently, when D6 arrives as integer 742, it remains INTEGER as the CHECK expression is evaluated rather than first being converted to text.

The authored CHECK then asks the storage-class question directly:

typeof(ref) = 'text'

SQLite documents that CHECK constraints are evaluated when rows are inserted or updated and that a false result causes a constraint violation.

The supplied commissioning observation is therefore two-part evidence:

D6 exception name: SQLITE_CONSTRAINT_CHECK
D6 row after attempt: absent

D1 through D5, by contrast, are admitted and remain their original text spellings.

That makes this declaration a useful database-side guard for the tested pipeline. It certifies that the candidate value reaching this STRICT ANY column has SQLite storage class TEXT and satisfies the CHECK at write time. It does not certify that an upstream file is authoritative, that every possible identifier spelling is valid, or that no earlier system altered the string before the Python process received it.

It also does not repair already converted rows. A CHECK prevents an unacceptable write under its enforcement conditions; it does not recreate missing characters from historic data.

The application gate and SQL CHECK should therefore remain conceptually separate. The application gate enforces the Python-side source-type policy before SQL. The SQL check enforces a database storage-class invariant on the value that reaches the column.

Explain the uniqueness failure without discarding the input

The fixture contains only three measured uniqueness controls, each inserting the same two source strings in order:

"000742"
"742"

The results are:

SQLite documents that ordinary ANY may convert numeric-looking text, while STRICT ANY preserves values as supplied. It also documents that UNIQUE constrains stored column values.

The ordinary ANY result therefore follows directly from the chosen storage policy: "000742" becomes integer 742; the second source string "742" also becomes integer 742; the uniqueness constraint sees a duplicate stored value and rejects the second insert.

The second source record must remain visible in the evidence as a rejected attempt. It should not be labeled a replay, silently dropped, or merged into the first row merely because affinity made the stored representations equal.

No corresponding UNIQUE experiment was run for the separate NUMERIC table, so the article does not promote a NUMERIC uniqueness outcome from expectation to observation.

Distinguish a valid constraint from the wrong identity policy

Nothing in this result implies that UNIQUE malfunctioned.

SQLite correctly enforced uniqueness over the values presented to its constraint machinery. The defect is in the mismatch between that storage representation and this application’s identity contract. The application says 000742 and 742 are different opaque labels. Ordinary ANY’s conversion makes them the same stored integer before uniqueness is evaluated.

That is a schema-policy failure, not a Unicode comparison problem and not evidence that uniqueness should be disabled.

Nor should it be “fixed” with INSERT OR IGNORE or REPLACE. Those statements alter conflict handling and introduce different operational questions. For this experiment, ordinary INSERT plus explicit exception capture preserves the rejected attempt as evidence.

Publish one evidence row for every attempted input

A reliable acceptance review should be reconcilable at the level of attempted inputs, not summarized only as “the script passed.”

For each of the 36 schema/input cells, the ledger should retain:

• schema name and table mode;

• stable case key D1–D6;

• original Python type;

• original input value;

• expected storage class/value or named rejection;

• observed storage class/value;

• actual exception, if any;

• application decision.

That accounting matters because the main matrix contains 36 attempts but 35 inserted rows. The missing row is D6 in the STRICT ANY-plus-CHECK table, and its absence is an expected, explicitly observed rejection. Without the attempted-input ledger, “35 rows exist” could be mistaken for one unexplained dropped record.

The fixture keeps that event visible through both the captured SQLITE_CONSTRAINT_CHECK name and the empty row lookup.

The same discipline is why the three uniqueness controls should remain a separate evidence population. They contain six attempted inserts and answer a question about collision behavior after storage conversion; they are not 42 more “main matrix cells.” The copy/reload recovery checks form yet another population.

That separation prevents attractive but meaningless aggregate numbers.

The neighboring Refonte article on reconciling inputs skipped by conflict policies addresses a different failure mode in which conflict handling can make inputs disappear silently. This fixture deliberately avoids that path: ordinary inserts make uniqueness and CHECK failures explicit.

A commissioning status such as PASS_36_CELLS_3_PAIRS_2_RECOVERY_CONTROLS should therefore be read literally: all authored expected good and bad behaviors were observed for those bounded populations. It does not mean every schema tested is suitable for the target text contract. Ordinary ANY passes the diagnostic precisely because the diagnostic correctly observes its expected failure.

Recover from original text instead of copying converted values

Once affinity has erased spelling, changing the destination column type cannot manufacture the missing characters.

The fixture demonstrates that with two recovery controls.

First it creates a STRICT TEXT table named copied and copies D1–D5 from ordinary_any:

INSERT INTO copied
SELECT row_key, ref
FROM ordinary_any
WHERE row_key != 'D6';

By then D1–D4 have already become integer 742. The TEXT target legitimately converts those integers into text, producing:

The target is now type-correct, but three meaningful original spellings are still missing. D1 does not regain 000742; D3 does not regain 7.42e2; D4 does not regain 742.0.

The second control creates another STRICT TEXT table, reloaded, and writes again from the independently retained MANIFEST. That produces the expected five exact strings.

The difference is fundamental. Copying preserves the current converted representation. Reloading from an authoritative source can restore the original representation because the source still contains information the database lost.

Trying to infer zeros, decimal points, or exponent notation from integer 742 would be fabrication. Many original spellings map to that same numerical value.

This differs from SQLite saved-reference checks after maintenance, where the issue is whether a saved row handle still identifies the same record. Here, stable D-keys still identify the rows correctly; the payload’s spelling has been transformed.

Keep source replay separate from a production migration

The manifest is trusted by construction for this fixture. That is enough to demonstrate the recovery principle but not enough to claim a production migration process.

No backup restore, live cutover, rollback, writer drain, provenance investigation, or concurrent migration was executed. The experiment also does not prove that a real external source is complete or authentic.

The operational rule is therefore conservative:

RELOAD only when an approved source still carries the original exact text.

If the database contains only converted integer 742 and no authoritative evidence can distinguish 000742, 742, 7.42e2, or 742.0, the correct decision is HOLD. A new TEXT declaration can make future storage conformant, but it cannot reconstruct information that was never retained.

Make the schema and writer change pass a bounded gate

The acceptance decision should be explicit enough that another engineer can disagree with it for a specific reason.

For this application’s stated contract, ordinary ANY and ordinary NUMERIC are REPAIR outcomes because D1–D4 fail exact text preservation.

A candidate path should not be accepted merely because all SQL statements return success. At minimum the gate needs complete attempted-input inventory, typeof() readback, exact value comparison against independent expectations, a visible negative test for nontext input, and the expected two-row uniqueness behavior for whichever schema is being qualified.

The independent expected manifest must remain unchanged while a candidate is tested. Replacing 000742 with observed 742 after a failure would not repair the system; it would rewrite the oracle to agree with corruption.

For a deliberately text-only writer, the supplied evidence supports a practical candidate consisting of a TEXT column plus a pre-bind Python string gate. The fixture’s STRICT TEXT target preserves all five admitted strings, while write_text() rejects D6 before SQL. A database-side STRICT ANY plus CHECK(typeof(ref)='text') is another bounded control with different semantics.

The right choice depends on whether the system wants a text domain or wants exact heterogeneous storage types. What the experiment rules out is treating ordinary ANY as neutral storage for numeric-looking opaque strings.

Assign the input, schema and recovery owners

A robust boundary needs named ownership because three different teams can otherwise validate three different questions and all believe the identifier is safe.

The source owner defines what the identifier means. That owner determines whether leading zeros, exponent notation, punctuation, or decimal spelling are significant and owns the authoritative original representation. If 000742 and 742 are different identities, that rule must exist outside the candidate database.

The writer owner controls the Python values reaching sqlite3. That includes the explicit source-type gate, parameter binding, and any adapters or transformation logic introduced before execute(). Python documents that native str and int values bind as TEXT and INTEGER respectively, but the API also supports extensibility through adapters and converters. Changing that layer changes the qualification boundary.

The database owner controls DDL, affinity, STRICT mode, CHECK and UNIQUE constraints, and database-side expressions. That owner should be able to explain why the selected declaration represents the application policy rather than merely passing familiar examples.

A review record for the change should capture the candidate DDL, Python and SQLite versions, sqlite_source_id(), fixture source hash, test timestamp, input manifest revision, every failed case, selected remediation, unresolved inputs, and the person authorized to make the ACCEPT/REPAIR/RELOAD/HOLD decision.

The source digest is valuable evidence of fixture identity, but its meaning should remain narrow. SHA-256 can tell reviewers that two files contain the same bytes; it does not prove that the manifest corresponds to an authoritative business system, that the executable was invoked as claimed, or that a production database has no unrelated faults.

The same restraint applies to rollback. This laboratory demonstrates no production migration and therefore no production rollback. Any deployment that changes existing schema or writer behavior needs a separately designed change procedure appropriate to its environment.

The requalification trigger should also be written down rather than left to memory. If a source parser begins producing numbers, an SQL expression performs a cast, an adapter changes bound types, a trigger rewrites ref, or the identifier grammar expands, the old acceptance result no longer covers the new transformation boundary.

Requalify every conversion boundary that changes

The useful unit of acceptance is the complete path, not the declaration in isolation.

Consider several seemingly small changes:

source string -> Python parser -> custom adapter -> bound parameter
bound parameter -> SQL CAST -> target column
target column -> trigger expression -> replacement value

Any one of them can introduce a transformation that was absent from this fixture. SQLite itself notes that values bound as parameters carry storage classes and may be converted during execution according to affinity.

Accordingly, ACCEPT means “this declared pipeline and tested population met its authored contract,” not “SQLite can never alter this identifier.”

The limitations are material. The population contains small synthetic ASCII labels only. It does not qualify Unicode collation, large-integer precision, external encodings, custom adapters, functions, triggers, update paths, ORM mappings, or concurrent writers. STRICT ANY also preserves an integer if an integer is supplied, so it is not a text-only source contract. The UNIQUE control is evidence about coercion and stored identity, not a conflict-resolution playbook.

Independent review of the supplied evidence confirmed the 36 cells, three UNIQUE pairs, and two recovery controls without rerunning them. That review did not execute a migration transaction, backup restore, provenance audit, or rollback. Those omissions belong in the acceptance record rather than being implied away by a green fixture.

Build broader database-integrity practice with Refonte Learning

For this bounded problem, the operational conclusion is clear.

Use ACCEPT only for a candidate that preserves every admitted source string as exact TEXT and enforces the stated nontext-input policy. Use REPAIR when the schema or writer changes storage class or spelling. Use RELOAD when authoritative original text still exists and can repopulate affected records. Use HOLD when the only remaining evidence is already-converted data or when the runtime and qualification scope cannot be established.

The supplied experiment demonstrates why that discipline is necessary: a correctly parameterized string such as 000742 can be stored by ordinary ANY as integer 742; copying that value later into TEXT produces "742", not the lost original. SQLite’s documented STRICT ANY behavior offers a way to preserve supplied storage type, while a deliberate application gate can enforce the separate rule that this identifier must arrive as a string.

Engineers who want to develop the wider database-design and operational disciplines around that kind of decision can review Refonte Learning’s Database Administrator Essentials program. Its current page describes a three-month format at 12–14 hours per week and lists database design and architecture, SQL query optimization, backup and recovery, performance tuning, security, cloud database management, data migration and integration, and monitoring and maintenance among the curriculum areas. It also states that successful completion provides a Training Certificate and Certificate of Internship; the page describes internship access as a potential benefit rather than establishing a guaranteed placement.

Those are broader database-administration foundations. The program page does not establish that SQLite, sqlite3, STRICT ANY, or this exact fixture is part of the taught syllabus. The useful connection is the engineering discipline itself: define identity before migration, make conversion boundaries visible, retain independent evidence, and accept a storage design only when its measured behavior matches the policy it is supposed to enforce.