Research Vault (Snowflake) — tested manifest

research_vault_manifest_tested.sql

-- Research Vault (Snowflake) — tested manifest
-- Includes fixed GOVERNANCE schema + DIRECTORY view (uses relative_path)

CREATE DATABASE IF NOT EXISTS RESEARCH_VAULT;
CREATE SCHEMA IF NOT EXISTS RESEARCH_VAULT.RAW_PDFS;
CREATE SCHEMA IF NOT EXISTS RESEARCH_VAULT.GOVERNANCE;

CREATE TAG IF NOT EXISTS RESEARCH_VAULT.GOVERNANCE.LICENSE_SCOPE;
CREATE TAG IF NOT EXISTS RESEARCH_VAULT.GOVERNANCE.AI_USAGE;

CREATE STAGE IF NOT EXISTS RESEARCH_VAULT.RAW_PDFS.PDF_STAGE DIRECTORY=(ENABLE=TRUE);
ALTER STAGE RESEARCH_VAULT.RAW_PDFS.PDF_STAGE SET TAG
  RESEARCH_VAULT.GOVERNANCE.LICENSE_SCOPE = 'personal_research_only',
  RESEARCH_VAULT.GOVERNANCE.AI_USAGE      = 'no_model_training';

CREATE TABLE IF NOT EXISTS RESEARCH_VAULT.RAW_PDFS.MANIFEST (
  filename        STRING,
  doi             STRING,
  source_url      STRING,
  retrieved_at    TIMESTAMP_NTZ,
  license_scope   STRING,
  ai_usage        STRING,
  sha256          STRING
);

CREATE OR REPLACE VIEW RESEARCH_VAULT.RAW_PDFS.V_DIR AS
  SELECT REGEXP_REPLACE(relative_path, '^.*/', '') AS filename, relative_path AS path, size, last_modified
  FROM DIRECTORY(@RESEARCH_VAULT.RAW_PDFS.PDF_STAGE);

-- Optional: example PUT (run from snowsql)
-- PUT 'file:///Users/lou/Downloads/*.pdf' @RESEARCH_VAULT.RAW_PDFS.PDF_STAGE AUTO_COMPRESS=FALSE OVERWRITE=TRUE PARALLEL=8;

-- Optional: insert provenance example
-- INSERT INTO RESEARCH_VAULT.RAW_PDFS.MANIFEST
-- (filename, doi, source_url, retrieved_at, license_scope, ai_usage, sha256)
-- VALUES ('paper.pdf','10.1145/...','https://dl.acm.org/...',CURRENT_TIMESTAMP(),'personal_research_only','no_model_training','<sha256>');