piccolo: Unexpected changes to ID values during object creation in sqlite

I am observing a situation where a BigInt primary key that is not defined to auto-increment results in a different value than what I am passing in to be saved.

The issue may be sqlite specific. I have not tried this with postgres yet.

Schema

The trends field should not matter here, just including it for completeness in how I am current setup:

class Tweet(Table, tablename="tweets"):

    tweet_id: int = BigInt(primary_key=True)
    data: dict = JSON()
    trends = M2M(LazyTableReference("TrendTweet", module_path=__name__))

Migration

async def forwards():
    manager = MigrationManager(
        migration_id=ID,
        app_name="myapp",
        description=DESCRIPTION,
    )

    manager.add_table("Tweet", tablename="tweets")

    manager.add_column(
        table_class_name="Tweet",
        tablename="tweets",
        column_name="tweet_id",
        db_column_name="tweet_id",
        column_class_name="BigInt",
        column_class=BigInt,
        params={
            "default": None,
            "null": False,
            "primary_key": True,
            "unique": True,
            "index": True,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

    manager.add_column(
        table_class_name="Tweet",
        tablename="tweets",
        column_name="data",
        db_column_name="data",
        column_class_name="JSON",
        column_class=JSON,
        params={
            "default": "{}",
            "null": False,
            "primary_key": False,
            "unique": False,
            "index": False,
            "index_method": IndexMethod.btree,
            "choices": None,
            "db_column_name": None,
            "secret": False,
        },
    )

The resulting sqlite schema

which looks correct to me:

CREATE TABLE tweets ("tweet_id" INTEGER PRIMARY KEY UNIQUE NOT NULL DEFAULT null, "data" JSON NOT NULL DEFAULT '{}');
CREATE INDEX tweets_tweet_id ON tweets ("tweet_id");

Example code snippet

        for _tweet in tweets: # tweets is a list of response objects from the twitter API
            print("GET OR CREATE:", _tweet.id)
            tweet = Tweet.objects().get_or_create( Tweet.tweet_id == _tweet.id,
                defaults={ "data": _tweet }).run_sync()
            print("TWEET OBJ ID ", tweet.tweet_id)
            print("ORIG TWEET ID", _tweet["id"])

Output showing the problem

GET OR CREATE: 1587206613131317250
TWEET OBJ ID  1587206613131317248
ORIG TWEET ID 1587206613131317250

The issue does not seem to be specific to get_or_create. I have also used create:

            tweet = Tweet.objects().where( Tweet.tweet_id == _tweet.id).first().run_sync()
            if tweet is None:
                tweet = Tweet.objects().create (tweet_id=_tweet.id, data=_tweet).run_sync()

and with save …

            tweet = Tweet.objects().where( Tweet.tweet_id == _tweet.id).first().run_sync()
            if tweet is None:
                tweet = Tweet(tweet_id=_tweet.id, data=_tweet)
                tweet.save().run_sync()

I see the same problem with these other approaches.

A particularly odd thing is that it is not consistent. Sometimes my loop runs for as many as 3 or 4 tweets, meaning the ID that results is correct in those cases. The resulting tweet_id on the created object seems to be anywhere from n+0 to n+3 where n is the original ID.

I am very confused by this. I know the above does not really isolate the problem in a replicable way, but maybe you have a theory about what might be happening?