Feat: Better default value handling just like Notion

[git-init-priyanshu]

Scope & Context

I really like how notion handles the default value. For example:

1. I create a new property status in my database in notion. Not started is the default value.:

2. If I change the default value to In progress, all rows that were still using the default value automatically update to In progress:

3. if I update one row to let say Not started, that row is now treated as a custom value:

4. If I change the default value back to Not started again, I see all the rows have Not started value:

5. But now, if I change the default value to something else let say Done, all rows update their values to Done except for that one row which I explicitly set the value to Not started ( Because I changed it's value it no longer accepts default value ):

Current behavior

• If I create a new custom field without a default value, all rows are empty. But if I later set a default value, existing rows do not update.
• The system does not track which rows were manually updated versus which rows are still using the default value.

Expected Behaviour:
It should work like Notion:

• Rows without custom values should adopt the new default.
• Rows with manually changed values should remain unchanged.

Technical inputs

I checked the codebase and found that when we create a new custom field it sets the default value is directly written into all rows.
So my first instinct was to update these values whenever we are update the default value. So something like this:

UPDATE {table} 
SET {field} = {newValue} 
WHERE {field} IS NOT DISTINCT FROM {oldValue}

But there is a bug with this one:
In the notion example, if we follow the same steps, in step 4 when we change the default to Not started when all the rows have this value. After that if we change the default to something else all rows update their value even the row which I explicitly changed the value of.

So there is another option: Instead of writing default values directly into every row, we store all new field values as NULL initially, and while fetching the data we populate all these null values to the default value. And if we explicitly set a field's value to new value then we update the value from null to this new value and while getting the data we skip populating the fields which have data to default value.

I would like to take feedback of the maintainers on this one, as this might change how we store the data.

[git-init-priyanshu]

I would like to work on this feature if that is ok. Thanks.

[BOHEUS]

@git-init-priyanshu could you please explain what's your use case for it? I feel like it's pretty niche feature to implement

[git-init-priyanshu]

Don't have a good use case other than it feels like a good quality of life feature. This makes things more consistent and predictable and reduces the repetitiveness. I agree it is a bit niche feature, I just wanted to throw this idea I had in mind. Your call though.