init.sql

-- =====================================================================================
-- Yagura SPEC-002 init script
--
-- Run this ONCE per Yagura database, with a DB admin user (sa, DOMAIN\Administrator,
-- or db_owner of the target database). After this script completes, GRANT INSERT and
-- SELECT on [SyslogMessages] to the Yagura service account so the service can write
-- without owning DDL permissions (per ADR-0006 §原則 6).
--
-- Usage examples:
--   sqlcmd -S "(localdb)\MSSQLLocalDB" -d Yagura -E -i init.sql
--   sqlcmd -S "myserver.example.com" -d Yagura -U sa -P ******* -i init.sql
--
-- For a fresh SQL Server / LocalDB:
--   1. Create the database:  CREATE DATABASE Yagura;
--   2. Run this script as admin to create the table + indexes.
--   3. CREATE LOGIN + CREATE USER + GRANT for the Yagura service account.
--      The default service account installed by the MSI is `NT SERVICE\Yagura`
--      (virtual service account, no AD required).
--
--      IMPORTANT: a server-level LOGIN must exist before CREATE USER FOR LOGIN
--      is run. Skip the LOGIN step and you will hit Msg 15007 ("not a valid
--      login") at CREATE USER, or Error 18456 (Login failed) at first connect.
--
--      For local SQL Server Express co-located with Yagura on the same host:
--        USE master;
--        IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'NT SERVICE\Yagura')
--            CREATE LOGIN [NT SERVICE\Yagura] FROM WINDOWS;
--        USE Yagura;
--        IF USER_ID('NT SERVICE\Yagura') IS NULL
--            CREATE USER [NT SERVICE\Yagura] FOR LOGIN [NT SERVICE\Yagura];
--        GRANT INSERT, SELECT ON [dbo].[SyslogMessages] TO [NT SERVICE\Yagura];
--
--      For an AD environment with a dedicated service account (e.g. CORP\YaguraSvc):
--        USE master;
--        IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'CORP\YaguraSvc')
--            CREATE LOGIN [CORP\YaguraSvc] FROM WINDOWS;
--        USE Yagura;
--        IF USER_ID('CORP\YaguraSvc') IS NULL
--            CREATE USER [CORP\YaguraSvc] FOR LOGIN [CORP\YaguraSvc];
--        GRANT INSERT, SELECT ON [dbo].[SyslogMessages] TO [CORP\YaguraSvc];
--
--      For a remote SQL Server reached over the network from a workgroup (no AD):
--        Use SQL Server authentication. Create the SQL login (sa or admin):
--          USE master;
--          CREATE LOGIN yagura WITH PASSWORD = '...', CHECK_POLICY = ON;
--          USE Yagura;
--          CREATE USER yagura FOR LOGIN yagura;
--          GRANT INSERT, SELECT ON [dbo].[SyslogMessages] TO yagura;
--        Then point Yagura at this login via C:\ProgramData\Yagura\appsettings.local.json
--        with `User Id=yagura;Password=...;` (instead of Trusted_Connection=true).
--   4. Update Yagura's connection string to point at this database.
-- =====================================================================================

SET NOCOUNT ON;

-- Filtered indexes below (WHERE [Severity] IS NOT NULL, WHERE [ParseSucceeded] = 0)
-- require SET QUOTED_IDENTIFIER ON. SqlConnection enables it by default but sqlcmd
-- does NOT (CI hit "CREATE INDEX failed because ... QUOTED_IDENTIFIER" without this).
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;

IF OBJECT_ID(N'[dbo].[SyslogMessages]', N'U') IS NOT NULL
BEGIN
    PRINT 'Table [dbo].[SyslogMessages] already exists; init.sql is a no-op.';
    RETURN;
END;

CREATE TABLE [dbo].[SyslogMessages] (
    [Id]               BIGINT IDENTITY(1,1) NOT NULL,
    [ReceivedAt]       DATETIME2(7)         NOT NULL,
    [SourceAddress]    VARCHAR(45)          NOT NULL,  -- IPv4 / IPv6 string (45 = max IPv6 with embedded IPv4)
    [SourcePort]       INT                  NULL,      -- TCP only; UDP is NULL per SPEC-001 §データモデル
    [RawMessage]       NVARCHAR(MAX)        NOT NULL,  -- truncated to MaxMessageSize (default 8 KiB) by SPEC-001
    [Facility]         TINYINT              NULL,      -- 0-23 per RFC 5424 §6.2.1
    [Severity]         TINYINT              NULL,      -- 0-7 per RFC 5424 §6.2.1
    [Timestamp]        DATETIME2(7)         NULL,      -- sender-claimed timestamp, UTC
    [Hostname]         NVARCHAR(255)        NULL,      -- RFC 5424 §6.2.4 max 255
    [AppName]          NVARCHAR(64)         NULL,      -- RFC 5424 §6.2.5 max 48 (extra margin)
    [Message]          NVARCHAR(MAX)        NULL,      -- MSG portion only; HEADER excluded
    [Version]          INT                  NULL,      -- RFC 5424 only
    [MsgId]            NVARCHAR(64)         NULL,      -- RFC 5424 §6.2.7 max 32 (extra margin)
    [ProcId]           NVARCHAR(128)        NULL,      -- RFC 5424 §6.2.6 max 128 (RFC limit)
    [StructuredData]   NVARCHAR(MAX)        NULL,      -- RFC 5424 only
    [DetectedEncoding] NVARCHAR(32)         NULL,      -- e.g. shift_jis / euc-jp; UTF-8 = NULL
    [ParseSucceeded]   BIT                  NOT NULL,  -- false rows have only I/O fields populated

    -- SPEC-002 論点 2: 時系列範囲クエリ (Web UI: 「過去 1 時間」「今日」など) を PK 直当たりに
    -- するため (ReceivedAt, Id) 複合クラスタ化。Id は同 ms 衝突の tiebreaker。
    CONSTRAINT [PK_SyslogMessages] PRIMARY KEY CLUSTERED ([ReceivedAt], [Id])
);

-- SPEC-002 論点 3: 中間セット (3 non-clustered indexes)

-- 1. 機器ごとの時間範囲検索 (Web UI: 「この送信元の最近 N 時間」)
CREATE NONCLUSTERED INDEX [IX_SyslogMessages_SourceAddress_ReceivedAt]
    ON [dbo].[SyslogMessages] ([SourceAddress], [ReceivedAt]);

-- 2. Severity フィルタ + 時間範囲 (Web UI: 「Critical+ のアラート」)。
--    NULL 除外で index サイズを削減 (パース失敗行は別 index で扱う)。
CREATE NONCLUSTERED INDEX [IX_SyslogMessages_Severity_ReceivedAt]
    ON [dbo].[SyslogMessages] ([Severity], [ReceivedAt])
    WHERE [Severity] IS NOT NULL;

-- 3. パース失敗の運用診断用フィルタインデックス (小さい、INSERT コスト軽微)
CREATE NONCLUSTERED INDEX [IX_SyslogMessages_ParseFailed]
    ON [dbo].[SyslogMessages] ([ReceivedAt])
    WHERE [ParseSucceeded] = 0;

PRINT 'Created [dbo].[SyslogMessages] with PK and 3 non-clustered indexes.';
PRINT '';
PRINT 'NEXT STEP: GRANT INSERT, SELECT to the Yagura service account, e.g.:';
PRINT '    GRANT INSERT, SELECT ON [dbo].[SyslogMessages] TO [NT SERVICE\Yagura];';
PRINT '  (or [CORP\YaguraSvc] for an AD service account; see header comment for details.)';