title: Flipping status of records without regression over time
body: <p>Not sure how to look for this, so I'm sorry if this was asked before.</p>
  
  <p>Supposedly I am supplied with a collection of records. These records carry a certain status, which is either <code>true</code> or <code>false</code>. Over time, our client would want a specific subset of the documents to be switched from one state or another.</p>
  
  <p>So currently the records are stored in a SQL database (PostgreSQL to be exact). So in order to keep a history of the flipping of status (and also to ensure we can rerun everything from scratch in case something screwed up), we have all the update statements stored in a version-controlled repository. However, because this is SQL, and after a while the SQL becomes super complicated (to ensure no regression, i.e. not setting the status to <code>true</code> when it was previously requested to be <code>false</code>).</p>
  
  <p>For example, we received ticket #1 and #2 in the past to flip records to <code>true</code> and <code>false</code> respectively</p>
  
  <pre><code>-- DO THIS FOR TICKET #1
  UPDATE  record
  SET     status = TRUE
  WHERE   id IN (SELECT  id
                 FROM    records
                 WHERE   this_criteria = 'that');
  
  
  -- DO THIS FOR TICKET #2
  UPDATE  record
  SET     status = FALSE
  WHERE   id IN (SELECT id
                 FROM   records
                 WHERE  another_criteria = 'whatever')
  </code></pre>
  
  <p>This is what we do when we have a ticket #3 where it involves a subset of records in #1 and #2</p>
  
  <pre><code>-- DO THIS FOR TICKET #3
  UPDATE  records
  SET     statue = TRUE
  WHERE   id IN (SELECT  id
                 FROM    records
                 WHERE   true_criteria = 'something')
          AND id NOT IN (-- regression ticket #1
                         SELECT  id
                         FROM    records
                         WHERE   this_criteria = 'that'
                         UNION
                         -- regression ticket #2
                         SELECT  id
                         FROM    records
                         WHERE   another_criteria = 'whatever');
  </code></pre>
  
  <p>So what we are doing now is to run the update statement for #1, then followed by #2, lastly #3. As expected, #3 will take forever to run. I can optimize the query, but it is still not going to help in the long run, as future tickets will be causing more and more complicated conditions being written to avoid overwriting the status set in the previous ticket(s).</p>
  
  <p>Now that we are looking for an alternative to the approach for another project, and we are expecting to have the status flip to happen far more frequently in the future. What could be a better way to manage this, and if possible, provide a way for us to actually log the changes (doesn't have to be SQL)?</p>
  
  <p>Instead of doing it in batches, we are shifting towards update them incrementally over time (imagine we send the records into a pipe one after another periodically, and each request of client is a box to flip record's status if applicable, finally those records will be upserted to some data store). So we are replacing SQL into some rule-based mini DSL to ensure the condition is met before flipping the status. However, that doesn't look like an improvement to the SQL method, because sooner or later the condition will grow due to regression check, and the rules are practically just SQL in another format.</p>
  
  <p>An example of the new one for ticket #3 would be some thing like this</p>
  
  <pre><code>SET status TO TRUE
  CONDITION (is_equal, true_criteria, 'something') # new ticket #3 request
            # REGRESSION in ticket #1
            AND (not_equal, this_criteria, 'that')
            # REGRESSION in ticket #2
            AND (not_equal, another_criteria, 'whatever')
  </code></pre>
  
  <p>As you can see while I don't have to do it to all records at once like SQL, the statement above is really just a variation to the original SQL counterpart.</p>
  
  <p>EDIT:</p>
  
  <ol>
  <li>This is not about SQL optimization, I can also implement this without using relational database, and then I can remove the identifier column. The status variable is not determined by the value of the identifier.</li>
  <li>Assuming I am gonna throw the collection into a search appliance, i.e. ElasticSearch, so it would be nice if I can pre-compute the status, so I don't have to attach a huge query DSL on top of user issued query, to ensure only <code>status=true</code> records are returned.</li>
  <li>I am looking for an alternative solutions, that do not have to be implemented in relational database (hence i removed the <code>postgresql</code> tag).</li>
  <li>The client does not always care if their new request will caused the some of the previously set status to flip, they just want to ensure the previously flipped statuses are not implicitly rewritten when the new condition is applied.</li>
  <li>In the SQL implementation, we made a mistake of not logging the changelog for the status field, and the one we are drafting will be having that, so yes, we have to have a way to log the status flip.</li>
  </ol>