-- SPDX-FileCopyrightText: 2026 Alexandra de Wit
--
-- SPDX-License-Identifier: MIT

{- | The compiled advisory artifact's schema contract.

Écluse Pilot compiles OSV advisory data into a read-only SQLite artifact
(@osv.db@) and publishes it to object storage; the proxy downloads it and
queries it locally on the request path. This module is the one place the
writer and the reader agree on what that artifact looks like: the table-schema
epoch that names and stamps it, the tables' canonical DDL and the column
requirements the reader verifies at acceptance, and the keys of its @meta@
table.

The artifact is immutable and rebuilt from scratch on every compilation, so
there are no migrations, only a read-compatibility contract between whoever
wrote a file and whoever reads it. The epoch expresses exactly that contract:
it moves only when the shape of the data breaks, so the key stays findable
and the stamp stays checkable across releases of either side.
-}
module Ecluse.Core.Osv.Schema (
    -- * The table-schema epoch
    osvSchemaEpoch,
    osvDbFileName,

    -- * The tables
    rangesTableDdl,
    metaTableDdl,
    ColumnSpec (..),
    TableSpec (..),
    osvTableSpecs,

    -- * The @meta@ table
    MetaKey (..),
    renderMetaKey,
) where

import Data.Universe.Class (Universe (..))
import Data.Universe.Generic (universeGeneric)

{- | The table-schema epoch: the version of the artifact's shape, shared by
the Pilot writer and the proxy reader.

Bump it only for a breaking change to the existing shape (a column rename, a
semantic change, a key change). Additive changes (a new column, a new table)
must not bump it: readers select explicit columns, so additions are invisible
to them. A column exists exactly when the build populates it, so a reader
learns what data an artifact offers from the schema itself.

The epoch names the published artifact ('osvDbFileName') and is stamped into
it as SQLite's @user_version@; a reader must reject an artifact whose stamp
does not match its own compiled-in epoch and keep its last known-good
database.

Epoch 2 widened the affected-set model: the ranges table gained a
@last_affected_version@ column (an inclusive upper bound, distinct from the
exclusive @fixed_version@), exact enumerated versions are stored as points, and
@severity@ became a numeric @REAL@ CVSS base score. A reader compiled for epoch 2
requires those columns, so an epoch-1 artifact is rejected rather than read.

Epoch 3 made the stored value types part of the contract: both tables are
declared @STRICT@, so SQLite enforces each column's declared type at write time
and @PRAGMA quick_check@ verifies the stored values against it, and the reader
accepts an artifact only after confirming that declaration ('osvTableSpecs').
Every value the reader decodes is therefore type-sound by construction. The
ranges table's composite primary key became an equivalent unique index, since
@STRICT@ makes primary-key columns implicitly @NOT NULL@ and the bound columns
are legitimately absent.
-}
osvSchemaEpoch :: Int
osvSchemaEpoch :: Int
osvSchemaEpoch = Int
3

{- | The artifact's file name, and object-storage key, for an ecosystem.

The key is stable per ecosystem, so a reader can poll one known key by ETag,
and embeds only the epoch, so the key changes exactly when a reader could no
longer use the file.

>>> osvDbFileName "npm"
"npm-osv-schema3.db"
-}
osvDbFileName :: Text -> FilePath
osvDbFileName :: Text -> FilePath
osvDbFileName Text
ecosystem =
    Text -> FilePath
forall a. ToString a => a -> FilePath
toString Text
ecosystem FilePath -> FilePath -> FilePath
forall a. Semigroup a => a -> a -> a
<> FilePath
"-osv-schema" FilePath -> FilePath -> FilePath
forall a. Semigroup a => a -> a -> a
<> Int -> FilePath
forall b a. (Show a, IsString b) => a -> b
show Int
osvSchemaEpoch FilePath -> FilePath -> FilePath
forall a. Semigroup a => a -> a -> a
<> FilePath
".db"

{- | The ranges table's canonical DDL. @STRICT@ turns the declared column types
from affinity hints into enforced storage types, which is what lets the reader
decode rows without defending against type-confused values. The dedup guard
(the unique index over all five identity columns) is the writer's concern, not
part of the read contract, so it lives with the writer.
-}
rangesTableDdl :: Text
rangesTableDdl :: Text
rangesTableDdl =
    Text
"CREATE TABLE package_vulnerability_ranges (\
    \  package_name TEXT NOT NULL,\
    \  cve_id TEXT NOT NULL,\
    \  introduced_version TEXT,\
    \  fixed_version TEXT,\
    \  last_affected_version TEXT,\
    \  severity REAL\
    \) STRICT"

-- | The @meta@ provenance table's canonical DDL; @STRICT@ as 'rangesTableDdl'.
metaTableDdl :: Text
metaTableDdl :: Text
metaTableDdl =
    Text
"CREATE TABLE meta (\
    \  key TEXT NOT NULL PRIMARY KEY,\
    \  value TEXT NOT NULL\
    \) STRICT"

{- | One column the reader requires of an artifact table: its name, its declared
type (which @STRICT@ makes the enforced storage type), and whether the reader's
decode relies on the column being @NOT NULL@.
-}
data ColumnSpec = ColumnSpec
    { ColumnSpec -> Text
colName :: Text
    , ColumnSpec -> Text
colDeclaredType :: Text
    , ColumnSpec -> Bool
colNotNull :: Bool
    }
    deriving stock (ColumnSpec -> ColumnSpec -> Bool
(ColumnSpec -> ColumnSpec -> Bool)
-> (ColumnSpec -> ColumnSpec -> Bool) -> Eq ColumnSpec
forall a. (a -> a -> Bool) -> (a -> a -> Bool) -> Eq a
$c== :: ColumnSpec -> ColumnSpec -> Bool
== :: ColumnSpec -> ColumnSpec -> Bool
$c/= :: ColumnSpec -> ColumnSpec -> Bool
/= :: ColumnSpec -> ColumnSpec -> Bool
Eq, Int -> ColumnSpec -> FilePath -> FilePath
[ColumnSpec] -> FilePath -> FilePath
ColumnSpec -> FilePath
(Int -> ColumnSpec -> FilePath -> FilePath)
-> (ColumnSpec -> FilePath)
-> ([ColumnSpec] -> FilePath -> FilePath)
-> Show ColumnSpec
forall a.
(Int -> a -> FilePath -> FilePath)
-> (a -> FilePath) -> ([a] -> FilePath -> FilePath) -> Show a
$cshowsPrec :: Int -> ColumnSpec -> FilePath -> FilePath
showsPrec :: Int -> ColumnSpec -> FilePath -> FilePath
$cshow :: ColumnSpec -> FilePath
show :: ColumnSpec -> FilePath
$cshowList :: [ColumnSpec] -> FilePath -> FilePath
showList :: [ColumnSpec] -> FilePath -> FilePath
Show)

-- | A table the reader requires, with the columns its queries decode.
data TableSpec = TableSpec
    { TableSpec -> Text
tableName :: Text
    , TableSpec -> [ColumnSpec]
tableColumns :: [ColumnSpec]
    }
    deriving stock (TableSpec -> TableSpec -> Bool
(TableSpec -> TableSpec -> Bool)
-> (TableSpec -> TableSpec -> Bool) -> Eq TableSpec
forall a. (a -> a -> Bool) -> (a -> a -> Bool) -> Eq a
$c== :: TableSpec -> TableSpec -> Bool
== :: TableSpec -> TableSpec -> Bool
$c/= :: TableSpec -> TableSpec -> Bool
/= :: TableSpec -> TableSpec -> Bool
Eq, Int -> TableSpec -> FilePath -> FilePath
[TableSpec] -> FilePath -> FilePath
TableSpec -> FilePath
(Int -> TableSpec -> FilePath -> FilePath)
-> (TableSpec -> FilePath)
-> ([TableSpec] -> FilePath -> FilePath)
-> Show TableSpec
forall a.
(Int -> a -> FilePath -> FilePath)
-> (a -> FilePath) -> ([a] -> FilePath -> FilePath) -> Show a
$cshowsPrec :: Int -> TableSpec -> FilePath -> FilePath
showsPrec :: Int -> TableSpec -> FilePath -> FilePath
$cshow :: TableSpec -> FilePath
show :: TableSpec -> FilePath
$cshowList :: [TableSpec] -> FilePath -> FilePath
showList :: [TableSpec] -> FilePath -> FilePath
Show)

{- | What the reader verifies before trusting an artifact: each listed table
must be a real @STRICT@ table carrying at least these columns with these
declared types. Columns beyond these are tolerated, which is what keeps
additive schema changes epoch-neutral; the specs mirror 'rangesTableDdl' and
'metaTableDdl' column for column.
-}
osvTableSpecs :: [TableSpec]
osvTableSpecs :: [TableSpec]
osvTableSpecs =
    [ TableSpec
        { tableName :: Text
tableName = Text
"package_vulnerability_ranges"
        , tableColumns :: [ColumnSpec]
tableColumns =
            [ ColumnSpec{colName :: Text
colName = Text
"package_name", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
True}
            , ColumnSpec{colName :: Text
colName = Text
"cve_id", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
True}
            , ColumnSpec{colName :: Text
colName = Text
"introduced_version", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
False}
            , ColumnSpec{colName :: Text
colName = Text
"fixed_version", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
False}
            , ColumnSpec{colName :: Text
colName = Text
"last_affected_version", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
False}
            , ColumnSpec{colName :: Text
colName = Text
"severity", colDeclaredType :: Text
colDeclaredType = Text
"REAL", colNotNull :: Bool
colNotNull = Bool
False}
            ]
        }
    , TableSpec
        { tableName :: Text
tableName = Text
"meta"
        , tableColumns :: [ColumnSpec]
tableColumns =
            [ ColumnSpec{colName :: Text
colName = Text
"key", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
True}
            , ColumnSpec{colName :: Text
colName = Text
"value", colDeclaredType :: Text
colDeclaredType = Text
"TEXT", colNotNull :: Bool
colNotNull = Bool
True}
            ]
        }
    ]

{- | A key of the artifact's @meta@ table (one @TEXT@ key\/value row per key).

The table carries the artifact's provenance: which build produced it, from
what source, and when.
-}
data MetaKey
    = -- | The Pilot application version that produced the artifact.
      MetaPilotVersion
    | -- | The ecosystem the artifact was compiled for (e.g. @npm@).
      MetaEcosystem
    | -- | When the compilation finished, as an ISO-8601 UTC timestamp.
      MetaBuiltAt
    | -- | The advisory-dump URL the artifact was compiled from.
      MetaSourceUrl
    | -- | The number of advisory ranges the artifact holds.
      MetaRowCount
    deriving stock (MetaKey -> MetaKey -> Bool
(MetaKey -> MetaKey -> Bool)
-> (MetaKey -> MetaKey -> Bool) -> Eq MetaKey
forall a. (a -> a -> Bool) -> (a -> a -> Bool) -> Eq a
$c== :: MetaKey -> MetaKey -> Bool
== :: MetaKey -> MetaKey -> Bool
$c/= :: MetaKey -> MetaKey -> Bool
/= :: MetaKey -> MetaKey -> Bool
Eq, (forall x. MetaKey -> Rep MetaKey x)
-> (forall x. Rep MetaKey x -> MetaKey) -> Generic MetaKey
forall x. Rep MetaKey x -> MetaKey
forall x. MetaKey -> Rep MetaKey x
forall a.
(forall x. a -> Rep a x) -> (forall x. Rep a x -> a) -> Generic a
$cfrom :: forall x. MetaKey -> Rep MetaKey x
from :: forall x. MetaKey -> Rep MetaKey x
$cto :: forall x. Rep MetaKey x -> MetaKey
to :: forall x. Rep MetaKey x -> MetaKey
Generic, Int -> MetaKey -> FilePath -> FilePath
[MetaKey] -> FilePath -> FilePath
MetaKey -> FilePath
(Int -> MetaKey -> FilePath -> FilePath)
-> (MetaKey -> FilePath)
-> ([MetaKey] -> FilePath -> FilePath)
-> Show MetaKey
forall a.
(Int -> a -> FilePath -> FilePath)
-> (a -> FilePath) -> ([a] -> FilePath -> FilePath) -> Show a
$cshowsPrec :: Int -> MetaKey -> FilePath -> FilePath
showsPrec :: Int -> MetaKey -> FilePath -> FilePath
$cshow :: MetaKey -> FilePath
show :: MetaKey -> FilePath
$cshowList :: [MetaKey] -> FilePath -> FilePath
showList :: [MetaKey] -> FilePath -> FilePath
Show)

-- Enumerate every MetaKey from the type itself, so a new key is covered without a
-- hand-maintained list. Derived from Generic, not a partial Enum/Bounded pair.
instance Universe MetaKey where universe :: [MetaKey]
universe = [MetaKey]
forall a. (Generic a, GUniverse (Rep a)) => [a]
universeGeneric

-- | The key's stored form in the @meta@ table.
renderMetaKey :: MetaKey -> Text
renderMetaKey :: MetaKey -> Text
renderMetaKey = \case
    MetaKey
MetaPilotVersion -> Text
"pilot_version"
    MetaKey
MetaEcosystem -> Text
"ecosystem"
    MetaKey
MetaBuiltAt -> Text
"built_at"
    MetaKey
MetaSourceUrl -> Text
"source_url"
    MetaKey
MetaRowCount -> Text
"row_count"