go.chromium.org/luci@v0.0.0-20240309015107-7cdc2e660f33/tree_status/internal/span/init_db.sql (about)

     1  -- Copyright 2024 The LUCI Authors.
     2  --
     3  -- Licensed under the Apache License, Version 2.0 (the "License");
     4  -- you may not use this file except in compliance with the License.
     5  -- You may obtain a copy of the License at
     6  --
     7  --      http://www.apache.org/licenses/LICENSE-2.0
     8  --
     9  -- Unless required by applicable law or agreed to in writing, software
    10  -- distributed under the License is distributed on an "AS IS" BASIS,
    11  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  -- See the License for the specific language governing permissions and
    13  -- limitations under the License.
    14  
    15  --------------------------------------------------------------------------------
    16  -- This script initializes a LUCI Tree Status Spanner database.
    17  
    18  -- Status are the individual status updates on a tree.
    19  -- Write rate is very low (< 10 / day) so we use timestamp directly as
    20  -- part of the primary key.  This keeps read access to the latest value
    21  -- fast.
    22  -- Status are "Public User Data" (go/spud) and must be deleted within
    23  -- 175 days, so we set TTL to 140 days to allow ample time for Spanner
    24  -- compactions.
    25  CREATE TABLE Status (
    26    -- The name of the tree.
    27    TreeName STRING(63) NOT NULL,
    28    -- The unique identifier for the status. This is a randomly generated
    29    -- 128-bit ID, encoded as 32 lowercase hexadecimal characters.
    30    StatusId STRING(32) NOT NULL,
    31    -- The status of the tree.  One of 'open', 'closed', 'throttled' or 'maintenance'
    32    -- using the enum values in protos/v1/tree_status.proto
    33    GeneralStatus INT64 NOT NULL,
    34    -- The message provided with the status update.
    35    Message STRING(1024) NOT NULL,
    36    -- The username of the user who created the status update.
    37    -- This column will be overwritten to 'user' 30 days after CreationTime.
    38    -- This column should never be exported to BigQuery.
    39    CreateUser STRING(256) NOT NULL,
    40    -- The time the status update was created.
    41    -- Also used to control TTL of status values.
    42    CreateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
    43  ) PRIMARY KEY (TreeName, StatusId),
    44  ROW DELETION POLICY (OLDER_THAN(CreateTime, INTERVAL 140 DAY));