komp_ac/common/proto/table_definition.proto

// common/proto/table_definition.proto
syntax = "proto3";
package komp_ac.table_definition;

import "common.proto";

// The TableDefinition service manages the entire lifecycle of user-defined
// tables (stored as both metadata and physical PostgreSQL tables) inside
// logical "profiles" (schemas). Each table has stored structure, links, and
// validation rules.
service TableDefinition {
  // Creates a new table (and schema if missing) with system columns,
  // linked-table foreign keys, user-defined columns, and optional indexes.
  // Also inserts metadata and default validation rules. Entirely transactional.
  rpc PostTableDefinition(PostTableDefinitionRequest)
      returns (TableDefinitionResponse);

  // Lists all profiles (schemas) and their tables with declared dependencies.
  // This provides a tree-like overview of table relationships.
  rpc GetProfileTree(komp_ac.common.Empty)
      returns (ProfileTreeResponse);

  // Drops a table and its metadata, then deletes the profile if it becomes empty.
  rpc DeleteTable(DeleteTableRequest)
      returns (DeleteTableResponse);
}

// A single link to another table within the same profile (schema).
message TableLink {
  // Name of an existing table within the same profile to link to.
  // For each link, a "<linked>_id" column is created on the new table.
  // That column references "<linked>"(id) and adds an index automatically.
  string linked_table_name = 1;

  // If true, the generated foreign key column is NOT NULL.
  // Otherwise the column allows NULL.
  // Duplicate links to the same target table in one request are rejected.
  bool required = 2;
}

// Defines the input for creating a new table definition.
message PostTableDefinitionRequest {
  // Table name to create inside the target profile.
  // Must be lowercase, alphanumeric with underscores,
  // start with a letter, and be <= 63 chars.
  // Forbidden names: "id", "deleted", "created_at", or ending in "_id".
  string table_name = 1;

  // List of links (foreign keys) to existing tables in the same profile.
  // Each will automatically get a "<linked>_id" column and an index.
  repeated TableLink links = 2;

  // List of user-defined columns (adds to system/id/fk columns).
  repeated ColumnDefinition columns = 3;

  // List of column names to be indexed (must match existing user-defined columns).
  // Indexes can target only user-defined columns; system columns ("id", "deleted",
  // "created_at") and automatically generated foreign key ("*_id") columns already
  // have indexes. Requests trying to index those columns are rejected.
  repeated string indexes = 4;

  // Name of profile (Postgres schema) where the table will be created.
  // Same naming rules as table_name; cannot collide with reserved schemas
  // like "public", "information_schema", or ones starting with "pg_".
  string profile_name = 5;
}

// Describes one user-defined column for a table.
message ColumnDefinition {
  // Column name that follows the same validation rules as table_name.
  // Must be lowercase, start with a letter, no uppercase characters,
  // and cannot be "id", "deleted", "created_at", or end with "_id".
  string name = 1;

  // Logical column type. Supported values (case-insensitive):
  //   TEXT / STRING
  //   BOOLEAN
  //   TIMESTAMP / TIMESTAMPTZ / TIME
  //   MONEY (= NUMERIC(14,4))
  //   INTEGER / INT
  //   BIGINTEGER / BIGINT
  //   DATE
  //   DECIMAL(p,s) → NUMERIC(p,s)
  // DECIMAL args must be integers (no sign, no dot, no leading zeros);
  // s ≤ p and p ≥ 1.
  string field_type = 2;
}

// Response after table creation (success + DDL preview).
message TableDefinitionResponse {
  // True if all DB changes and metadata inserts succeeded.
  bool success = 1;

  // The actual SQL executed: CREATE TABLE + CREATE INDEX statements.
  string sql = 2;
}

// Describes the tree of all profiles and their tables.
message ProfileTreeResponse {
  // Table entry in a profile.
  message Table {
    // Internal ID from table_definitions.id (metadata record).
    int64 id = 1;

    // Table name within the profile (schema).
    string name = 2;

    // Other tables this one references (based on link definitions only).
    repeated string depends_on = 3;
  }

  // Profile (schema) entry.
  message Profile {
    // Name of the schema/profile (as stored in `schemas.name`).
    string name = 1;

    // All tables in that schema and their dependencies.
    repeated Table tables = 2;
  }

  // All profiles in the system.
  repeated Profile profiles = 1;
}

// Request to delete one table definition entirely.
message DeleteTableRequest {
  // Profile (schema) name owning the table (must exist).
  string profile_name = 1;

  // Table to drop (must exist in the profile).
  // Executes DROP TABLE "profile"."table" CASCADE and then removes metadata.
  string table_name = 2;
}

// Response after table deletion.
message DeleteTableResponse {
  // True if table and metadata were successfully deleted in one transaction.
  bool success = 1;

  // Human-readable summary of what was removed.
  string message = 2;
}